回滚事务在 SSIS 中不起作用 [英] Rollback Transaction not working in SSIS

问题描述

我的 SSIS 包的 ForEach 循环中有以下序列容器:

I have the following Sequence Container inside of a ForEach loop in my SSIS package:

我正忙于测试 ROLLBACK TRANSACTION 语句，它可以正常执行，但不会回滚.

I am busy testing the ROLLBACK TRANSACTION statement, it executes fine but it does not rollback.

不确定我是否遗漏了什么?

Not sure if I am missing anything?

提前致谢.

这是我的序列容器中数据流的样子:

This is how the data flow looks like in my Sequence Container:

推荐答案

我采取了另一种方式来确保我的故障转移有效.我所做的是在我的表中添加了一列来存储 GUID，当出现错误时，我只是从该 GUID 等于该会话中使用的 GUID 的表中删除记录.我在我的 ForEach Container 将使用的选择语句中添加了 GUID，然后将其写入表中.

I took an alternative route to make sure that my fail over works. What I did was I added a column to my tables that will store a GUID and when there is an error I just delete records from tables where that GUID is equal to the one in that is used in that session. I added the GUID in my select statement that my ForEach Container will use and then I write it to the table.

SELECT
    ReconMedicalAidFile.fReconMedicalAidFileID
    ,ReconMedicalAidFile.fReconMedicalAidID
    ,ReconMedicalAids.fMedicalAidID
    ,ReconMedicalAidFile.fFileName
    ,ReconMedicalAidFile.fFileLocation
    ,ReconMedicalAidFile.fFileImportedDate
    ,ReconMedicalAidFile.fNumberRecords
    ,ReconMedicalAidFile.fUser
    ,ReconMedicalAidFile.fIsImported 
    ,CONVERT(varchar(50),NEWID()) AS 'Session'
FROM ReconMedicalAidFile 
INNER JOIN ReconMedicalAids ON ReconMedicalAidFile.fReconMedicalAidID = ReconMedicalAids.fReconMedicalAidID
WHERE (fIsImported = 0) AND (fNumberRecords = 0)

我在脚本任务中添加了这段代码，它将映射上面选择的 GUID:

I added this code in the Script Task which will map the selected GUID above:

DELETE FROM BankmedStatments
WHERE fSession = ?

DELETE FROM ReconMedicalAidData
WHERE fSession = ?

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