Solved

ssis sequence containers

Posted on 2010-09-07
12
832 Views
Last Modified: 2013-11-10
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
Comment
Question by:Mr_Shaw
  • 5
  • 5
  • 2
12 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 300 total points
ID: 33619717

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
 

Author Comment

by:Mr_Shaw
ID: 33619860
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
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 33619976
YES
YES
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 33619995
You can change that behavior, if one step fails the whole container fails.
but I don't think that's what you want.
0
 

Author Comment

by:Mr_Shaw
ID: 33620162
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
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 300 total points
ID: 33620319
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
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 300 total points
ID: 33620326
This is good about checkpoints, just for your knowledge HERE
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 200 total points
ID: 33620688
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
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 200 total points
ID: 33620695
another sample of transaction is here:
http://www.mssqltips.com/tip.asp?tip=1585
0
 

Author Comment

by:Mr_Shaw
ID: 33622305
thanks i'll have a look at work tomorrow and dish out the points!
0
 

Author Comment

by:Mr_Shaw
ID: 33625008
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
 

Author Closing Comment

by:Mr_Shaw
ID: 33625400
thanks
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question