How to restart the Package from where it failed?

Friends,

In DTS there was NO option like restarting the package from where it failed. In 2005 and later versions this was added by Microsoft which is a real boon to the guys working in SSIS. If the package is failed and not able to RERUN, then this is damn painful if some of the processes inside the package are expensive in terms of resources or time which needs to be started from scratch. This is the reason why we say this option is a real BOON. For this Microsoft introduced “Package Restartability” or checkpoints in SQL Server 2005 and continuing the  same in later versions too.

Checkpoints are the base for restarting the packages from where it failed. These stored the STATE of each task in the package to a file and later when we restart the package it uses the same information from the file to make sure which task needs to be ruin and which need not based on the status written to the file.

To create the “CheckPointFile” the following properties need to be set.

• Three Package level properties
• One Task level property for each task.

PFB all the properties in detail.

• CheckpointFilename: This is the file name of the checkpoint file, which must be provided. There are no specific conventions or requirements for the file name.
• CheckpointUsage: There are three values, which describe how a checkpoint file is used during package execution:
  • Never: The package will not use a checkpoint file and therefore will never restart.
  • If Exists: If a checkpoint file exists in the place you specified for the CheckpointFilename property, then it will be used, and the package will restart according to the checkpoints written.
  • Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.
• SaveCheckpoints: This is a simple Boolean to indicate whether checkpoints are to be written. Obviously this must be set to true for this scenario.
• FailPackageOnFailure: This is the TASK level property and this has to be set to TRUE in order to create CheckPointFile else you cannot restart the package from where it failed. If you do not set this property to true and the task fails, no file will be written, and the next time you invoke the package, it will start from the beginning again.

One important point to remember here is “CheckPoints” can only be set at Control Flow but not at Data Flow level. The Data Flow Task can be a checkpoint, but it is treated as any other task. You cannot set CheckPoints for the sub tasks in the Data Flow task.

Also remember that If nothing fails in your package then NO FILE will be created. Lets see a simple example here.

In this example I am taking a simple package with THREE EXECUTE SQL Tasks and intentionally writing some code to the third so that it fails.

• Taken a  new package.
• Dropped three EXECUTE SQL tasks into the package control flow.
• Edit the EXECUTE SQL tasks and set the connection property and set SQLStatement as “Select 1” for first TWO and for the third on set “Select 1/0” as shown below.

• Let’s run the package to see the output of the  same. As expected it failed at THIRD task. Now if you rerun the package then it will start from FIRST task which is not the one we a re looking for.

• Now to make the package run from the task where it failed we have to set the properties discussed  above. First let me set Package level properties.

• I also set the task level property i.e FailPackageOnFailure to all tasks to TRUE. Now i executed the package with the same error and it failed at TASK 3 as expected.

• Now the checkpoint file will be saved for all the tasks. I edited the Third task and modified the SQL  Statement to “Select 1” from “Select 1/0” and re triggered the package. You can see the result below.

• From the above pic you can understand that the package restarted from the task failed. SSIS engine first checks for CheckPoint file and based on the STATE of each task i it, it starts the package from the failed task.

Super cool option right. That’s it for now. Try it and implement it. This is very very useful option in SSIS.

Happy Coding !!

Regards,
Roopesh Babu V