Executing a DTS from a stored procedure

[TomR100]

Hello,
Can anyone tell me how to execute a DTS from a stored procedure? The DBA at my company asked that we not use the xp_cmdshell command. I was hoping there was another way to execute the DTS.

Thank you for your help.
TomR100

 

[JohnDTampaBay]

Here is an article from Database Journal that describes how to run a DTS package from a stored procedure using the sp_OA OLE automation stored procedures.

http://www.databasejournal.com/features/mssql/article.php/1459181

Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[fluteplr]

The easiest way I have found is to schedule the dts job in SQl Agent. (You can do this by right clicking on the dts job in enterprise manager and selecting schedule.) Then going into the job in enterprise manager and deleting the schedule. You now have a job that will only run on request. You can then fire off the job by using a stored proc that looks similar to the following

cREATE PROCEDURE dbo.mydtsjob AS

exec msdb.dbo.sp_start_job @job_name = 'MyDTSJob'

 

[fluteplr]

Hopefully in the next version MS will add a new store proc like sp_run_dts.

 

[TomR100]

Hello flutepir,

The code you gave worked great! Thank you both for your help in steering me through this.

Thanks again,
TomR100