ssis sequence containers

I have a sequence container which contains three data flows.

Each data flow contains tasks which insert data into a table.

My question is... if one of the data flows inside the sequence container fails will it prevent/rollback the data from the other two data flows?

Thanks
Mr_ShawAsked:
Who is Participating?
 
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:

Yes, it's totally different tasks, three different transactions !
The container will fail if any of the data flow contains tasks fails.

what you're trying to avoid exactly? the design logic will avoid errors, also error handling techniques will help you achieve what you want. as

0
 
Mr_ShawAuthor Commented:
What you are saying is that If task1 and task2 are a success but task3 fails the sequence container will fail.

Will the data from task1 and task2 commit?

0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
YES
YES
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jason Yousef, MSSr. BI  DeveloperCommented:
You can change that behavior, if one step fails the whole container fails.
but I don't think that's what you want.
0
 
Mr_ShawAuthor Commented:
No want I want is that if any of the tasks within the container fails the data will be rolled back on not commited.

Is this possible?
0
 
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
COMMIT TRANSACTION is only in T-SQL statements, read about it here

In SSIS there's something called checkpoints but not similar it only restart the package from the failed point, not the whole package, but I don't think that what you want !!
0
 
Jason Yousef, MSConnect With a Mentor Sr. BI  DeveloperCommented:
This is good about checkpoints, just for your knowledge HERE
0
 
Reza RadConnect With a Mentor Consultant, TrainerCommented:
you can use Transaction in SSIS,
you can set transaction on data flow and/or sequence container level,
look here for more details:
http://stackoverflow.com/questions/919302/ssis-transactions-large-record-sets


0
 
Reza RadConnect With a Mentor Consultant, TrainerCommented:
another sample of transaction is here:
http://www.mssqltips.com/tip.asp?tip=1585
0
 
Mr_ShawAuthor Commented:
thanks i'll have a look at work tomorrow and dish out the points!
0
 
Mr_ShawAuthor Commented:
here is a screen shot of my package.

If either Inpatients, Outpatients or AE fails I need to rollback/delete what was inserted into the table so that I can start again.
ssis-rollback1.bmp
0
 
Mr_ShawAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.