Hello all,

I am attempting to run a DTS package using a stored procedure and SP_OA.

Here is the code (which is primarily lifted from Timothy Peterson's MS SQL
server 2000 DTS book):

DECLARE @sPackageName varchar(255)
DECLARE @sServerName varchar(255)
DECLARE @lFlags int

--Enter name of package and access information
SET @sPackageName = 'GenericDTSPakageName'
SET @sServerName = '(local)'
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
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,
@Flags = @lFlags,
@PackageName = @sPackageName
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
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'

However, I get this error (which is not very helpful):

Step Name|Task Name|Result|Started|Finished|Duration
DTSStep_DTSActiveScriptTask_1|DTSTask_DTSActiveScr iptTask_1|Failure|2004-07-
26 10:52:18.000|2004-07-26 10:52:18.000|0

Execution of Package Completed

Can someone point me to identify where the failure may be?
The DTS package is a very simple VBScript which executes without a hitch
from Enterprise manager. It is a single step package
Could it be a permissions / ownership issue?

Thanks in advance.

Wael (Will) Fadel