February 2, 2011 at 1:25 pm
I have the following trigger, it works when I manually input into the table but if I run a SSIS package to add data to the table the trigger does not fire, should I write something in the SSIS package after the data is added to the table?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Add_LapLine_Header]
ON [dbo].[tmp_LapLineData]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO LapLine_Header (CycleDesc,PartMarker)
SELECT tmp_CycleDesc, tmp_PartMarker from tmp_LapLineData
END
When I manual enter data into the table tmp_LapLineData the trigger works but if
February 2, 2011 at 2:27 pm
Bulk imports usually bypass triggers. You can turn that off, but instead I'd recommend having SSIS do the inserts into the other table directly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2011 at 6:03 am
In the second table I don't want duplicates and that is what the trigger does, it looks to see if the data already exists and if it doesn't it adds the data once because the uploaded file can contain multiple lines of the same data.
What would I use in the package? Would I write the trigger code in "Execute T-SQL Statement Task" in the SSIS package?
Thanks.
February 3, 2011 at 7:54 am
I might suggest that you handle the duplicates in the SSIS data flow or in a staged table instead of relying on a trigger. I am not a big fan of hidden trigger processing other than DDL triggers for auditing purposes. Performance over head can build over time and you might not see it.
February 3, 2011 at 8:04 am
I believe GSquared is right. You should directly insert into second table by using an Execute SQL task, If you are wirried about duplicatesYou can always Have Group by and then Left outer join with Second table.
Actually, Just write stored proc, do a group by and then Use LEft outer join to avoid duplicates and run the stored proc in the Execute SQL task.
:w00t:
November 17, 2016 at 5:36 am
Hi GSquare,
I have this same issue, but I need to export data as soon as it is imported into a table. You mentioned that it can be turned off, how do I go about doing this?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply