x

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

more ▼

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

HRugbeer gravatar image

HRugbeer
91 15 23 23

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

2 answers: sort newest

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

Usman Butt gravatar image

Usman Butt
13.9k 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, 2012 at 08:33 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x290

asked: Mar 19, 2012 at 09:14 AM

Seen: 1328 times

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