This is the second part of a series on using checkpoints in SSIS. In the previous article, I went over the basics of checkpoints, including enabling and configuring them in their simplest form.  In this article, I’ll be covering how containers can be utilized with checkpoints, as well as the limitations of each of the different types of containers.

Note: this article applies to SQL Server 2005.

What are containers?

Microsoft BOL states that containers are objects in SSIS that provide structure to packages and services to tasks. These containers can be used to group tasks and other containers into meaningful units of work, thus creating a hierarchy within the package.

At its lowest level, the package itself is considered to be a container that holds all tasks and containers for the package.  There are different kinds of containers that can be used when building a package.  These include:

• For Loop Container
• Foreach Loop Container
• Sequence Container
• Task Host Container

Event Handlers are also types of containers.  However, I will not be covering these until the next article.

Restarting containers

There is very limited documentation from Microsoft on the use of containers with checkpoints, which makes it fairly difficult to understand how they should and should not work.

When checkpoints are enabled in a package, there are properties that you can set on the tasks and the containers to ensure that the entire container is restarted if any of the tasks within the container fail.

Set the FailParentOnFailure property on each task within the container to True as shown below.

Set the FailPackageOnFailure property on the container to True as shown below.

By setting these properties, you are preventing the tasks from being restart points for the package if it fails.  Instead, the container will be the restart point, and all tasks will run again if anything in the container fails.

Foreach Loop Container

In a Foreach Loop, the control flow is repeated for every member of an enumerator, or collection.  However, neither the information about the state of the enumerator, nor about the number of iterations the loop has gone through, is saved to the checkpoint file.

This means that Foreach Loops will always iterate through all items in the enumerator, even when checkpoints are enabled.

For Loop Container

In a For Loop, the control flow is repeated as long as the evaluation condition is true.  There is quite a bit of confusion regarding how these loops are restarted when checkpoints are enabled.

Since For Loops use variables in the condition and since variable values are saved to the checkpoint file, many people assume that if a For Loop were to fail on the second iteration of the loop, it should restart at the same iteration.  This seems to make sense when the variable is being used as a counter, as shown below.

However, For Loops are actually handled in the same manner as Foreach Loops.  Although the value of the @LoopCounter variable will be saved to the checkpoint file, the For Loop will ignore this value, and instead, evaluates the InitExpression every time.

The reason for this is actually fairly obvious when you look at another example of a For Loop.  Instead of having a counter that is incremented, it’s now evaluating the value of a Boolean variable, as shown below.

The value of this variable will be changed at some point in the loop and will cause the loop to end.  Since information about the how many iterations a loop has gone through is not saved in the checkpoint file, the package would be unable to determine where it left off when it failed.

Sequence Container

A Sequence Container provides a method of logically grouping a set of tasks and containers into its own control flow within the package.  Oddly enough, Sequence Containers do not perform as you might expect when checkpoints are enabled.

For example, I have created a package with a Sequence Container that has three Script Tasks.  I then set the FailParentOnFailure property on each task to True and the FailPackageOnFailure property on the container to True.  I have also set the ForceExecutionResult property Task 2 to Failure so that when the package is started, it will fail on Task 2.

If you restart the package, the entire Sequence Container and the three tasks in it should all be run again.  However, SSIS actually starts on Task 3, completely skipping the first two tasks in the container.

Although you are telling SSIS to only write information about the successful completion of the container to the checkpoint file, invalid data is also being written to the file.

As soon as the first task in the container completes, whether it was successful or not, information about the container and the first task are written to the checkpoint file.  This result is quite unexpected to many people, and is generally considered to be a bug in SQL Server 2005 Integration Services.

Instead of using a Sequence Container, you can get around this problem by using a For Loop that will have only one iteration, as shown below.

Conclusion

This article is intended to show you how containers can be utilized with checkpoints in SSIS.  Here are some of the key points from this article.

• Variables of type Object are never saved to the checkpoint file.
• Although variable values are saved in the checkpoint file, those values are not used in the expressions of a For Loop or Foreach Loop.
• A For Loop and a Foreach Loop will always be re-evaluated, even when checkpoints are enabled.

Sequence Containers do not handle checkpoints properly so For Loops should be considered instead.