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

Question

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?

Solution 1

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.

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

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

Solution 2

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.

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

Solution 3

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.

Comments

[NealR]

Does one have similar options if they are using and ADO.Net connection and working on an SQL database programmatically?

[ladenedge]

@NealR: yes, see SqlBulkCopy.

[MisterIsaak]

I just want to add, with OLE DB Destination, if you have the Keep nulls checked, your triggers won't get fired either.

[PBMe_HikeIt]

@JIsaak I had Keep Nulls checked and it did fire my trigger. I'm using SQL2008R2 and SSIS 2012

[Andrew Hill]

Typically you are using bulk load because your hundreds of thousands of rows are too slow to do a Row-by-Row operation on, many people will find this is not the best solution.