SSIS Loops that do not fail

By default, in a Loop container, if the contents of that loop fail, then the whole container is failed at the same time. Every now and then I need to create loops that do not fail when the contents fail – for example because failure is permitted or handled within the container.

The way to allow a loop to continue even if its contents have failed is to set the ForceExecutionResult property to Success, and every iteration of the loop will be treated as successful, regardless of what happens during the loop.

I’ve knocked up a very quick sample package demonstrating this, which can be downloaded here.

Read More

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.

Read More