Do? While / Until Loops in SSIS

[James Beresford]

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.



Get More from the original blog...