How to execute an SSIS package installed on a SQL Server trigger

Question

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

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

Now I want to use it in my trigger.

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

Thanks.

Answer 1

As I mentioned earlier: I would not put such a task in a trigger. Since you cannot control when and how many times a trigger fires, all triggers must be very short in terms of runtime. Do not put long processing in a trigger!

My approach:

• the trigger writes a record to the table (the table "task" or whatever you want to name)

• a job (for example, an SQL agent job) that runs, for example. every 5 minutes. or whatever reads this table and, if necessary, writes a file.

This separates the startup code from the longer process of actually writing the file.

Otherwise, the performance of your system will suffer greatly from this potentially very long run ...

Answer 2

There is a way to execute SSIS packages from T-SQL, but, in my opinion, is quite unsafe. This includes the xp_cmdshell option and the use of dtexec. But it is definitely not for use in a trigger! In any case, as marc_s noted in his comment, the trigger should be very meager. Since this is part of the start command transaction, any lengthy operation, especially the SSIS package, will significantly slow down your database.

Answer 3

I would break it down into separate steps. The file requirement may be there, but consider it: while each line generates a file, is it really a transaction breaker, if it takes maybe five or ten minutes to create the file? And is it really worse than the hits that you will see from the launch of the SSIS package from the trigger?

So:

Step 1: the trigger simply inserts a row into another table with the information needed for the file being created.
Step 2: modify your SSIS package to get an extra early step that checks this table for any new entries, creates files as needed, and then notes that the entries are complete (or delete them completely, but I personally like the audit logs) .
Step 3: Add the scheduled task to the server that runs this SSIS package every 5 minutes.

If you do not want to modify your SSIS package, you can instead create a stored procedure that will test the table and execute the package, and schedule it in the task. The main thing is to get away from the idea of ​​launching a package directly from a trigger.

The above method also minimizes the impact of potential problems such as file inaccessibility for any reason.

Answer 4

just by complementing all the other answers, you should not do it completely. The idea of ​​marc_s to insert data into a table and have work every X minutes is the best apporach, in my opinion.

PS1: There is a link on how to call a package from SP

PS2: Running into another quest, call the package stored on SQl using DTEXEC, just replace /FILE with /SQL

 dtexec /sql "\YourPackage"