SSIS 中的事务选项 [英] TransactionOption in SSIS

问题描述

我已经创建了一个 SSIS 包.我需要将事务应用于此包以在包失败时进行回滚.我发现的是一个属性TransactionOption"，它应该被赋予必需".我对吗 ?我已经将包的 TransactionOption 设置为必需"但是当我执行时包失败了.将 TransactionOption 设置为必需"我应该执行哪些所有步骤?

I have created a SSIS package. I need to apply Transaction to this package for rollbacking in case the package fails. What I found is a property "TransactionOption" which should be given "Required". Am I right ? And I have set TransactionOption for the package as "Required" But the package fails when I executed. What all steps should I do for setting TransactionOption "required" ?

详细来说，我的包包含 4 个控制任务.其中之一是数据流任务，包含查找任务.

In detail, My package contains 4 control tasks. One of them is a Data Flow Task, containing Lookup Tasks.

我收到以下错误[执行 SQL 任务] 错误:无法获取连接SQLConnectionMgr1".连接可能未正确配置，或者您可能没有对此连接的正确权限."

I got the following error "[Execute SQL Task] Error: Failed to acquire connection "SQLConnectionMgr1". Connection may not be configured correctly or you may not have the right permissions on this connection."

当我将 TransactionOption 设置为支持"时，程序包运行成功.

When I set the TransactionOption to "Supported", the packages runs successfully.

推荐答案

1. 创建 2 个OLE DB 连接管理器"，它们都连接到相同的数据库.关于OLE DB 连接"之一的属性manager" 将 'RetainSameConnection' 属性设置为 true.
2. 然后创建 3 个执行 SQL 任务"并将它们与OLE DB连接管理器"设置了RetainSameConnection"属性为真.

3. 在相应的执行 SQL 任务上添加以下语句

1. Create 2 "OLE DB Connection managers" both connecting to the same database. On the properties of one of the "OLE DB Connection managers" set the 'RetainSameConnection' property to true.
2. Then create 3 "Execute SQL Task" and connect them with the "OLE DB Connection managers" were the 'RetainSameConnection' property is set to true.

3. Add the following statements on there respective Execute SQL task

• 开始交易
• 提交交易
• 回滚交易

这篇关于SSIS 中的事务选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！