Sql-Server

在 SSIS 包中創建事務時出現問題

  • December 16, 2015

我正在開發一個需要使用事務的包,但目前出現以下錯誤:

SSIS package "CATS-Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001401A at CATS-Package: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
SSIS package "CATS-Package.dtsx" finished: Failure.

到目前為止,這是我所知道的:

  • 2012 包
  • 我正在包部署中執行
  • TransactionOption 屬性設置為必需
  • 針對 2008 R2 實例執行
  • 我可以在實例上手動創建分佈式事務,並根據事件查看器 MSDTC 正在執行。
  • MSDTC 有以下設置

在此處輸入圖像描述

任何人都可以指出一個方向以找到更多資訊。

**編輯:**我讀到的步驟之一是在執行包的本地機器以及持有實例的伺服器上執行 DTC。一旦我這樣做了,我得到了一個新的錯誤:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

重要的部分The transaction manager has disabled its support for remote/network transactions. 是我在權限中遺漏了什麼嗎?注意:Allow Remote Clients也已檢查。

我已經看到提到的 MSDTC ping(包括在下面的評論中),但是當我查看它時,我不太確定如何處理它。

我解決了這個問題。我在幾個地方讀到需要在源機器和目標機器上啟動 DTC。所以在我的位置,我的工作站以及實例所在的伺服器。

打開 MSDTC 後,我收到一個新錯誤:

Information: 0x4001100A at CATS-Package: Starting distributed transaction for this container.
Error: 0xC001402C at CATS-Package, Connection manager "connectionName": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
Error: 0xC0202009 at CATS-Package, Connection manager "connectionName": SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D024.
Error: 0xC00291EC at Execute SQL Task - Max Product ID, Execute SQL Task: Failed to acquire connection "connectionName". Connection may not be configured correctly or you may not have the right permissions on this connection.

重要的部分是The transaction manager has disabled its support for remote/network transactions.

在那裡我進行了額外的研究,最終意識到我沒有將本地 DTC 配置為允許網路訪問等。一旦我將其配置為允許網路 DTC 訪問、允許遠端連接和允許輸出連接,一切工作順利。

在此處輸入圖像描述

我不確定這是本地連接所需的最低權限,但它似乎是。

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