SSIS 错误-无法执行事务操作，因为有处理此事务的待处理请求 [英] SSIS ERROR-The transaction operation cannot be performed because there are pending requests working on this transaction

问题描述

在执行 ssis 包时，我收到以下错误:无法执行交易操作，因为有处理此交易的待处理请求."

While executing an ssis package,I am getting the below error: "The transaction operation cannot be performed because there are pending requests working on this transaction."

ssis 包有 4 个并行运行的执行 sql 任务.连接是 ADO.Net.

The ssis package has 4 execute sql tasks that run parallel. The connection is ADO.Net.

execute sql 任务调用具有事务、回滚和提交的过程，执行 1 或 2 执行 sql 任务失败，其余成功.

The execute sql tasks calls a procedure which has transaction,rollback and commit, On execution 1 or 2 execute sql tasks fail and remaining succeeds.

另外，当任何一个sql任务失败时，其他执行sql任务的数据也会回滚.

Also, On failure of any of the sql tasks, data of other execute sql tasks also rollbacks.

我希望这些execute sql任务相互独立运行.数据应该只回滚到具有并行运行的任务的所有记录而不是所有记录的任务.

I want these execute sql tasks to run independent of each other.The data should be rolled back only for the tasks that have instead of all the records of the tasks running in parallel.

推荐答案

您可能遇到了需要终止的死锁.您可以通过进入 sql server management studio 并运行以下命令来查看:

You may have a deadlock that needs to be killed. You can see this by going into sql server management studio and running the following:

SELECT * FROM SYSPROCESSES where blocked > 0

然后，如果这样做是安全的，则通过其 ID 终止进程，例如

Then if it is safe to do so, kill the process by its ID e.g.

杀死99

如果这是夜间 ETL 过程的一部分，您可能需要考虑在 SQL 代理中创建 4 个不同的作业步骤，并在夜间的不同时间执行它们.这可能会解决您遇到的问题.

If this is part of a nightly ETL process, you may want to consider creating 4 different job steps in SQL Agent and executing them at different times during the night. This will possibly get around the issues you are having.

否则，如果执行 SQL 任务需要在同一个包中运行，您将需要检查它们的运行顺序.例如.运行首先执行 sql 任务，然后移动到下一个 - 例如将序列容器添加到您的控制流中，以确保第一个执行 sql 任务在下一个运行之前完成.

Otherwise, if the execute SQL tasks need to be run in the same package, you will want to check the order they are run in. e.g. run first execute sql task, then move onto the next one - e.g. add a sequence container to your control flow to make sure the first execute sql task is finished before the next is run.

这篇关于SSIS 错误-无法执行事务操作，因为有处理此事务的待处理请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！