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

 

[Rhys666]

Could you use dynamic T-SQL in a stored procedure to create a scheduled task to be executed immediately or GetDate + 1 minute, giving it a name defined in a table, which you then look up at the end of you DTS package, from another stored procedure designed to create a DROP SCHEDULED TASK script with Dynamic T-SQL...

Basically I have't tried this, or what you're looking to achieve, but I believe from memory that what I'm suggesting is technically possible...

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[dafdba]

You can schedule the DTS-job and remove the schedule from the job in SQL Server Agent. From the stored procedure you start the job using sp_start_job. You need to have the public role of the msdb database to do this.