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|
Be aware of those triggers next time!! 😀