Thursday, March 26, 2009

Trigger not working when importing data with SSIS package

I am very new to SSIS package. Actually i am not a hardcode SQL guy, so i never needed to work with SSIS before. In my current project i needed to import data after fetching from a server with FTP and then extract the ZIP files and insert it into respective tables. For eatracting ZIP files i used "SharpZipLib" which is open source library for dot net. Then i created a temp table in which i first inserted to remove duplicate. Remember, i created the temp table on the fly inside the "Execute SQL Task" with create table statement and after insert to the original table i deleted the table. In this scenario you have to change the "DelayValidation" property of the "Data Flow Task" to "true" (By default it is false). Otherwise you will get some validation error as the temp table in which you are going to insert data is not present now, it is created on the fly and deleted after insert is completed.
Now in my case i had a trigger which need to fire each time i instered some data into the table. Actually, i was updating the stock rate and needed to copy the rate by trigger. But to my surprise, the trigger was not firing when i insert/update the data by the SSIS. But the trigger fires when i do the update by SQL.
The problem is with the settings in "OleDB Destination" inside "Data Flow Task". Here is how i solved the issue
Right click on th "OldeDB Destination" and you will find "Show Advanced Editor" and open the Advanced editor.

In the editor check the "FastLoadOptions" which is by default "TABLOCK,CHECK_CONSTRAINTS". You need to add one more option "FIRE_TRIGGERS" . And thats it. But remember the trigger runs once for each update/insert. Not for every update/insert. So in case where we are doing batch update, you must write the trigger code in such a way that it will be able to handle the situation for multiple row sets. Here is a sample statement
CREATE TRIGGER UpdateStockRate
ON INV_tbl_StockMaster
AFTER INSERT,Update
AS

UPDATE t
SET CMP = i.LastTradedPrice
FROM INV_tbl_Holding t
JOIN INSERTED i
ON i.Symbol = t.Symbol

2 comments:

ESquared said...

Thank you for posting that. Exactly what I needed.

ESquared said...

Although, I just realized you might want to tighten up the confusing phrase "But remember the trigger runs once for each update/insert. Not for every update/insert."

Triggers run once per DML operation (update/insert/delete), not once per row within the operation. The inserted and deleted meta-tables accessible within the trigger contain all rows affected.