I was working in a package and I was requested to set the import task as transaction consistent. In SSIS you can do all sorts of ETL stuff so when you import data you need to make sure the import is in consistent state which means either all the data should gets loaded or it should be rolled back to the previous state. You can enable this option either in package level or container level or at task level. Based on your requirement you can set the transaction property so the final output is in consistent state.
We have a property called “TransactionOption” in SSIS which lets you to chose the transaction state of the package. You have 3 values to chose for this property and it’s below
NotSupported – Indicates that it won’t start any transaction or join any transaction that’s started by the parent object.
Supported – Indicates that it won’t start a new transaction but it will join the transaction that’s started by the parent object.
Required – Indicates that it will either start a new transaction or it will join the transaction that’s started but the parent object.]
So which one to use? By default the transaction option is set as Supported however incase of error or failure , to rollback to the previous state it need to set as Required. My requirement is to import the data from csv files where there is possibility the input data might be corrupted , so I need to rollback the package incase of failures. I have created a package to import the data from csv file and I have also corrupted the csv file (added char value to INT column as shown below). From image below you can see Column1 Row 4 has invalid data. Let’s do a test in supported as we as in Required values
My package is below, I have only data import task
Transaction Option Set to Supported
After creating the package I haven’t modified anything to the package which means that the transaction option is set to default. I have now executed the package and you see from the below result that it imported first three rows and then skipped invalid row & all other rows.
Transaction Option Set to Required
Now I’ve changed the transaction option to Required in this test as shown below. I have executed the package now and the result is below. None of the rows get imported. So to rollback your import incase of issues you need to mark the transaction option to Required.
I have tested this is SSIS 2012 however in all the 3 transaction options the data is getting roll backed incase off any issues.
Pre-Requisites to Set TransactionOption to Required
I have collected the SSIS errorlog when it got failed. From the screenshot below you can see a distributed transaction is used to commit or rollback the import, this means when you set the transaction option to Required it uses MSDTC (Microsoft Distributor Transaction Coordinator) . So you need make sure MSDTC service is running on the server.
- Go to Run—> type services.msc
- Make sure “Distributed Transaction Coordinator” service is up and running fine.
If you are running the package from client machine then you need to make sure this service is running fine in both the machines. In addition to this you need to configure MSDTC to allow remote clients so that client machines can start \ stop distributed transactions. To configure MSDTC try the steps below
Windows Server 2008 \ Windows 7
- Go to Run –> type dcomcnfg
- Expand “Component Services”—> Computer –> MY Computer –> Distributed Transaction Coordinator
- Now right click on “Local DTC” and then click on properties
- Now go to “Security” tab and then enable the options below
Windows Server 2003 \ Windows XP
- Go to Run –> type dcomcnfg
- Expand “Component Services”—> Computer
- Now right click on “My Computer” and then go to MSDTC tab
- Click on “Security Configuration” button and then set the properties below
Once you set the MSDTC properties you need to restart the machine then only these settings will take effect, so don’t forget this step. You need to set MSDTC properties in both client and server then only you will be able to execute the package or else you will be thrown with the error.
System.Transactions.TransactionException: The partner transaction manager has disabled its support for remove/remote transactions. (Exception from HRESULTL 0x8004D025)
In brief to rollback in SSIS Data import task you need to set the TransactionOption to Required which in turn depends on MSDTC so you need to make sure it’s started and configured as shown above. Once that’s done then your package is transaction consistent.