Using a trigger to execute Stored proc's after the import file wizzard has run

I am using the SQL import wizzard to import my file into an existing table on my DB. I have created ad trigger using the below code. but it does not run. How do I make this run. Note when I import data I am importing many rows.

create trigger [dbo].[A_ImportRun] on [dbo].[A_Import] after insert

exec A_ImportData

asked Mar 19 '12 at 09:14 AM in Default

HRugbeer
91 ● 8 ● 12 ● 19

(comments are locked)

2 answers:

This is the default behavior if you are using the data import wizard i.e. triggers are not fired during the bulk load. For that you must save the package and change the OLEDB destination's "FastLoadOptions" property i.e. After opening the saved package in BIDS do the following

In Data Flow Right Click on the OLEDB Destination
Select "Show Advanced Editor"
Select "Component Properties" Tab
Append string FIRE_TRIGGERS in the "FastLoadOptions" property which by default have "TABLOCK,CHECK_CONSTRAINTS" enabled. You have to add a comma before specifying each property.

But please keep in mind that your trigger should be able to handle multiple rows insertion. Hope it helps.

answered Mar 19 '12 at 09:47 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

This is solution how to let the triggers fire, however better approach will be the one @Fatherjack suggested to fire the stored proc after import using the Execute SQL task rather than fire stored proc in a trigger. So +1 to both of you. :-)

Mar 19 '12 at 08:33 PM Pavel Pawlowski

(comments are locked)

If you have used the data import wizard to import the data then you will need to have saved the import process as a SSIS package. Providing you have the Business Intelligence Development Studio installed you can edit this package and add an "Execute SQL" step to it that executes the script you need.

answered Mar 19 '12 at 09:23 AM