Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why aren't my triggers firing during an insert by SSIS?

I have an SSIS data flow task with an OLE DB Destination component that inserts records into a table with a trigger. When I execute a normal INSERT statement against this table, the trigger fires. When I insert records through the SSIS task the trigger does not fire.

How can I get the trigger firing in SSIS?

like image 527
ladenedge Avatar asked Jun 01 '12 03:06

ladenedge


3 Answers

Because the OLE DB Destination task uses a bulk insert, triggers are not fired by default. From BULK INSERT (MSDN):

If FIRE_TRIGGERS is not specified, no insert triggers execute.

One must manually specify FIRE_TRIGGERS as part of the OLE DB component through its Advanced Editor.

enter image description here

Then add "FIRE_TRIGGERS" to the value of FastLoadOptions (note that options are comma-separated):

enter image description here

With that option in place, the triggers should fire during the task's execution.

like image 82
ladenedge Avatar answered Oct 21 '22 12:10

ladenedge


complementing ladenedge's answer.

Because the OLE DB Destination task uses a bulk insert, triggers are not fired by default

that is true when you have the "fast load" option selected.

enter image description here

If you change it to a regular "table or view" data access mode, your triggers should fire normally because the insert is done row-by-row

like image 45
Diego Avatar answered Oct 21 '22 12:10

Diego


You can do this without using SQL Server Data Tool for Visual Studio by editing the dtsx File with Notepad (or any other Text editors).

Search for the following property:

<property
 dataType="System.String"
 description="Specifies options to be used with fast load.  Applies only 
 if  fast load is turned on."
 name="FastLoadOptions">
      TABLOCK,CHECK_CONSTRAINTS
</property>

and add the value FIRE_TRIGGERS as Diego already described.

like image 32
Stefan Avatar answered Oct 21 '22 12:10

Stefan