SSIS transaction management MSSQL

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.

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'"

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.