Post reply

Execute SQL Task Contorl Flow and BEGIN\COMMIT\ROLLBAK Transaction

  • October 12, 2008 at 5:49 pm

    #199548

    I created an SSIS (2005) package using an Execute SQL Task in the Control Flow pane which calls a stored proc. I set the task's "TransactionOption" property to Required, "twiddled" with the proc to make it fail, and this caused the transaction to be rolled back.

    Question: it appears that if TransactionOption is Required, then I do not need to use BEGIN\COMMIT\ROLLBACK in my t-sql. Is this cortrect or should I used BEGIN.. anyway?

    TIA,

    barkingdog

  • October 13, 2008 at 8:38 am

    #884181

    Setting TransactionOption to required means that the task MUST have a transaction. If one is already started, it will join to it. If none already exists, it will start one.

    To answer your question: You are correct...since you forced a transaction to start for your Exec SQL task with TransactionOption = Required, SSIS will start a transaction and roll it back if your SQL fails.

Viewing 2 posts - 1 through 1 (of 1 total)

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