SSIS 包中的事务传播 [英] Transaction Propagation in SSIS Packages

问题描述

以下是 SSIS 包中控制流执行的任务列表

Following are the list of tasks performed by control flow in the SSIS package

1. 使用执行 SQL 任务"从表中删除数据
2. 使用数据流任务"从 txt 文件中读取数据并将数据加载到表中
3. 使用执行 SQL 任务"从表中删除一些行(基于业务逻辑)
4. 使用执行 SQL 任务"更新表中的某些行(基于业务逻辑)

我的疑问是，如果第4步出现错误，SSIS会自动回滚到第1步之前的状态吗?

My doubt is, if there is an error in step 4, will SSIS automatically rollback to the state before step 1?

谁能帮我找到正确的方向?任何帮助表示赞赏.

Can anyone help me in the right direction? Any help is appreciated.

谢谢！

推荐答案

一般来说，每个任务都是原子的.任务 4 将完成或将不完成，不会发生半途而废.如果您需要包作为一个整体成功/失败，那么您需要对其进行配置.

Generally speaking, each task is atomic. Task 4 will or will not complete, no half-measures will happen. If you need the package to succeed/fail as a whole, then you will need to configure it as such.

最简单的选择是对包使用内置的事务选项.这使用 Microsoft 分布式事务协调器 (MSDTC) 来处理跨各种服务器的事务.在您的包中，右键单击控制流的背景并将 TransactionOption 从 Supported 更改为 Required 有关所有工作原理的更多详细信息如何在同一个事务中并行运行多个数据流任务?

The easiest option is to use the built in transaction option for packages. This uses the Microsoft Distributed Transaction Coordinator (MSDTC) to handle transactions across various servers. In your package, right click on the background of your Control Flow and change the TransactionOption from Supported to Required More detail about how all that works over on How do I run multiple data flow tasks in parallel within the same transaction?

如果 MSDTC 由于某种原因对您不起作用，那么您需要将连接管理器设置为 RetainSameConnection 并将开始传输/提交/回滚逻辑添加到您的包中，请参阅SSIS:默认登录 OnError 不适用于 RetainSameConnection 为例，但这会只有在所有任务都在同一个数据库中执行时才有效.

If MSDTC doesn't work for you for whatever reason, then you would need to either set the connection manager to RetainSameConnection and add a begin tran/commit/rollback logic into your package See SSIS: Default Logging OnError don't work with RetainSameConnection for an example but that will only work if all the tasks are performed in the same database.

如果 MSDTC 不起作用并且您将修改多个数据库中的数据，那么您需要编写自己的回滚"脚本，并让它们在 OnError/OnTaskFailed 事件或错误分支中触发控制流.在您发布更新和删除操作时，需要先实施变更数据捕获机制，然后才能撤消这些操作.

If MSDTC doesn't work and you will be modifying data in more than one database, then you'll need to write your own "rollback" scripts and have them fire in the OnError/OnTaskFailed events or the Error branch in your control flow. As you are issuing updates and deletes, that will require a change data capture mechanism be implemented prior to you being able to undo those operations.

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