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
(For more information on these methods see
the
Execute a package from T-SQL article)
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.