I looked around and found some ideas about how to do this, but no definitive best way. One of the ideas was to use sp_start_job to kick off an SQL Server Agent job that runs the DTS package. If this is the best way to do it, then the next question would be, "How do I schedule a DTS package from a job and make it non-recurring?"
Thanks, Tim
I wouldn't suggest tying this kind of functionality to a trigger. Triggers are supposed to be fast. I don't think there is any way to launch a DTS package that will be as fast as I would want a trigger to be. If this resonates with you, then I would suggest having your trigger simply insert a row into a special table, and then have a job that executes as often as you need for your purpose (every minute? every 10 seconds?) that monitors this table and kicks off the appropriate DTS package as needed.
Instead of using xp_cmdshell, I did this:
When a certain value in a table changes, the trigger uses msdb.sp_start_job to start a job. This job should not run on a schedule, only when initiated by a user. I set the job schedule to run one time, which is now in the past, and I unchecked the enabled box.
This job has one step, which is DTSRun /~Z0xHEXENCRYPTEDVALUE. The DTS package copies some rows from this server to another server on a different platform and on success resets values in the table with the trigger for next time. The trigger checks a table value before calling sp_start_job, so that the job starts only under certain conditions, not every time.
Since sp_start_job runs asyhchronously the trigger completes quickly. The only drawback to this is that I need to poll the value that was reset on success and either let the user know it worked, or after some time out period, it did not work.
The alternative would be to use xp_cmdshell if I needed synchronous operation, which might not be a good idea from inside of a trigger.