SSIS Package Restartability:

Introduction :

Package Restartability is nothing but starting a package from a point where it has failed.

Let’s assume that in company X, a DBA has been assigned to perform certain DB tasks, say three tasks namely A, B and C. These tasks have to run in a sequential order. DBA starts the task in night and leaves the company thinking that work will be completed the next day, if all is well fine. Assume that task B failed for some reason and next day the DBA has to start all the work again right from scratch. In case if task A is very costly in terms of resource and time required, DBA will be in trouble.

SSIS comes handy for these kinds of situations. Using SSIS checkpoints DBA can start running the task B and then task C instead of running task A again.


How to achieve this in SSIS:

We have to write the state information of each tasks in a file after each tasks complete. This is very simple in SSIS. We have to just set four properties. Three in package properties and one in task properties.


Following table will explain what are these properties and how to use it:

Package Properties

CheckPointFileName - File path to store the state information of the tasks e.g.: D:\name.chk
CheckPointUsage - User can select one out of three values available. These values tell us how to use checkpointfile in the package execution

1) Never - Tells the package not to use a checkpointfile

2) If exists - If the checkpointfile exists, it will be used and the package will be restarted according to the information stored.

3) Always - Always use a checkpointfile. If you have not specified any value in checkpointfilename property, package will fail.

SaveCheckPoints - Boolean value to indicate whether checkpoints are to be written.

Task Properties:

FailPackageonFailure - This must be set for each task that we want to be the point for a checkpoint and restart.

Note:

Checkpoints can be used only on control flow and not on dataflow.

Summary:

In this article we learnt about restarting the SSIS package from the point it has failed using checkpoints. We need to just remember the four important properties we have to set for achieving the same. For containers and transactions, we have to adjust or set little more properties. I will try to cover this in next article.