运用sp_OA调用SQLSERVER上DTS运行的一个存储过程

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
  --Description:  execute the DTS package in SQL Server
  --02/17/05-     Created  (tom)
  --Input         :   @sPackageName DTS package名字；
                        @sServerName SQL SERVER 服务器；
                        @sServerUserName USER ID
                        @sServerPassword Passwod
  --Output      :   @iResult =0 表示执行成功，否则失败

*/
ALTER   PROCEDURE gswr_system_DtsExecute
@sPackageName varchar(255),
@sServerName varchar(255),
@sServerUserName varchar(255),
@sServerPassword varchar(255),
@iResult int out
AS
BEGIN
 
DECLARE @lFlags int

--Enter name of package and access information

SET @lFlags = 256 --256 For Trusted, 0 for SQL Server Security

DECLARE @hPkg int
DECLARE @hSteps int
DECLARE @hStp int

DECLARE @sMethod varchar(30)
DECLARE @sProperty varchar(30)
DECLARE @src varchar(40)
DECLARE @desc varchar(100)

DECLARE @sStepName varchar(40)
DECLARE @sTaskName varchar(40)
DECLARE @lExecResult int
DECLARE @lExecStatus int
DECLARE @lDisableStep int
DECLARE @sExecResult varchar(20)
DECLARE @dtStartTime datetime
DECLARE @lExecutionTime int
DECLARE @dtFinishTime datetime
DECLARE @lStepCount int
DECLARE @idxStep int

DECLARE @hResult int

SET NOCOUNT ON

--Create package object
EXEC @hResult = sp_OACreate 'DTS.Package2', @hPkg OUT

set @iResult=@hResult

IF @hResult <> 0
BEGIN
   EXEC sp_OAGetErrorInfo NULL, @src OUT, @desc OUT
   SELECT Info = 'Create Package', Source= RTrim(@src), Description=@desc
END

--Load package
SET @sMethod = 'LoadFromSQLServer'
EXEC @hResult = sp_OAMethod @hPkg , @sMethod , NULL ,
 @ServerName = @sServerName,
 @ServerUserName = @sServerUserName,
 @ServerPassword = @sServerPassword,
 @Flags = @lFlags,
 @PackageName = @sPackageName

set @iResult=@hResult

IF @hResult <> 0
BEGIN
   EXEC sp_OAGetErrorInfo NULL, @src OUT, @desc OUT
   SELECT Info = 'Method - ' + @sMethod, Source=@src, Description=@desc
END

--Execute the package
SET @sMethod = 'Execute'
EXEC @hResult = sp_OAMethod @hPkg, @sMethod, NULL

set @iResult=@hResult

IF @hResult <> 0
BEGIN
   EXEC sp_OAGetErrorInfo NULL, @src OUT, @desc OUT
   SELECT Info = 'Method - ' + @sMethod, Source = @src, Description=@desc
END

EXEC @hResult = sp_OAGetProperty @hPkg, 'Steps', @hSteps OUT
EXEC @hResult = sp_OAGetProperty @hSteps, 'Count', @lStepCount OUT
SET @idxStep = 0

--Check each of the steps for execution information
WHILE @idxStep < @lStepCount
BEGIN

    SET @idxStep = @idxStep + 1

    SET @sProperty = 'Steps(' + Convert(varchar(10), @idxStep) + ')'
    EXEC @hResult = sp_OAGetProperty @hPkg, @sProperty, @hStp OUT

    EXEC @hResult = sp_OAGetProperty @hStp,
             'Name', @sStepName OUT 
    EXEC @hResult = sp_OAGetProperty @hStp,
             'TaskName', @sTaskName OUT
    EXEC @hResult = sp_OAGetProperty @hStp,
             'ExecutionStatus', @lExecStatus OUT
    EXEC @hResult = sp_OAGetProperty @hStp,
             'DisableStep', @lDisableStep OUT
    EXEC @hResult = sp_OAGetProperty @hStp,
             'ExecutionResult', @lExecResult OUT

    IF @lExecStatus = 4 --Step completed
    BEGIN

        IF @lExecResult = 1
            Set @sExecResult = 'Failure'
        ELSE
            Set @sExecResult = 'Success'

        EXEC @hResult = sp_OAGetProperty @hStp,
                 'StartTime', @dtStartTime OUT
        EXEC @hResult = sp_OAGetProperty @hStp,
                 'ExecutionTime', @lExecutionTime OUT
        EXEC @hResult = sp_OAGetProperty @hStp,
                 'FinishTime', @dtFinishTime OUT

    END
    ELSE
    BEGIN

        IF @lDisableStep <> 0
            Set @sExecResult = 'Disabled'
        ELSE
            Set @sExecResult = 'Not Executed'

        SET @dtStartTime = NULL
        SET @lExecutionTime = NULL
        SET @dtFinishTime = NULL

    END
  
    SELECT "Step Name" = @sStepName,
           "Task Name" = @sTaskName,
           "Result" = @sExecResult,
           "Started" = @dtStartTime,
           "Finished" = @dtFinishTime,
           "Duration" = @lExecutionTime   
END

SET NOCOUNT OFF

PRINT 'Execution of Package Completed'

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO