General discussion

Locked

SQL Trigger for Execution of DTS Package

By jgill ·
How do we write a sql script that when a table is updated, will execute a dts package to send me an sql mail?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Trigger for Execution of DTS Package

by roc180 In reply to SQL Trigger for Execution ...

Lot of ways to go here. First off I'm pretty sure you can't start a DTS package from inside a trigger. I would suggest that you write a trigger on the table that is being updated, that on the after_update kicks the trigger that then insersts the data into a stage table. I would then have a sql scheduled job running at whatever interval you decide to read my stage table and do work (sql mail). The job would also clean up after itself by deleting records out of the stage table that it processes.

Good Luck

Neal Martinelli

Collapse -

SQL Trigger for Execution of DTS Package

by jgill In reply to SQL Trigger for Execution ...

Great ideas but I'm already passed the logic part. I'm looking for assitance with the coding part. BTW have you ever heard of "dtsrun"? you can kick a dts package off with dtsrun.

Collapse -

SQL Trigger for Execution of DTS Package

by Glen_McLeod In reply to SQL Trigger for Execution ...

dtsrun will kick off a DTS package, but since it's a command-line utility (in 7.0 anyway), you'll have to call it using xp_cmdshell(). Be sure you assign proper permissions, as this is a restricted stored proc for most users.

Glen

Collapse -

SQL Trigger for Execution of DTS Package

by jgill In reply to SQL Trigger for Execution ...

Poster rated this answer

Collapse -

SQL Trigger for Execution of DTS Package

by jgill In reply to SQL Trigger for Execution ...

This question was closed by the author

Back to Desktop Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums