• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

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
0
Mr_Shaw
Asked:
Mr_Shaw
  • 5
  • 5
  • 2
5 Solutions
 
Jason Yousef, MSSr. 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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, MSSr. 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, MSSr. BI DeveloperCommented:
This is good about checkpoints, just for your knowledge HERE
0
 
Reza RadCommented:
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 RadCommented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now