OLEDB Destinations in SSIS and the dilemma with triggers!!

So I was sitting happily at my desk, loading data from some flat files into SQL Server tables using SSIS and minding my own business. I set up my OLEDB source. I set my OLEDB destination. I did all the transformations very carfully and I was excited because I thought this might be my first package which would run successfully the first time. And it did. I was a happy bunny. But you see, good things don’t just happen in life like that. There is always a caveat. You see, this particular destination table had insert/update triggers on it. Whenever a row is added/updated in this table, the trigger would fire and log the changes in the audit table. Sort of maintaining a history. However, and this is where it gets interesting, when I was populating it from SSIS, no trigger would get fired.  Normal insert would fire the trigger but not SSIS. Try it yourself. 

The culprit (or “solution” whichever way you look at life) was the Data Access mode in OLEDB destination. If you are using “Table or view – fast load”, the triggers won’t get fired. This is the default mode selected so this can be very easily missed. So for reference purpose, I have created the following table which lists all the Data Access modes and which one’s support triggers. This table is valid for SQL Server 2008, 2008 R2 and 2012. 

Data Access Mode  Supports Triggers
Table or view Yes
Table or view – fast load No
Table name or view name variable Yes
Table name or view name variable – fast load No
SQL command Yes

Be aware of those triggers next time!! 😀

Advertisements

One comment

  1. With fast load, you just have to go to the advanced editor, component properties tab and enter “FIRE_TRIGGERS” in FastLoadOptions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s