April 24, 2002 at 10:52 am
When I run this from command line:
dtsrun /Ssuperdevelop /Usa /Psdsdo402 /Ntest_import
This runs ok and the table is loaded
but when I call the same from within my SP
as
EXEC master..xp_cmdshell 'dtsrun /Ssuperdevelop /Usa /Psdsdo402 /Ntest_import'
It fails and gives a path not recognized error.
Please help
Thanks
Just intorduced to DTS
Raghu
Raghu
April 25, 2002 at 2:21 am
Like all good politicians I won't answer your question directly. All I'd say is that if you're calling DTS from SP's then I'd reccomend using the OLE object method rather than xp_cmdshell. It gives you far more flexibility in controlling the process, check out the properties etc you can set and use.
Here's a little Generic sp that I use to encapsulate the calls to DTS. You can extned it as you wish
PRINT 'STORED PROCEDURE : dbo.prc_util_execute_dtspackage'
GO
IF EXISTS (SELECT 1 from dbo.sysobjects
WHERE id = Object_id('dbo.prc_util_execute_dtspackage') AND (type = 'P' or type = 'RF'))
BEGIN
DROP PROC dbo.prc_util_execute_dtspackage
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*******************************************************************************
Now For The Procedure Proper
*******************************************************************************/
CREATE PROCEDURE prc_util_execute_dtspackage
@ServerName sysname,
@ServerUserName sysname,
@ServerPassword sysname,
@PackageName sysname
AS
DECLARE
@ErrorValue INT,
@Object INT,
@ErrorDescription VARCHAR(255)
/*********************************
Create A Package Object
*********************************/
EXEC @ErrorValue = sp_OACreate 'DTS.PACKAGE', @Object OUTPUT
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/********************************
Load The Method With Required Params
********************************/
EXEC @ErrorValue = sp_OAMethod @Object,
'LoadFromSqlServer',
NULL,
@ServerName = @ServerName,
@ServerUserName = @ServerUserName,
@PackageName = @PackageName,
@Flags = 0,
@PackagePassword = '',
@ServerPassword = @ServerPassword
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/**********************************
Execute The Method
**********************************/
EXEC @ErrorValue = sp_OAMethod @Object, 'Execute'
IF @ErrorValue <> 0 BEGIN
EXEC sp_OAGetErrorInfo @Object, NULL, @ErrorDescription OUT
SELECT @ErrorDescription AS ErrorDescription
RETURN
END
/*********************************
Empty The Package Object
*********************************/
EXEC @ErrorValue = sp_OAMethod @Object, 'UnInitialize'
/********************************
Destroy the Object
********************************/
EXEC @ErrorValue = sp_OADestroy @Object
RETURN
I have found it is easy to please a great many people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply