locked
Exiting a Foreach loop container prematurely RRS feed

  • Question

  • 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,

    Dave


    Dave SQL Developer
    Tuesday, November 30, 2010 9:50 PM

Answers

  • 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
    • Marked as answer by DaveDB Thursday, December 2, 2010 3:44 PM
    Wednesday, December 1, 2010 7:15 PM

All replies

  • what do you mean by FILE HAS ERROR ?

     


    http://www.rad.pasfu.com
    Tuesday, November 30, 2010 9:53 PM
  • If the file that I am processing encounters any type of error.

    Dave SQL Developer
    Tuesday, November 30, 2010 9:57 PM
  • 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 response


    Dave SQL Developer
    Tuesday, November 30, 2010 10:02 PM
  • 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
    Tuesday, November 30, 2010 10:07 PM
  • 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
    Tuesday, November 30, 2010 10:09 PM
  • @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
    Tuesday, November 30, 2010 10:20 PM
  • 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
    Tuesday, November 30, 2010 10:32 PM
  • 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
    Tuesday, November 30, 2010 10:44 PM
  • 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
    Tuesday, November 30, 2010 11:25 PM
  • 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.

     

    Wednesday, December 1, 2010 12:24 PM
  • 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
    Wednesday, December 1, 2010 2:49 PM
  • 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
    Wednesday, December 1, 2010 3:31 PM
  • 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?

     

    Thanks


    Dave SQL Developer
    Wednesday, December 1, 2010 4:03 PM
  • FYI: When one file fails within the for-each loop, by default no other files are processed...

    Dave SQL Developer
    • Marked as answer by DaveDB Wednesday, December 1, 2010 5:03 PM
    • Unmarked as answer by DaveDB Thursday, December 2, 2010 3:44 PM
    Wednesday, December 1, 2010 5:03 PM
  • 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
    • Marked as answer by DaveDB Thursday, December 2, 2010 3:44 PM
    Wednesday, December 1, 2010 7:15 PM
  • 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

    Tier 1 Support
    Wednesday, December 1, 2010 7:19 PM
  • 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
    Thursday, December 2, 2010 3:46 PM