asked
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 as exec A_ImportData
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.
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.
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. :-)
4 People are following this question.
