Post reply

Explicit Transactions Vs TransactionOption in SSIS

  • December 19, 2012 at 7:55 pm

    #268361

    Hi Friends,

    I am going to implement Transaction in a package. I thought of using the Transaction feature available in SSIS. This needed the DTC to be started and changing the TransactionOption for the components involved in the the Transaction.

    One of my colleagues told me the other way to implement the Transaction. That is explicitly creating a transaction in SSIS using Execute SQL Task. So in this case, in case of a failure we would be executing a Rollback command. To commit the Transaction we would be having a Commit statement as the last task in SSIS.

    I felt that the second option would be better since it does not need the DTC to be started. I would like to know if there are any negatives in using the second option.

    Your inputs would be appreciated.

  • December 20, 2012 at 12:19 am

    #1570416

    The second option will only work if the transaction involves only SQL Server related tasks, and they all must use the same connection manager (you need to set the RetainSameConnection property on the connection manager to true).

    However, in most cases of typical DWH ETL scenarios, this is the case and you can apply this scenario. I usually use the second option.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 20, 2012 at 4:40 am

    #1570484

    Hey Koen,

    Thanks for your response. I found that there are 2 tasks which you can use for this purpose. OnError and onTaskFailed. All I am going to do here is to put Execute SQL task with ROLLBack command. So which Event Handler shoudl i use here.

    Should I also set the FailParentonFailure option of the components to failure. My aim is to Rollback the transaction if any of the component fails. Will setting the onTaskFailed EventHandler at the package level alone work or should i use the FailParentonFailure option as well

  • December 20, 2012 at 5:05 am

    #1570490

    I personally wouldn't use event handlers, but use precedence constraints.

    I'd put all the tasks in one sequence container. After the sequence container, I would put two Execute SQL Tasks: one with COMMIT and another with ROLLBACK. Connect the first one with the green arrow and the second one with the red arrow.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply