Sql-server – SSIS OLEDB Command not firing triggers

oledbsql serversql-server-2012ssis

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

Best Answer

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!!!

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

Related Solutions

Sql-server – more helpful way to detect lock acquire/release stats in SQL Profiler

Would that actually cause a table lock on the entire table, even though i'm only selecting certain rows into a table variable?

Lock escalation. If your 'select' is not ... selective, if it has to scan large portions of a table then the engine may escalate to table locks. See Lock Escalation (Database Engine).

I can see there are 2 Lock Escalations occurring, does that mean row locks have been escalated to table locks?

Are they lock escalation events, or just lock escalation attempts? If they succeeded then the transaction has locked the entire table.

Can someone give me some tips on how to trace this deadlock down further?

Approaching this from the individual locks POV is not very productive. The typical investigation focuses on capturing and analyzing the deadlock graph. See Analyzing Deadlocks with SQL Server Profiler. Capture the deadlock graph and post it here and maybe we can help. Upload the deadlock graph XML, not the picture of the deadlock, see How to: Save Deadlock Graphs (SQL Server Profiler).

Deadlock graph:

Spid 58 has page 7:1:11066 in IX mode and is blocking spid 61 that wants it in S mode. Spid 61 has page 7:1:1932345 in S mode and is blocking spid 58 who has an IU mode lock on it but wants it convert it to IX. Things are complicated by the addition of parallelism and a long list of 'me too' waiters, but the fundamental issue can be reduced to the one I described above.

This is a typical pattern of lack of indexes. You have an updater, XWingNew.dbo.UpdatePostsCleanedUriUniqueUri which is issuing an update that likely scans the entire XWingNew.dbo.Posts table. At the same time you have several readers likely scanning the entire table too in some linq generated query that was truncated in the XML: SELECT [UnionAll3].[C2] AS [C1], [UnionAll3].[C3] AS [C2], [UnionAll3].[C4] AS [C3], [UnionAll3].[C5] AS [C4],... . Because both the update and the select issue table scans they are pretty much guaranteed to deadlock when the update attempts to convert the IU page locks it acquired in the scan phase to IX locks needed to update the qualified rows.

You have two alternatives to pursue right now:

fix your application. The queries are clearly not tuned and there was no thought given to the indexing strategies. You will not only fix the deadlock problem, but your app will also be much faster.
the easy cop out: enable read committed snapshot. This will not fix the application flaws, but will hide them and eliminate the deadlocks.

Debugging a SSIS OleDbCommand

You can check what's going on using SQL Profiler. When you execute an OLE DB task, you should see something like this as an RPC:Completed event:

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 int',N'update testtable set id = @P1',1
select @p1

In this case, I'm just updating a test table (called testtable).

Then, for each iteration, you'll see an RPC:Completed event which reads something like:

exec sp_execute 1,50

This is saying to execute the first prepared event (our update statement--sp_prepare's first parameter is "1" here), and send in 50 as the new ID for testtable.

If you don't see these calls on your database as you debug your flow, it might indicate that you're actually doing the process on a different instance (or maybe there's something like a trigger preventing your change from occurring?).