April 24, 2002 at 9:53 am
Hello,
I am running a DTS package via. EXEC xp_cmdshell DTSRUN ... in a stored procedure.
Is there a way to test for any errors that may have occured during the DTS process?
Thanks,
Dan
Edited by - DanG on 04/24/2002 09:54:09 AM
April 25, 2002 at 2:25 am
Could I reccomend calling DTS with the OLE metyhod rather than xp_cmdshell. This offers you far richer pickings in control and error trapping.
I use this little Generic SP to encapsulate a call to DTS, it works very well for us and allows a high level of flexibility as we can extend the SP's abilities by utilising the interface exposed by the DTS object
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