How to execute SSIS package installed in SQL Server in trigger

Question

I have created SSIS package in SQL Server Business Intelligence Development Studio. The package works fine if I run it there, so I deployed the package.

Then I used the package installation wizard and installed it on a local SQL Server 2005.

Now I want to use it in my trigger.

I know how to execute a package from file, but how to I execute it when it is installed in SQL Server?

Thank you.

Answer 1

As I mentioned before: I would not put such a task into a trigger. Since you cannot control when and how many times the trigger is fired, anything in the trigger should be very short in terms of execution time. Do not put long-running processing into a trigger!

My approach would be:

1. the trigger writes an entry into a table (a "job" table or whatever you want to call it)

2. a task (e.g. SQL Agent Job) that runs e.g. every 5 mins. or whatever reads that table, and if necessary, writes the file.

This decouples the trigger code from the longer process of actually writing the file.

Otherwise, your system performance will be severely affected in a bad way by this potentially very long-running trigger....