1

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.

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.

  • hi Bill, Even after enabling the DTC on both servers , i am getting an like this: Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted". – Mihir Chittora Feb 27 '15 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.