September 28, 2006 at 2:01 am
I have set up an integration package to transfer a number of tables.
One of the destination tables has an after insert trigger on it to populate 2 fields which don't exist in the source table. The problem is that this trigger does not seem to be firing, thus the 2 fields are left null.
Has anyone got any ideas to why this is happening?
September 29, 2006 at 3:43 am
i suspect that the integration package is disabling the trigger before doing the insert, then re-enabling it afterwards. Not sure where that would be controlled from. However, I'm not sure that a trigger is the best way of going about this... for large volumes of data it could be very costly. Is it possible to insert all the data first, then run a single update query which sets the two fields on all the table? That would probably have the best performance, and can be incorporated as the final step in the package job. Alternatively you can do lookups and calculations for each row as you go along, as part of the integration package job.
September 29, 2006 at 12:36 pm
Just add an execute SQL task to the end of your SSIS package control flow that runs the code in the trigger. Your trigger may not be firing because you are doing a bulk insert instead of an insert - I had the same problem. Adding the update into the package actaully seems o be more efficient and it has the added benefit of only running the update if the insert is successful.
Joshua Perry
http://www.greenarrow.net
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply