?
Solved

ssis sequence containers

Posted on 2010-09-07
12
Medium Priority
?
844 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 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