none
SSIS 2005 - Foreach loop container - Stopping the loop after processing X number of file?

    Question

  • I need to stop the Foreach loop container from processing more files in the folder than desired.

    Scenario:  If I only want 1 files processed, i need to stop the loop after it finds 1 file.  No matter what the contraints or tests or variables I set, the loop processes all the files in the folder.  This is bad because I need the value of the 1st mapped variable and not the last one it finds.

    I have tried counting records and setting variables used in the contraints but to no avail  Nothing seems to stop the loop.

    Please advise

    Thursday, September 02, 2010 9:08 PM

All replies

  • If you explain the scenario more we could suggest answers.

    If you just need to have the 1st file Name in a folder(It dosent matter to you which file it picks??) have a flag variable set to boolean type set it to true and another variable to store the 1st file name and another variable to save the filenames per iteration of foreach loop.

    Now inside the foreach loop set the variable mapping.

    Have a script task where you assign the file name to the other variable after checking if the flag is true or false. After file name assignment set the flag to false so that the file name for any more iteration will not be stored in to the variable.

    But I dont think this is what you need or there is a design flaw. In a foreach loop you cannot say for sure that the order of files you see will be the same in which the file you receive in foreach loop. nor can you sort the files based on date modified. If these 2 points are considered it makes no difference whether you read the first file name or the last file name as there is no real order.


    Sudeep's Domain
    Thursday, September 02, 2010 9:39 PM
  • This sample demonstrates this kind of do - while / until type process: http://www.bimonkey.com/2009/07/do-while-until-loops-in-ssis/
    James Beresford @ www.bimonkey.com
    SSIS / MSBI Consultant in Sydney, Australia
    SSIS ETL Execution Control and Management Framework @ SSIS ETL Framework on Codeplex
    • Proposed as answer by BI Monkey Sunday, September 26, 2010 11:32 PM
    Thursday, September 02, 2010 11:11 PM
  • Thanks for the advice so far!  Based on the above input, I need to rethink my strategy.  I was originall concerned about getting one file but I now must consider just grabbing anything in the folder.  It wasn't my original plan...but... plans change.

    Slightly off the subject, but a question about the Foreach loop container (using filespecs) in general.  Based on my experience, this container executes as long as the enumeration returns files.  So far, in all my searching, I can't find a way to stop the loop from executing fully.  In anyone elses experience??? Has anyone been able to stop the loop itself from looping, say halfway through a list of files?  The only way I found to stop it was to error off a task inside the loop which then gets pushed up to the parent container.  But as long as task inside the container execute as "SUccess" then you cant stop it.

    Or maybe my coffee is just bad!

     

    Friday, September 03, 2010 11:51 AM
  • Thanks for the advice so far!  Based on the above input, I need to rethink my strategy.  I was originall concerned about getting one file but I now must consider just grabbing anything in the folder.  It wasn't my original plan...but... plans change.

    Slightly off the subject, but a question about the Foreach loop container (using filespecs) in general.  Based on my experience, this container executes as long as the enumeration returns files.  So far, in all my searching, I can't find a way to stop the loop from executing fully.  In anyone elses experience??? Has anyone been able to stop the loop itself from looping, say halfway through a list of files?  The only way I found to stop it was to error off a task inside the loop which then gets pushed up to the parent container.  But as long as task inside the container execute as "SUccess" then you cant stop it.

    Or maybe my coffee is just bad!

     

    For each loop by itself means that it will be executed for each value in the enumerator right? Thats what it is meant to do.

    In case you dont want to take any action on all the iterations you could make use of expressions in precedence constraint inside ur loop.


    Sudeep's Domain
    Friday, September 03, 2010 12:16 PM