SSIS 事务管理 MSSQL [英] SSIS transaction management MSSQL
问题描述
如果复制失败，我需要将数据从数据库源"复制到数据库目标"，我需要回滚目标".这两个连接在连接管理器中定义为 OLE DB.
I need to copy data from DB "source" to db "destination" should the copying fail, I need to roll back on "destination". The two connections are defined in the connection manager as OLE DB.
这是我目前的尝试，但无效.我尝试使用内置的事务管理器(将任务事务设置为必需)，但这只会导致无法连接到目标".
Here is my current attempt which is not working. I tried playing around with the in-built transaction managemen (setting the tasks transaction to required) but that only made it impossible to connect to "destination".
目的地已将RetainSameConnection"设置为 true，而对于源"而言，此设置为 false，无特殊原因.
The destination has set "RetainSameConnection" = true, while this is false for "source" for no particular reason.
我还设置了 "MaxConcurrentExecutables" = 1 以阻止 SSIS 作为第一件事执行我的回滚.
I also set the "MaxConcurrentExecutables" = 1 in to hinder SSIS from executing my rollback as the first thing.
序列中的每个任务都设置为隔离级别"=ReadUncommitted 和transactionOption"=supported.
Each of the tasks in the sequence is set to "Isolation level"=ReadUncommitted and "transactionOption"=supported.
失败脚本"是一个总是失败的脚本，以便我测试交易是否正常工作.
The "failing script" is a script that always fail in order for me to test the transaction is working.
任务begin tran"的代码为BEGIN TRANSACTION"，连接设置为destination"
The code for the task "begin tran" is "BEGIN TRANSACTION " and the connection is set to "destination"
任务rollback tran"的代码为rollback transaction"，连接设置为destination"
The Code for the task "rollback tran" is "rollback transaction" and the connection is set to "destination"
回滚失败，提示回滚事务请求没有对应的‘BEGIN TRANSACTION’"
The rollback fails with "the rollback transaction request has no corresponding 'BEGIN TRANSACTION'"
推荐答案
您在这里混合了两个概念.在 SSIS 中有两种方法可以实现事务.第一个是 SSIS 事务.在这里，您的包应设置为 TransactionOption = Supported，您的容器应设置为 TransactionOption = Required(这将开始交易)，然后您的两个数据流任务将需要设置为TransactionOption = Supported，这将使它们都加入打开的事务.但是，请注意此选项需要分布式事务协调器，而且没有办法解决这个问题.
You are mixing two concepts here. There are 2 ways to achieve transactions in SSIS. The first is SSIS Transactions. Here, your package should be set to TransactionOption = Supported, you container should be set to TransactionOption = Required (which will begin a transaction) and then your two Data Flow Tasks would need to be set to TransactionOption = Supported, which would make both of them join the open transaction. However, please not that this option requires Distributed Transaction Coordinator and there is no way around that.
实现事务的第二种方式是使用 SQL Native Transactions.在这里，您将有一个启动事务的执行 SQL 任务，然后是您的数据流任务，然后是另一个提交事务的执行 SQL(当然还有另一个要回滚的).这里的问题是，要求我刚刚提到的所有任务使用相同的连接管理器，并且在该连接管理器上retainsameconnection = True 否则它将不起作用，因为 SSIS 和 SQl Server 仍然将其视为分布式事务，即使它们不在同一台服务器上，并且您必须使用 BEGIN DISTRIBUTED 事务，这再次需要分布式事务协调器.我还记得分布式本机 SQL 事务在 SSIS 中无法正常工作.
The second way of achieving transactions is with SQL Native Transactions. Here, you would have an Execute SQL Task that starts a transaction, followed by your Data Flow Tasks and then another Execute SQL that commits the transaction (and of course another to rollback). The issue here, is that it is a requirement that all of the tasks I have just mentioned Use the same connection manager and that retainsameconnection = True on that connection manager otherwise it will not work, as SSIS and SQl Server still regard it as a distributed transaction, even though they are not on the same server, and you would have to use BEGIN DISTRIBUTED transaction, which again requires Distributed Transaction Coordinator. Also I recall that Distributed Native SQL Transactions do not work properly in SSIS.
简短的回答是，您无法通过 SSIS 中的事务实现您想要做的事情.另一种方法是使用补偿块.在这里，如果插入失败，您将有一个执行 SQL 任务，根据时间或 SELECT MAX(ID) 删除您刚刚插入的数据，哪个最适合您的要求.
The short answer is that you cannot achieve what you are trying to do with transactions in SSIS. An alternative would be to use a compensation block. Here, on failure of insert, you would have an Execute SQL Task that deletes the data you have just inserted, based on either Time, or a SELECT MAX(ID), which ever suits your requirements best.
这篇关于SSIS 事务管理 MSSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
