I need to execute a Data Transformation Services (DTS) package from within a stored procedure but can't find the syntax. How can I execute the package?
Unfortunately, no T-SQL command lets you run a DTS package, so you have to jump through some hoops to address your problem. You can choose from three possible solutions; your specific needs will determine which solution is best.
- 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.