General discussion

  • Creator
    Topic
  • #2096168

    SQL Trigger for Execution of DTS Package

    Locked

    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?

All Comments

  • Author
    Replies
    • #3834563

      SQL Trigger for Execution of DTS Package

      by roc180 ·

      In reply to SQL Trigger for Execution of DTS Package

      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

      • #3835140

        SQL Trigger for Execution of DTS Package

        by jgill ·

        In reply to SQL Trigger for Execution of DTS Package

        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.

    • #3836224

      SQL Trigger for Execution of DTS Package

      by glen_mcleod ·

      In reply to SQL Trigger for Execution of DTS Package

      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

    • #3669453

      SQL Trigger for Execution of DTS Package

      by jgill ·

      In reply to SQL Trigger for Execution of DTS Package

      This question was closed by the author

Viewing 2 reply threads