How to execute SSIS package installed in SQL Server in trigger

6
votes

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.

4
sql-servertriggersssis
Quite honestly: I don't think this is a good idea to begin with. A trigger should be very lean and mean - write an entry into a table or something like that. Not more. A trigger should never cause a long-running processing - like a SSIS package.... you should decouple this from the trigger's execution. – marc_s
what i need to do is write a file on every inserted row. I went with xp_cmdshell, but people advice me not to use it. Therefore i wrote an SSID package and installed it on MSSQL. All i need to do now is to execute it when a row is inserted in a specific table and i know of no other solution then a trigger. – no9
Do you need to write the entries to the file as they appear or would some delay between insert and logging to a file be acceptable? – Mithrandir
Short delay would be acceptable. Currently i am trying to create a SQL Server Agent job that would execute SSIS package. And then start the job in a trigger. Am i going in the wrong direction here? – no9

4 Answers

8
votes

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....

1
votes

There is a way to execute SSIS-packages from T-SQL, but a quite unsave one, i think. It involves enabling the "xp_cmdshell" option and using dtexec. But that's definetly not for use in a trigger! Any way, as marc_s pointed out in his comment, a trigger should be very lean. Since it is part of the transaction of the triggering command any long running operation, especially a SSIS package, would slow your database down considerably.

1
votes

I'd break this out into separate steps. The file requirement may be there, but consider this: as long as every row generates a file, is it truly a deal-breaker if it takes perhaps five or ten minutes to create the file? And is this worse than the knock-on effects you'll see from running a SSIS package from within the trigger?

So:

Step 1 : the trigger simply inserts a row into another table with the information required for the file to be created.
Step 2 : modify your SSIS package to have an extra early step that polls that table for any new entries, creates the files as needed, then marks the entries as completed (or removes them completely, but personally I like audit trails).
Step 3 : add a scheduled job to the server that runs that SSIS package every 5 minutes.

If you don't want to modify your SSIS package, you could instead create a stored procedure that polls the table and executes the package, and schedule that in a job. The main thing is to get away from the idea of firing the package direct from the trigger.

The method above will also minimize the impact of potential problems such as the file destination being unavailable for some reason.

1
votes

just complementing all other answers, you should definitively not do that. marc_s's idea of inserting data into a table and having a job every X minutes is the best apporach in my opinion.

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

PS2: Ansering your other questiom, to call a package stored on SQl using DTEXEC, just replace /FILE to /SQL

dtexec /sql "\YourPackage"