title: SSIS transaction management MSSQL
body: <p>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.</p>
  
  <p>Here is my current attempt which is not working. I tried playing around with the in-built transaction managemen (setting the tasks transaction to <strong>required</strong>) but that only made it impossible to connect to "destination".</p>
  
  <p>The destination has set "RetainSameConnection" = true, while this is false for "source" for no particular reason.</p>
  
  <p>I also set the "MaxConcurrentExecutables" = 1 in to hinder SSIS from executing my rollback as the first thing.</p>
  
  <p>Each of the tasks in the sequence is set to "Isolation level"=ReadUncommitted and "transactionOption"=supported.</p>
  
  <p>The "failing script" is a script that always fail in order for me to test the transaction is working.</p>
  
  <p><img src="https://i.stack.imgur.com/oPS9k.png" alt="enter image description here"></p>
  
  <p>The code for the task "begin tran" is "BEGIN TRANSACTION            " and the connection is set to "destination" </p>
  
  <p>The Code for the task "rollback tran" is "rollback transaction"  and the connection is set to "destination" </p>
  
  <p>The rollback fails with "the rollback transaction request has no corresponding 'BEGIN TRANSACTION'"</p>