SSIS Transaction Problem

Question

I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback.

I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to "Required".

But it fails to execute the package and gives DTS error . (Everything is working fine if i remove the Data Flow from Sequence Container).

i have done the changes on server as described here: http://www.databasesupport.eu/categories/sql-server/1482-ssis-distributed-transactions

Could you please tell me if there is something i am missing. Do i need to do DTS settings on both the servers from where i am importing the data and to which i am importing.

Please tell what i am missing. I am very new to SSIS.

Answer 1

Yes. You will need to configure all the servers involved with your process to be of DTC.

When you use the native SSIS transaction capability, if the connection managers are entirely self contained, then behind the scenes a standard BEGIN TRAN...COMMIT|ROLLBACK are issued. When there are multiple servers involved, that's when the distributed transaction coordinator comes into play. Even if you're only reading from a source table, SSIS will attempt to enlist that SELECT statement in a transaction.

We learned this the hard way when dealing with DB2 as they apparently have the ability to enable/deny transaction stuff. We also had issues using an antiquated MySQL instance as it didn't support the concept of a transaction. In that instance, we had two data flows. One ran without a transaction and wrote to a local file, then used a transaction controlled data flow to read that file into our target table. Not an advisable approach for very large data sets but worked fine for our problem set.