4

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

  • run profiler when the ssis package is running; check for errors; might be triggers are failing with permissions or some other error. – SqlACID Feb 4 '16 at 21:22
  • No errors are reported in profiler trace – philcart Feb 5 '16 at 0:58
3

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.

enter image description here

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

  1. Once installed, right click on an SSIS project and select Add new Biml File
  2. 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
  3. 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>
1

The following will show you if the triggers are enabled. If they are, they will fire, assuming the DML meets the trigger requirements. You should run this while the SSIS package is running.

SELECT TableName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , TriggerName = t.name
    , t.is_disabled
FROM sys.triggers t
    INNER JOIN sys.objects o ON t.parent_id = o.object_id 
        AND t.parent_class_desc = 'OBJECT_OR_COLUMN'
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id;

The SSIS package may be disabling the triggers while its code is running.

  • Ok, will set that up and see if it leads anywhere – philcart Feb 5 '16 at 0:58
  • Have been monitoring trigger status all afternoon and we've had six data loads in that time. After each load data has had to be manually synced with the other system. No errors or other questionable activity apparent in SQL Profiler trace, no indication of triggers being disabled. – philcart Feb 5 '16 at 5:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.