URGENT!!!! HOW DO I SET THE LAST UPDATE DATE AND LAST UPDATED BY IN THE TARGET TABLE USING DTS
-
Hello all,
I am new to SQL Server and to DTS. I am trying to load a flat file using DTS Designer into target tables in SQL server. My target tables have two standard columns last update date and last updated by and they are not supplied in the flat file. I have created a trigger on my target table to automatically fire when updates and inserts take place. The mapping works when executed and inserts all the flat file data but the last update date and last updated by columns are still empty. The trigger is somehow not firing. Is there anything I need to do explicitly to make this work. Any help would be appreciated.
Thanks...
Srini.
-
Try changing the trigger to be an UPDATE statement that gets executed directly after the BULK INSERT step is complete in the DTS package. I believe the problem is that the BULK INSERT process will not activate the trigger.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Also check the Fast Load option in the package - if it is checked it is doing a Bulk copy insert.
Darrell Parrish
La Crosse, WI -
Thanks for the response. I am not using the BULK INSERT task in my mapping. I am using the Transform data task. Are you advising to change this. Below is the body of my trigger
CREATE TRIGGER test_trigger ON dbo.test_table
FOR INSERT,UPDATE
AS
Update test_table
Set LAST_UPDT_ID =SYSTEM_USER, Last_Updt_DT= GetDate()
FROM Inserted
WHERE test_table.id = Inserted.id
Thanks,
Srini
-
In the properties of the transformation task on the last Tab "Options" there is an option under SQL Server to use a Fast Load which is the same as a Bulk Load/Copy. Turn this off, it should then allow the trigger to fire.
Darrell Parrish
La Crosse, WI -
Thanks Darrell !!! It worked.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply