Rollback Transaction not working in SSIS

Question

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

enter image description here

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

Not sure if I am missing anything?

Thanks in advance.

EDIT:

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

enter image description here

Can you show the code you have inside of the ForEach loop container's components? Also what is the full code in the Begin Bankmed and Rollback Bankmed? — Zsuzsa
In your Connection Manager, have you specified RetainSameConnection=true? The default is false. — billinkc
@G-Man have you read this: stackoverflow.com/questions/13626755/… ? and this article: mssqltips.com/sqlservertip/1585/… — Jacques Bronkhorst
@JacquesBronkhorst yes I went through that already. I can't use a package transaction, there is a issue with how our SQL admin has been setup. The only solution so far is to execute script tasks — Gericke

Gericke Gericke · Accepted Answer · 2015-07-10T14:04:20

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)

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 = ?

Rollback Transaction not working in SSIS

1 Answers