Do… While / Until Loops in SSIS

There isn’t an explicit Do.. While / Until loop in SSIS, but it is easy to emulate the functionality using a For Loop Container. It’s a simple 2 step process:

  1. Create a variable to hold your Until / While break value
  2. Set only the EvalExpression of the For Loop container to break when your condition is met in the variable

I’ve attached an example which uses the variable User::WhileCondition. The For Loop container stops executing when this EvalExpression is no longer true:

@[User::WhileCondition] < 5

The script task within the loop increases User::WhileCondition by 1 on each iteration. When it reaches 5, the loop stops executing.

The scenario I was using this was for monitoring a folder where files might keep arriving even during processing. So after the Foreach loop over the folder ran, the script task would check to see if any files remained in the folder. If there were, the loop would run again and process the new files. Once there were no files left, the Do.. Until loop evaluated that there were zero files and stopped running.

The sample package can be found here for 2005 and guidelines on use are here.

8 thoughts on “Do… While / Until Loops in SSIS

  1. BI Monkey,

    I went looking for some “For Each Loop” tips but have come up short and was wondering if you have successully achieved the following. I have a “For Each Loop” container that I want to reference a variable containing the folder starting location. At this point in time I have it hard coded as C:\Source, but as this is going to potentially be moved onto a more secure share drive space I actually want the location varaible to be an easily updatable configuration variable.

    Have you successfully achieved this?

  2. This is fairly painless – you want to set the value for the “Directory” expression on the ForEach container to a variable containing your file name.

    You can then set this variable from either a Package Configuration or as a parameter when you run the job either via dtexec or SQL Agent.

  3. Is it possible for me to save the value of the variable that i use in foreach loop container for storing the file name in some flat file. I’m trying to save it but it is giving me nothin.

  4. I would suggest creating another variable – to append the result to in each iteration of the loop – then use that to write out the final file with all the values in.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>