Bringing the Transaction mode with SSIS into reality.

  • Article

Isn’t it a desirable request to run your Data flow Tasks into Transaction Mode that rolls back if something wrong happened in the middle. Will the functionality is there but you’ll need some configuration to bring it into life. Let’s have a look together.

First you want to change the TransactionMode in the DataFlowTask properties from supported to Required

Now you need a log holder for holding the data until the Commit or rollback. The Distrebuted Transaction Coordinator " DTC" is your target we need to enable it as follows

1. Go to the component services in the administrative tools of Control Panel.

2. Expand component services > Computers > My Computer > Distrebuted Transaction Coordinator

 

3. Then right click go to properties security tab.

4. Check Network DTC Access

5. Check Allow Remote Clients

6. Check Allow Inbound

7. Check Allow Outbound

8. Choose No Authuntication required

9. Check Enable SNA LU 6.2 Transactions

10. Then confirm it’ll require the service restart.

 

After this you can try to run the Data flow in transaction mode. If you received “Bulk Insert with another outstanding result set should be run with XACT_ABORT on”

Switch off the Bulk Insert in the Data Destination component that’s in your Data Flow tasks. As it triggers an error regarding Deadlock checker of SQL Server 2008 r2 more info could be found here

https://support.microsoft.com/kb/2412203

Hope this Helps !!! and say goodbye to debugging data what inserted and what not inserted after errors