Explicit Transactions Vs TransactionOption in SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Explicit Transactions Vs TransactionOption in...

Explicit Transactions Vs TransactionOption in SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

MuraliKrishnan1980

Posted Wednesday, December 19, 2012 7:55 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:01 AM
Points: 111, Visits: 1,333

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.

Post #1398740

Koen Verbeeck

Posted Thursday, December 20, 2012 12:19 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1398819

MuraliKrishnan1980

Posted Thursday, December 20, 2012 4:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:01 AM
Points: 111, Visits: 1,333

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

Post #1398908

Koen Verbeeck

Posted Thursday, December 20, 2012 5:05 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

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.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1398914

« Prev Topic | Next Topic »

Permissions