Multiple DTS Packages to Run from a Stored Procedure

[ljevans]

I have 10 DTS packages that I want to execute sequentially. Originally, there was one package, but it kept pulling errors during execution. Can I create a Stored Procedure that will execute all 10 packages sequentially?

 

[cheyney]

try this syntax

exec master..xp_cmdshell 'dtsrun /S<server> /N<dts1name> /E'
exec master..xp_cmdshell 'dtsrun /S<server> /N<dts2name> /E'
exec master..xp_cmdshell 'dtsrun /S<server> /N<dts3name> /E'
exec master..xp_cmdshell 'dtsrun /S<server> /N<dts4name> /E'

/S = SQL Server Host Name
/N = DTS Package Name
You can either use /E for &quot;trusted connection&quot; or /U/P to supply username and passwords!


cheyney

 

[tlbroadbent]

There is a page on SQLDTS.com that explains how to execute packages from a strored procedure. The site appears to be broken at the moment so I can't provide the direct link. Select DTS FAQ from the home page and the Execution.

http://www.sqldts.com/

Have you considered setting up a job with ten steps? If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm