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>
