DTS can be used keep distributed systems synchronised, and a common question is how can I execute DTS from a trigger to ensure the remote system is updated whenever I make a change to my local system?

There are three methods for executing DTS from T-SQL-

• Use the extended stored procedure xp_cmdshell to call the DTSRUN utility.
• Use the OLE stored procedures to execute the package via the DTS object model
• Create a SQL Server Agent job to execute the package

The first two methods are synchronous and for this very reason I would strongly advise against them. Triggers must successfully complete for the transaction to commit, so if a package takes two seconds to complete, it will take two seconds longer for the transaction to commit. I would find this unacceptable in a production system and could lead to serious blocking issues. Another very important point is that to even make this method work the DTS package must use a source query specifying the NOLOCK hint, otherwise the uncommitted transaction will block the package itself.

Using SQL Server Agent will introduce a layer of abstraction, being an asynchronous execution method, which will avoid both blocking issues. This method can also have problems depending on the frequency of updates and the package execution time, since you may try and start a job which is already running.

Another solution is to use the trigger to insert a record into a simple indicator table. You may then schedule a DTS package to execute at appropriate intervals, in which the first task is used to check the change indicator table. If records are found, continue with the data transfer task, and finally clear out the indicator table. If no records are found you can gracefully end the package.