Executing a DTS Package in a Stored Procedure

Solution 1—You can use the dtsrun command-line utility to invoke DTS packages. Dtsrun isn't a T-SQL command, but you can invoke command-line utilities by using the xp_cmdshell extended stored procedure. Xp_cmdshell executes a given command string as an OS command shell and returns any output as rows of text. This means that your stored procedure could call xp_cmdshell, which invokes dtsrun, which in turn invokes your package.

Solution 2—You can schedule a DTS package as a job that the SQL Server Agent job-management service executes. You can use the sp_start_job procedure in msdb to create an on-demand job that runs the package. The SQL Server Agent simply runs the DTS package by executing an Operating System Command task that runs the command-line utility dtsrun that I describe in Solution 1. However, multiple users can't run the same job at the same time. Users will receive an error message if they try to start a job that's already running. Therefore, this solution isn't a good choice unless users can access the DTS job one at a time.

Solution 3—DTS packages are technically nothing more than COM objects, and you can easily manipulate them through the usual COM interfaces. You can write a COM wrapper program that calls and manipulates the DTS package any way you want. You can then call this wrapper from T-SQL by using the sp_OACreate() function that lets T-SQL create and manipulate an instance of a COM object on your SQL Server. This option is the most difficult to implement, but it can provide additional flexibility that you won't find in Solution 1 or Solution 2.