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

more ▼

asked Mar 19, 2012 at 09:14 AM in Default

avatar image

91 21 23 25

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Mar 19, 2012 at 09:23 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 19, 2012 at 09:47 AM

avatar image

Usman Butt
14.6k 6 13 21

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, 2012 at 08:33 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 19, 2012 at 09:14 AM

Seen: 1501 times

Last Updated: Mar 19, 2012 at 08:34 PM

Copyright 2018 Redgate Software. Privacy Policy