Sql-Server

SSIS OLEDB 命令未觸發觸發器

  • March 16, 2016

有點奇怪,我似乎無法解決。

我們有一個位於 SQL Server 2012 SP2 上的供應商數據庫(因此我們無法控製表結構、外鍵等)。我們每天通過 SSIS 包載入大量數據。

SSIS 包使用 OLEDB 命令插入行,該命令只是執行儲存過程

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

此過程的簡化版本如下

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)

我們在涉及的每個表​​上都有觸發器,以將新插入的數據傳輸到另一個系統。

似乎正在發生的事情是,當從 SSIS 執行該過程時,觸發器不會觸發。從 SQL Management Studio 執行該過程時,一切都按預期工作。

任何人都對為什麼會發生這種情況以及我們如何解決問題有任何想法。

乾杯菲爾

我無法重現您的錯誤(儘管我只測試了一張混合表)。

OLE DB 目標能夠在載入時禁用觸發器和其他檢查約束,但 OLE DB 命令不提供類似的功能。

設置

我創建了一個簡單的表:當行更新時,將通過觸發器填充一個鍵、一個值和一個空欄位。我創建了一個觸發器,它將更新已修改行的空欄位。最後,我創建了一個儲存過程來處理實際執行的 UPDATE 語句。

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

我的包裹很簡單。具有 OLE DB 源和 OLE DB 命令的數據流。

在此處輸入圖像描述

我的 OLE DB 源使用以下查詢,它只計算 ASCII 表中 Bar 的下一個值,並引入原始值。A變成B,B變成C,C變成D。

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

OLE DB 命令使用以下語句。

EXECUTE dbo.dba_128344Update ?, ?;

OLE DB 連接管理器需要使用?基於零序號的替換系統。在這裡,我將列映射Foo到參數@FooNewBar參數,@bar因為我不想在我的外殼中保持一致……

執行後,正如預期的那樣,我看到我的日期在每行之間填充了約 1 秒的延遲,以與 OLE DB 命令的逐行性質相稱。

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,即商業智能標記語言,是一種 XML 方言,可以輸入翻譯器以生成 SSIS 包。它很光滑。您需要做的就是下載並安裝免費的外掛BIDS Helper

  1. 安裝後,右鍵點擊 SSIS 項目並選擇添加新的 Biml 文件
  2. 點兩下 BimlScript.biml 並粘貼以下內容 1,調整第 3 行,使其指向執行上述 DDL 的有效伺服器和數據庫
  3. 保存然後右鍵點擊 BimlScript.biml 並選擇“生成 SSIS 包”。嗖嗖出來 dba_128344.dtsx

試一試!!!

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

下面將顯示觸發器是否已啟用。如果是,它們觸發,假設 DML 滿足觸發要求。您應該在 SSIS 包執行時執行它。

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;

SSIS 包可能會在其程式碼執行時禁用觸發器。

引用自:https://dba.stackexchange.com/questions/128344