This article is a simple scenario based solution that my friends provide me to solve. This article is related to SSIS Transaction and How we use the Transaction Option in case of Parallel processing of Task. Hope it will be enjoyable.
We have a Sequence Container. In the Sequence containers we have two data flow tasks named Task-1 and Task-2. The Task-3 is outside the Sequence containers.
When we execute the package what we want is if the Task-1 or Task-2 fails, both the task will be Rolled Back but Task-3 will be executed successfully.
If the Task-3 failed it will be Roll Backed but Task-1 and Task-2 within the Sequence container must be Executed Successfully.
Hope you understand the Scenario.
The Control Flow Design of Package
In Sequence Container Property
TransactionOption = Required
In Task-3 Property
TransactionOption = Supported
Execute the Package Where Task - 2 Gives an Error
If we look carefully Task-2 is Failed and both the Task-1 and Task-2 are Roll Backed and Task-3 is Executed Successfully. So it supports the Scenario that I Mentioned above.
Execute the Package Where Task – 3 Gives an Error
In this case all the Tasks are Failed and Roll Backed which is not Supported the above mentioned scenario.
How we Solved it
To Solve it Just run the services named Distribution Transaction Coordinator (DTC)
Now Execute the Package Again Where Task – 3 Gives an Error
In this case only the Task-3 fails and Roll Backed but Task-1 and Task-2 Succeed.
Hope you like it.