Explicit transaction declaration in SSIS causes 'double' rollback exception

Question

Working with SSIS, here is my control flow

But here is the catch: When an exception occurs in the Sequence Container and the flow goes to the Failure route, I get an error on the Rollback Transaction telling me the transaction as already been rollback.

Here are the key info:

• I'm using RetainSameConnection=True on both of my Connection Manager (used by the different dataflow). It's not a distributed transaction, one of the connection is purely for read purpose.
• I have an event handler with propagate to false set on the Sequence Container.
• All Transaction option are set to 'Supported'.
• My transaction is explicitly declared (Begin, Commit and Rollback).

It seems as if my transaction get enroll into a Distributed Transaction (DTC) and are rollback by the sequence container so when I try to roll it back by myself, it generates a second exception failing the whole package.

I haven't being able to reproduce the problem on my local machine, only in prod environment. Any idea anyone?

Answer 1

A partial answer/solution to this question is:

• The transaction is rollback automatically on an SQL Type Exception (ex: duplicate key, etc.) event tough it's declared. The rollback part of the flow is therefore only use when some other exception occurs.

How do we manage such a behavior?

• We have to use some database specific command (in my case SQLServer) to validate if we are still in a transaction before rollbacking it, otherwise we'll get an Exception that trying to rollback a unexisting transaction.

Here is the code in my case:

    IF @@TRANCOUNT > 0               
         Rollback Transaction TX_NAME;

Hope it helps someone one day ;-)