Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Using a Trigger to Launch a DTS Package
Would using a trigger to launch your DTS package streamline your process? One of these three methods likely fits your scenario.
Microsoft's Sql Server Development Team
November 25, 2002
1 Min Read
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.
About the Author
You May Also Like
