Introduction
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.
The Scenario
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
Enabled Transaction
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.
Please Note that if we Do not want to use the DTC in transaction, in this scenario we have a single connection against for all the Task and the Retain same connection property must be set TRUE.
ReplyDeleteNow we can use BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION within different task in SSIS.
MSDN Reference to Enable DTC
ReplyDeletehttps://msdn.microsoft.com/en-us/library/ms936442(v=cs.70).aspx
Another Good Article related to DTC Enabled in Clustered Server
ReplyDeletehttp://sqlha.com/2013/03/12/how-to-properly-configure-dtc-for-clustered-instances-of-sql-server-with-windows-server-2008-r2/