Trigger doesn't work when data is enter through SSIS package

  • 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

  • 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

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

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

  • 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:

  • 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