Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 3 tasks in a SSIS package. If the final task fails I only want that task to rollback.

Do I just set that task to REQUIRED? Or leave it as SUPPORTED?

Thanks!

share|improve this question
1  
What do you want to have happen if task 1 or 2 fails? –  Eric Higgins Jan 30 at 22:37
    
they only select/filter data so no need to rollback –  k09 Jan 30 at 22:57

1 Answer 1

SSIS Transaction Level

The default transaction level for all tasks and containers is Supported which means they will enlist in an transaction if available but they will not create one..

A Required settings will start a transaction if one does not exist and enlist in an existing transaction.

NotSupported indicates that the Executable/Container will ignore any existing transactions which may result in deadlocks depending on your design.

Your case

You have 3 Tasks and the desire is to have the third item behave in an atomic way. The answer is, partially, that you'll want to set the Transaction option to Required. Partially because you can commit throughout your data load. In an OLE DB Destination, if you have a non-zero commit size then the committed data is fully committed. A rollback isn't going to pull that data back out.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.