主页 / dba / 问题 / 122610
Accepted
Kenneth Fisher
Asked: 2015-12-02 14:53:41 +0800 CST

在 SSIS 包中创建事务时出现问题

  • 772

我正在开发一个需要使用事务的包，但目前出现以下错误：

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（包括在下面的评论中），但是当我查看它时，我不太确定如何处理它。

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    我解决了这个问题。我在几个地方读到需要在源机器和目标机器上启动 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 访问、允许远程连接和允许输出连接，一切工作顺利。

    在此处输入图像描述

    我不确定这是本地连接所需的最低权限，但它似乎是。

    • 10

相关问题