如何在同一事务中并行运行多个数据流任务? [英] How do I run multiple data flow tasks in parallel within the same transaction?

问题描述

我在一个序列容器中添加了一个包含 3 个并行数据流任务的包.我需要实现这样的事务，如果所有 3 个数据流任务都成功执行，则事务完成.如果任何数据流任务失败，那么事务应该回滚.

I have a package with 3 parallel data flow tasks added in a sequence container. I need to implement transaction such that, the transaction completes if all the 3 data flow tasks successfully execute. If any of the data flow task fails, then transaction should rollback.

我正在测试这个包，但它不起作用.

I am testing this package but its not working.

• 我拿了一个sequence container，拖放了data flow task 1、data flow task 2和data流任务 3 并配置了所有三个数据流任务.

• I took one sequence container and dragged and dropped data flow task 1, data flow task 2 and data flow task 3 and configured all three data flow tasks.

在sequencee容器的属性中，我设置了以下属性

In the sequencee container's properties, I have set the following properties

交易:支持

隔离:readcommited

在三个数据流任务的属性上，我设置了以下属性

On the three data flow task's properties, I have set the following properties

交易:支持

隔离:readuncommited

当我运行包时，数据流任务 1 失败，但数据流任务 2 中的数据仍在插入和提交中.

When I run the package, the data flow task 1 fails but the data into data flow task 2 is still being inserted and committed.

即使其中一个数据流任务失败，如何防止数据流任务提交事务?

How can I prevent the data flow tasks from committing the transactions even if one of the data flow tasks fail?

推荐答案

我同意其他答案，您的问题要求您将封闭容器上的事务范围设置为 Required.除非您更改了内部对象，否则它们的默认事务级别为 Supported，这意味着如果可用，它们将加入事务.Required 设置将启动一个事务，并且为了完整性，NotSupported 表示可执行文件/容器将忽略任何可能导致死锁的现有事务，具体取决于您的设计.

I agree with the other answers, your problem requires you set the transaction scope on an enclosing container to Required. Unless you have altered the inner objects, their default transaction level is Supported which means they will enlist in an transaction if available. A Required settings will start a transaction and for completeness, NotSupported indicates that the Executable/Container will ignore any existing transactions which may result in deadlocks depending on your design.

我构建了一个示例包，该包删除并重新创建目标表以验证交易是否按预期运行.包中有 3 个数据流，每个数据流都向表 (1, 2, 4) 添加一个唯一值，这样这样的查询将指示值是否到达目标表.

I built out a sample package that drops and recreates a target table to verify transactions are behaving as expected. There are 3 data flows in the package, each one adding a unique value to the table (1, 2, 4) so that a query like this will indicate whether values arrived in the destination table.

SELECT count(1) AS rc, sum(T.col1) AS Total FROM dbo.TrxTest T

如您所见，有 7 个变量，3 个成对.FailDataFlow 命名的是布尔值，允许任何唯一的数据流失败/成功.这是通过在相应查询的 where 子句中引起除以 0 异常来实现的.

As you can see, there are 7 variables, 3 in pairs. The FailDataFlow named ones are booleans that allow any of the unique data flows to fail/succeed. This is accomplished by causing a divide by 0 exception in the where clause of the corresponding query.

序列容器具有 Required 的 TransactionOption.各个数据流保留其 Supported.

The Sequence Container has a TransactionOption of Required. The individual data flows retain their default TransactionOption of Supported.

第一次执行导致无法与分布式事务协调器通信，因为它被设置为在此 VM 上手动启动.纠正该问题导致包正确失败，因为DFT 值 2"生成了除以零异常.尽管DFT 值 1"上有一个绿色框，但运行上面的查询方式在我的表中没有显示任何内容.

The first execution resulted in an unable to communicate with the distributed transaction coordinator as it was set to manual startup on this VM. Correcting that problem resulted in the package correctly failing out as "DFT value 2" generated a divide by zero exception. Running the query way above showed nothing in my table despite the presence of a green box on "DFT Value 1".

将 FailDataFlow1 的值切换为 False 并重新运行，在我的查询中分别显示值 3 和 7，这表示所有行都已到达.

Switching the value of FailDataFlow1 to False and re-running showed values of 3 and 7 respectively in my query which indicates all rows arrived.

您可以通过更改各种容器/可执行文件上的事务选项来进一步探索，以确保它们像其他受访者指出的那样工作.

You can further explore by changing transaction options on various containers/executables to assure yourself they are working as the other respondents have indicated.

这篇关于如何在同一事务中并行运行多个数据流任务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！