patternsqlMinor

SSIS OLEDB Command not firing triggers

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

ssisoledbtriggersfiringcommandnot

Problem

Got a bit of a strange one that I can't seem to work out.

We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a large amount of data that's getting loaded daily via an SSIS package.

The SSIS package is inserting rows using an OLEDB Command that is simply executing a stored procedure

EXEC StoredProc @Param1, @Param2, @Param3, @Param4, @Param5

The simplified version of what this procedure does is as follows

INSERT INTO TableA (Col1, Col2) 
 VALUES(@Param1, @Param2)
 SET @IdentA = SCOPE_IDENTITY()

 INSERT INTO TableB (Col3, Col4) 
 VALUES(@IdentA, @Param3)
 SET @IdentB = SCOPE_IDENTITY()

 INSERT INTO TableC (Col5, Col6) 
 VALUES(@IdentB, @Param4)
 SET @IdentC = SCOPE_IDENTITY()

 INSERT INTO TableD (Col7, Col8, Col9, Col10) 
 VALUES(@IdentA,@IdentB,@IdentC,@Param5)
 SET @IdentD = SCOPE_IDENTITY()

 INSERT INTO TableE (Col11, Col12, Col13, Col14, Col15) 
 VALUES(@IdentA,@IdentC,@IdentD,@Param3,@Param5)

We have triggers on each of the tables involved to transfer newly inserted data across to another system.

What appears to be happening is that the triggers do not fire when the procedure is run from SSIS. Everything works as expected when running the procedure from SQL Management Studio.

Anyone got any thoughts on why this could be occurring, and how we can resolve the issue.

Cheers
Phil

Solution

I was not able to reproduce your error (although I only tested with one table in the mix).

An OLE DB Destination has the ability to disable trigger and other check constraints when it loads but the OLE DB Command does not provide a similar facility.

Set up

I created a simplistic table: a key, a value and an empty field that will be populated, via trigger, when the row is updated. I created a trigger that will update the empty field for the modified row. Finally, I created a stored procedure to handle actually performing the UPDATE statement.

CREATE TABLE dbo.dba_128344
(
    Foo int NOT NULL
,   Bar char(1) NOT NULL
,   Blee datetime NULL
);
GO
CREATE TRIGGER tr_dba_128344_update
ON dbo.dba_128344
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        D
    SET
        Blee = CURRENT_TIMESTAMP
    FROM
        Inserted AS I
        INNER JOIN
            dbo.dba_128344 AS D
            ON D.Foo = I.Foo;
END
GO
CREATE PROCEDURE dbo.dba_128344Update
(
    @Foo int
,   @bar char(1)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        DBA
    SET
        DBA.Bar = @bar    
    FROM
        dbo.dba_128344 AS DBA
    WHERE
        DBA.Foo = @Foo;

    -- Induce a 1 second delay
    -- Allows us to ensure we aren't cheating
    WAITFOR DELAY '00:00:01';
END
GO

My package is simplistic. A data flow with an OLE DB Source and an OLE DB Command.

My OLE DB Source uses the following query which just calculates the next value of Bar in the ASCII table and also brings in the original value. A will become B, B becomes C, C becomes D.

SELECT
    D.Foo
,   D.Bar AS OldBar
,   CHAR(ASCII(D.Bar) + 1) AS NewBar
FROM
    dbo.dba_128344 AS D;

The OLE DB Command uses the following statement.

EXECUTE dbo.dba_128344Update ?, ?;

OLE DB Connection Managers require the use of the ? as an zero ordinal based replacement system. Here I mapped column Foo to the parameter @Foo and NewBar to the parameter @bar because I'd hate to be consistent in my casing...

After execution, as expected I saw my dates populated with ~ 1 second delay between each row to commensurate with the row-by-row nature of the OLE DB Command.

Foo Bar Blee
10  B   2016-02-04 22:53:18.043
20  C   2016-02-04 22:53:19.050
30  D   2016-02-04 22:53:20.053

Biml

If you'd like to confirm you're seeing the same behaviour on your system, you're in luck. Biml, the Business Intelligence Markup Language, is an XML dialect that can be fed into a translator to generate SSIS packages. It's pretty slick. All you need to do is download and install the free add-in BIDS Helper

Once installed, right click on an SSIS project and select Add new Biml File

Double click BimlScript.biml and paste the following content

1, Adjust line 3 to make that point to a valid server and database where you ran the above DDL

Save and then right click on BimlScript.biml and select "Generate SSIS Packages". Whoosh Out comes dba_128344.dtsx

GIVE IT A TRY!!!

Code Snippets

CREATE TABLE dbo.dba_128344
(
    Foo int NOT NULL
,   Bar char(1) NOT NULL
,   Blee datetime NULL
);
GO
CREATE TRIGGER tr_dba_128344_update
ON dbo.dba_128344
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        D
    SET
        Blee = CURRENT_TIMESTAMP
    FROM
        Inserted AS I
        INNER JOIN
            dbo.dba_128344 AS D
            ON D.Foo = I.Foo;
END
GO
CREATE PROCEDURE dbo.dba_128344Update
(
    @Foo int
,   @bar char(1)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        DBA
    SET
        DBA.Bar = @bar    
    FROM
        dbo.dba_128344 AS DBA
    WHERE
        DBA.Foo = @Foo;

    -- Induce a 1 second delay
    -- Allows us to ensure we aren't cheating
    WAITFOR DELAY '00:00:01';
END
GO

SELECT
    D.Foo
,   D.Bar AS OldBar
,   CHAR(ASCII(D.Bar) + 1) AS NewBar
FROM
    dbo.dba_128344 AS D;

EXECUTE dbo.dba_128344Update ?, ?;

Foo Bar Blee
10  B   2016-02-04 22:53:18.043
20  C   2016-02-04 22:53:19.050
30  D   2016-02-04 22:53:20.053

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
    <Packages>
        <Package Name="dba_128344">
            <Tasks>
                <Dataflow Name="DFT Demo Trigger">
                    <Transformations>
                        <OleDbSource 
                            ConnectionName="CM_OLE" 
                            Name="OLESRC Demo Data">
                            <DirectInput><![CDATA[-- SourceQuery
SELECT
    D.Foo
,   D.Bar AS OldBar
,   CHAR(ASCII(D.Bar) + 1) AS NewBar
FROM
    dbo.dba_128344 AS D;
]]></DirectInput>
                        </OleDbSource>

                        <OleDbCommand 
                            ConnectionName="CM_OLE" 
                            Name="Trigger Tester">
                            <DirectInput><![CDATA[EXECUTE dbo.dba_128344Update ?, ?;]]></DirectInput>
                            <Parameters>
                                <Parameter SourceColumn="Foo" TargetColumn="@Foo" DataType="Int32" />
                                <Parameter SourceColumn="NewBar" TargetColumn="@bar" DataType="AnsiString" Length="1"/>
                            </Parameters>
                        </OleDbCommand>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Context

StackExchange Database Administrators Q#128344, answer score: 3

Revisions (0)

No revisions yet.