Alternative way to performing rollback or commit transaction

Question

I Have a task which begins with truncating the destination table and then goes into the data flow task. Inside the data flow task I have a source file which is then loaded to the target table through an OLE db destination and if it is successful it commits the transaction and if there is an error it rollbacks the transaction including the table that was truncated. Is there anyway I can do away with the sequence container and still have a begin transaction along with rollback and commit transaction? Please provide alternatives, I will rate highly. Thank you in advance.

Answer 1

As it works now, the rollback branch will be fired if there is a failure in either the SQL Truncate Table step OR the SQL Insert Data Into step.

If neither of those tasks, or the sequence container itself generated a failure, then the Success path of "SQL Commit Transaction" will execute.

To remove the sequence container, the success precedent constraint from SQL Insert Data Into will be mapped directly into the SQL Commit Transaction.

The Failure path from both SQL Truncate Table and SQL Insert Data Into will map to SQL Rollback Transaction. At this point, the only way SQL Rollback Transaction will fire would be if BOTH truncate and insert data failed. Logically, that can't happen so you will need to modify the failure precedent constraint to be an OR condition.

I'm sure you have a valid reason for making this modification but were I involved in this project, I would advocate for leaving the pattern as it is instead of complicating matters. Visually, I can see that the tasks in the sequence container are intended to be an atomic unit - either it all works or it gets reset. Breaking it out into individual pieces detracts from that. I do award full marks for the Jamie Thomson naming convention on the tasks, though.

Answer 2

SSIS offers transaction support via MS Distributed Transaction Coordinator. It has some limitations and has a performance overhead, but it works. Transaction can be specified on scope of single task or Sequence Container, i.e. the transaction will be committed if all steps of the Sequence Container worked without error, otherwise the transaction will be rolled back. It is done automatically, no need to execute special rollback task.
Some people do not like DTC or have problems setting it up, and try use native MS SQL transactions, which are handled with BEGIN TRAN, COMMIT TRAN and ROLLBACK TRAN commands. To my experience, this approach works well only if you could put all your data manipulation actions in one SQL Task command.
If you have to use several SSIS Tasks and want to set up MS SQL Transaction on it, here are some limitations and guidelines:

• MS Transaction lives inside connection session, it cannon spawn several connections (it will become a Distributed transaction in that case). Which means:
  1. You have to specify RetainSameConnection=true property on Connection Manager
  2. You have to design your tasks and data flows to use only one connection. If you use a Dataflow where Source and Destination access the same database (same connection manager) - you are out of luck, this creates two DB connections and breaks your Transaction. Same problem for Lookups inside DataFlow.
• Working with temp tables in SSIS and native MS Transaction is a PITA.

Bottom line - I prefer working with MS DTC despite its performance penalty and setup complexity. Native MS SQL Transactions across several task showed itself unstable and unreliable, the only way to deal with it - put it inside one SQL Command.