Search code examples
ssistransactionsssis-2012foreach-loop-container

SSIS 2012 Transaction in ForEach Loop

I have a package that iterates several times, one for each Category. I put a transaction in the package such that each Category completes in full or not at all. If I have 3 Categories, A B and C, and Category B fails, I want to ensure that A and C will run successfully. However, currently, when B fails, the package execution halts with an error instead of moving on to the next Category.

I have a ForEach Loop container that iterates over the Categories. Inside that, I have a sequence container with the Transaction option set to Required. This should make each iteration its own transaction.

How do I get that package to move onto the next Category when another Category fails and rolls back?

Thanks in advance for any help.

Solution

  • Change the maximum error count of the Loop container and all parent containers to 3, and changed the "stop package on failure" flag to false. This should allow it to continue looping.

    While this should work, I don't advocate this approach. I would split the three transactions into separate containers, connected by "Completion" constraints, since there may be errors that should still halt package execution.