5

I need to copy data from DB "source" to db "destination" should the copying fail, I need to roll back on "destination". The two connections are defined in the connection manager as OLE DB.

Here is my current attempt which is not working. I tried playing around with the in-built transaction managemen (setting the tasks transaction to required) but that only made it impossible to connect to "destination".

The destination has set "RetainSameConnection" = true, while this is false for "source" for no particular reason.

I also set the "MaxConcurrentExecutables" = 1 in to hinder SSIS from executing my rollback as the first thing.

Each of the tasks in the sequence is set to "Isolation level"=ReadUncommitted and "transactionOption"=supported.

The "failing script" is a script that always fail in order for me to test the transaction is working.

enter image description here

The code for the task "begin tran" is "BEGIN TRANSACTION " and the connection is set to "destination"

The Code for the task "rollback tran" is "rollback transaction" and the connection is set to "destination"

The rollback fails with "the rollback transaction request has no corresponding 'BEGIN TRANSACTION'"

Carlo V. Dango
  • 13,322
  • 16
  • 71
  • 114
  • did you try setting `transactionoption = required` for sequence container? – rs. Oct 22 '12 at 13:26
  • Yes I described that as not working. It complains "..distributed transaction with error 0x8004D025. the partner transaction manager has disable its support for remote/network transactions.". And I'm not interested in a distributed transaction across multiple computers. – Carlo V. Dango Oct 22 '12 at 13:32

1 Answers1

8

You are mixing two concepts here. There are 2 ways to achieve transactions in SSIS. The first is SSIS Transactions. Here, your package should be set to TransactionOption = Supported, you container should be set to TransactionOption = Required (which will begin a transaction) and then your two Data Flow Tasks would need to be set to TransactionOption = Supported, which would make both of them join the open transaction. However, please not that this option requires Distributed Transaction Coordinator and there is no way around that.

The second way of achieving transactions is with SQL Native Transactions. Here, you would have an Execute SQL Task that starts a transaction, followed by your Data Flow Tasks and then another Execute SQL that commits the transaction (and of course another to rollback). The issue here, is that it is a requirement that all of the tasks I have just mentioned Use the same connection manager and that retainsameconnection = True on that connection manager otherwise it will not work, as SSIS and SQl Server still regard it as a distributed transaction, even though they are not on the same server, and you would have to use BEGIN DISTRIBUTED transaction, which again requires Distributed Transaction Coordinator. Also I recall that Distributed Native SQL Transactions do not work properly in SSIS.

The short answer is that you cannot achieve what you are trying to do with transactions in SSIS. An alternative would be to use a compensation block. Here, on failure of insert, you would have an Execute SQL Task that deletes the data you have just inserted, based on either Time, or a SELECT MAX(ID), which ever suits your requirements best.

Pete Carter
  • 2,691
  • 3
  • 23
  • 34
  • Pete have you tried to setup similar configuration to this from question? I've done this (without second dataflow) using ssis 2012 (from BIDS) and sql-srv-2012 on different machine and it works. With default islotation level = serializable and when I change it to read uncommitted. Failure of script causes rollback of the SQL transaction. So it's possible, however I have no idea why Carlo has an error. – Piotr Sobiegraj Oct 22 '12 at 21:03
  • @piotr sobiegral - yes I have done extensive work with SSIS transactions. I have not tried this in 2012 and i will have to give it a go but Calos screenshot is 2008 and it definatly will not work in 2008 without DTC or the same connection manager. Remember 2012 was a big overhaul. – Pete Carter Oct 23 '12 at 04:02
  • 1
    @PeteCarter - An example for transactions with TSQL instead of just SSIS is given here - http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html Its really simple. Worked for me. – Steam Nov 11 '13 at 18:55