[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

ssis sequence containers

Posted on 2010-09-07
12
Medium Priority
?
846 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
[X]
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
  • 5
  • 5
  • 2
12 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 1200 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 1200 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 1200 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 800 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 800 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

656 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