Hello, I have a SSIS package that is looping through a series of csv files. I need to find a way to exit the loop if any of the files error out. For example: If I have 4 files, and file #2 has an error, I would like to exit out of the loop entirely and not process the last 2 files. I know in transact SQL you can use a simple RETURN;, but I am not sure how to simualte this is SSIS. Any help would be greatly appreciated. Thanks, DaveDave SQL Developer

what do you mean by FILE HAS ERROR ? http://www.rad.pasfu.com

If the file that I am processing encounters any type of error.Dave SQL Developer

If the file that I am processing encounters any type of error.Dave SQL Developer

Basically I am dumping csv files into a temp table using bulk insert, and then moving them to production. I am using the for each loop container to loop through all of the files. If any of the files cause any type of error, I want to exit the loop, log the file name, and not insert anything into production. I just need to figure out how to exit the loop container. Thanks for your quick responseDave SQL Developer

Basically I am dumping csv files into a temp table using bulk insert, and then moving them to production. I am using the for each loop container to loop through all of the files. If any of the files cause any type of error, I want to exit the loop, log the file name, and not insert anything into production. I just need to figure out how to exit the loop container. Thanks for your quick responseDave SQL Developer

you can put OnERROR event handler on the task inside foreach loop and in the event handler put a script task and set a variable value you should put another script task which is empty inside the foreach loop before other tasks, and check the value of that variable to be valid for continue with expression, and then connect precedence constraint to other tasks. This will cause foreach to jump files after first error occurance. http://www.rad.pasfu.com

you can put OnERROR event handler on the task inside foreach loop and in the event handler put a script task and set a variable value you should put another script task which is empty inside the foreach loop before other tasks, and check the value of that variable to be valid for continue with expression, and then connect precedence constraint to other tasks. This will cause foreach to jump files after first error occurance. http://www.rad.pasfu.com

You need to implement rollback by setting the transactionoption property of the foreachloop to required. The for each loop should break by itself on any error. http://msdn.microsoft.com/en-us/library/ms141724.aspx Read the part about ensuring data integrity by using transactions.Please 'Mark as Answer' if found helpful - Chris@tier-1-support

You need to implement rollback by setting the transactionoption property of the foreachloop to required. The for each loop should break by itself on any error. http://msdn.microsoft.com/en-us/library/ms141724.aspx Read the part about ensuring data integrity by using transactions.Please 'Mark as Answer' if found helpful - Chris@tier-1-support

@Tier1: if you set transaction property on foreach loop , A NEW TRANSACTION WILL OPEN ON EACH ITERATION, and this means that if one iteration cause error, next iteration will be continue, and this is not what Dave requested here.http://www.rad.pasfu.com

@Tier1: if you set transaction property on foreach loop , A NEW TRANSACTION WILL OPEN ON EACH ITERATION, and this means that if one iteration cause error, next iteration will be continue, and this is not what Dave requested here.http://www.rad.pasfu.com

I thought everything that happens in the for each loop is considered one transaction... Is there documentation that says it will commit a new transaction on each enumeration? Thanks in advance.Please 'Mark as Answer' if found helpful - Chris@tier-1-support

I thought everything that happens in the for each loop is considered one transaction... Is there documentation that says it will commit a new transaction on each enumeration? Thanks in advance.Please 'Mark as Answer' if found helpful - Chris@tier-1-support

I thought everything that happens in the for each loop is considered one transaction... Is there documentation that says it will commit a new transaction on each enumeration? Thanks in advance. Please 'Mark as Answer' if found helpful - Chris@tier-1-support in the 70-448 book , page 61, first paragraph: If you set the TransactionOption property of a Foreach Loop Container or For Loop Container to Required, a new transaction will be created for each loop of the container http://www.rad.pasfu.com

I thought everything that happens in the for each loop is considered one transaction... Is there documentation that says it will commit a new transaction on each enumeration? Thanks in advance. Please 'Mark as Answer' if found helpful - Chris@tier-1-support in the 70-448 book , page 61, first paragraph: If you set the TransactionOption property of a Foreach Loop Container or For Loop Container to Required, a new transaction will be created for each loop of the container http://www.rad.pasfu.com

I thought everything that happens in the for each loop is considered one transaction... Is there documentation that says it will commit a new transaction on each enumeration? Thanks in advance. Please 'Mark as Answer' if found helpful - Chris@tier-1-support in the 70-448 book , page 61, first paragraph: If you set the TransactionOption property of a Foreach Loop Container or For Loop Container to Required, a new transaction will be created for each loop of the container http://www.rad.pasfu.com Thanks Reza! :)Please 'Mark as Answer' if found helpful - Chris@tier-1-support

Hi Reza, What happens when we keep some dummy-task just before ForEach Loop. TransactionOption for dummy-task is set to Required. TransactionOption for ForEach Loop is set to Supported. my doubt is, let us say dummy-task starts transaction T1. and the ForEach Loop will not start new transaction but joins T1. what i read in books is this....is there any thing else... I am so much confused about transactions.

Thank you all for your feedback... We still haven't found a solution to this problem. Does anyone else have any ideas? My deadline is looming :( Thanks Dave SQL Developer

I am surprised that no one knows how to solve this problem. I would assume that pre-maturely exiting a loop, and rolling back any other transactions would not be difficult, but I guess I was wrong...Dave SQL Developer

I think the best way to handle this would be to create a table and set a flag for each file. Then, before inserting into production, query the table and if any files failed, manually rollback the transaction... What do you guys think? ThanksDave SQL Developer

FYI: When one file fails within the for-each loop, by default no other files are processed...Dave SQL Developer

FYI: When one file fails within the for-each loop, by default no other files are processed...Dave SQL Developer

I had another approach, which you put whole foreach loop in a sequence container and then set TransactionOption of that container as Required. this worth to try.http://www.rad.pasfu.com

That was my initial thought... But I was not in an enviroment to be able to test. Good to know Dave and thanks to Reza for the book info. Very helpful.Please 'Mark as Answer' if found helpful - Chris@tier-1-support

That's a really good idea, Reza. To be honest, I am considering this option just as a precaution. I ran several tests with my loop and each time a file failed nothing else was processed. I do, however, have a sinking feeling that this method is not 100%. I am going to continue testing for now. Thanks to everyone for your help :)Dave SQL Developer