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

sql triggers ssis bulkinsert
ladenedge picture ladenedge · Jun 1, 2012 · Viewed 21.2k times · Source

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

ladenedge picture ladenedge · Jun 1, 2012

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.

Related questions

Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( …

Read More
SQL update trigger only when column is modified

By looking at other examples I've come up with the following but it doesn't seem to work as I would like: I want it to only update the modified information if the QtyToRepair value has been updated... but it doesn't …

Read More
MySQL Trigger after update only if row has changed

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count &…

Read More