DTS Package and triggers 1

[jialian]

Hi,

I have an after insert trigger on a table. If I insert data into the table by writing an insert SQL statement, the trigger fires normally. If I insert data into the same table using a DTS package, the trigger does not fire. The DTS package does insert the data into the table. Is it possible to use a DTS package to load the data and also fire the trigger at the same time?

Thanks,
Jia Lian

 

[Corran007]

How are you putting the data into the table with dts? there are lots of ways.

 

[jialian]

Thanks for your response.

The dts package was created using the GUI interface in SQL Server's enterprise manager. Basically, a source (SQL Server database) and a destination (another SQL Server database) and selected both of them, right click and selected transform data task. Using the Transform Data Task properties, I defined the source table and the destination table and the mapping through the transformations tab. Most of the transformations are basic Copy Columns with one transformation being an ActiveX script that used a lookup. I'm afraid that I don't know what kind of dts that is.

 

[Corran007]

I do not know of any way to directly to it useing the tranform data task(the black line). however, there is a workaround.

For the destination table for the transform data task, change it to a temporary copy of the table you are sending it to. Make the structure exactly the same as the final table. This will get all the information in the table as you want it with the copy columns and lookup completed. Then add a execute sql task that selects everything from that temptable into your final table. Statment looks something like:
Insert into Finaltable
Select * from CopyTable

Set the sql task to execute on success. This will allow for you to use dts to transform the data and to have the trigger fire.

After its finished, you can clear the copy table for next time. hope this helps some. Perhaps someone else knows a way to get it to work directly.

 

[jialian]

Thanks for your help ... it will work for now