Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ssis sequence containers

Posted on 2010-09-07
12
837 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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