Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Containers with Checkpoints (Part 2)

By Aaron Akin,

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.

Insert properties

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

FailPackageonFailure property

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.

For Loop Properties

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.

For Loop Properties 2

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.

Package Failing

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.

Package restart and success

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.

For Loop Properties

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.

Total article views: 4441 | Views in the last 30 days: 15
 
Related Articles
ARTICLE

Using Event Handlers with Checkpoints (Part 3)

In Part 3 of his series on checkpoints in SSIS, Aaron Akin talks about how you can use event handler...

FORUM

Foreach and Variables...

Foreach Container with Variables...

ARTICLE

Using Checkpoints in SSIS (Part 1)

SSIS is a great platform for building ETL type applications with SQL Server. One of the great featur...

FORUM

Accessing Dynamic Properties task Properties in Activex Script

DTS Package/ Dynamic Properties task

FORUM

Using checkpoints in a Foreach Loop Container

Foreach Loop Container Reading xml file

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones