Executing DTS via Stored Procedure: problem

[davideart]

Hi everybody.

I would like to execute DTS packages (stored in msdb database of a SQL Server 7) via SP and I was given some advice in this Forum.
Unfortunetly I still find some problems:

1)I launch the followin' SP from Query Analizer (after having properly connected):

exec master..xp_cmdshell 'dtsrun /N MyDtsPackageName'

and get the followin' output:

DTSRun:  Cannot create COM Server to load and execute DTS Package.  Error -2147221164 (80040154):  Class not registered

2)I've also tried launching the followin' (found on MS web site):

declare @hr int
declare @oPKG int

--Creating the DTS Package Object: 				
EXEC @hr = sp_OACreate 'DTS.Package' , @oPKG OUT
IF @hr <> 0 BEGIN
     PRINT '***  Create Package object failed'
     EXEC sp_displayoaerrorinfo @oPKG, @hr
     RETURN
END				

--Loading the Package: 				
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSQLServer(&quot;TSTSOA01&quot;, &quot;&quot;, &quot;&quot;, 256, , , , &quot;AA_ASP_Prova&quot;)',    NULL
IF @hr <> 0 BEGIN
     PRINT '***  Load Package failed'
     EXEC sp_displayoaerrorinfo @oPKG, @hr
     RETURN
END				


--Executing the Package: 				
EXEC @hr = sp_OAMethod @oPKG, 'Execute' 
IF @hr <> 0 BEGIN 
    PRINT '***  Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN
END				

--Cleaning up: 				
EXEC @hr = sp_OADestroy @oPKG 
IF @hr <> 0 BEGIN
     PRINT '***  Destroy Package failed'
     EXEC sp_displayoaerrorinfo @oPKG, @hr
     RETURN
END

but the output is still quite bad:

***  Create Package object failed
OLE Automation Error Information
  HRESULT: 0x80040154
  Source: ODSOLE Extended Procedure
  Description:  Class not registered

NOTE: If I open a MS-DOS prompt window and execute the command:

dtsrun /S MyServerName /U MyUserName /P MyUserNamePwd /N MyDtsPackageName

it works perfectly.

Any idea?

Any help will be greatly appreciated.

Thank you in advance

Davide

 

[RickCole]

Not sure why you are getting this error but as a work around could you do the following.

Set up a disabled scheduled job to run the DTS pacakage.

Run a stored procedure, which enables the job, and set's a required start date and time.

SQL Agent then runs the job.

At the end of the DTS package a step disables the scheduled job ready for the next time the SP is called.

This approach works fine at my site. The only thing you have to watch out for is to include a step that refreshes the job scheduler as this is held in cache.

Rick.