Solved

ssis sequence containers

Posted on 2010-09-07
12
829 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now