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?

Answer

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.