Can I use a trigger to launch a Data Transformation Services (DTS) package?
Yes, you can use a trigger to launch a DTS package in the following three ways:
- Scheduling the DTS package as an ad hoc job by using the sp_start_job stored procedure in msdb to fire the job from the trigger. This method has the advantage of providing resource governance because only one instance of the job can run at one time.
- Using the sp_Oa* system stored procedures to invoke the DTS COM interface, which launches the package. However, this approach can be difficult if you want to set global variables.
- Using xp_cmdshell from within the trigger. Note that you must have execution rights to run xp_cmdshell.
All the methods have one flaw—they execute outside the caller's transaction context, so they can't be rolled back. Consequently, even if a user request (or error) rolls back the transaction after the job has been launched, the work that the DTS package does isn't automatically rolled back. For example, if the DTS job runs a check-printing program and the request that generated the job is canceled, the check would print anyway. You can avoid this problem by creating a trigger to insert entries into a request table. Then, if your transaction is rolled back, the row in the request table will also be rolled back. You can easily code a SQL Server Agent task—running at an interval you choose—to look in the request table for committed requests, then use one of the three methods to launch a DTS package.