When executing SSIS packages, there is always the chance that something could go wrong and cause the package to fail. This can be extremely frustrating if you have a package that affects multiple tables and takes a considerably long amount of time to run. To help ease the pain, SSIS has the option of using checkpoints. Checkpoints give your package the ability to restart from the point of failure. For instance, Say you have a package which contains ten different data flow tasks. The first seven run just fine, but then the package fails on the eighth task. If checkpoints are being used in that package, then it will start at the eighth task the next time the package runs. This can help you with two things. First, it saves time. You won’t have to wait for the package to run from the beginning. Second, if the package has altered data in any tables (ei. Running insert statements), you wouldn’t have to worry about duplicates being loaded when the package restarts.
In order to use checkpoints, you must configure the package to use them. It is important to note that checkpoints can only be used in the control flow. You can set the checkpoints on dataflow tasks, but you cannot set them on tasks inside the data flow. In addition, if there is a container, the checkpoint can be set on the container, but not on the tasks inside of it.
There are three properties that deal specifically with checkpoints; CheckPointFileName, CheckPointUsage, and SaveCheckpoints.
First, set the CheckPointUsage property in the control flow. There are three options for this; Never, Always, and IfExists. This property should be set to IfExists. If it is set to Always, then the package will not run unless there is a checkpoint file.Next, specify the CheckPointFileName property. The checkpoint file is an XML file that stores information about the failed task. . Also included in the checkpoint file are the current values of variables at the time the package failed. When the package fails, the failure point is saved in the checkpoint file. When the package successfully completes, the checkpoint file is deleted Then, set the SaveCheckpoints property to True. This must be set to true in order for the checkpoints to be written to the XML file.
First, set the CheckPointUsage property in the control flow. There are three options for this; Never, Always, and IfExists. This property should be set to IfExists. If it is set to Always, then the package will not run unless there is a checkpoint file.
Next, specify the CheckPointFileName property. The checkpoint file is an XML file that stores information about the failed task. . Also included in the checkpoint file are the current values of variables at the time the package failed. When the package fails, the failure point is saved in the checkpoint file. When the package successfully completes, the checkpoint file is deleted
Then, set the SaveCheckpoints property to True. This must be set to true in order for the checkpoints to be written to the XML file.
After the checkpoint properties are set, there is one more property. For each task in the control flow that needs to be configured for checkpoints, set the FailPackageOnFailure property to True. This will cause the entire package to fail when a task fails and the task information will be written to the checkpoint file.
Once checkpoints have been configured, it is a good idea to test the package in order to make sure that the checkpoints are configured correctly. To do this, there is another property you can set for the tasks inside of the control flow. Click on a particular task, and open the properties pane. In the misc. section of the properties, there is a property of ForceExecutionResult. This property is set to None by default, but when testing checkpoints, this property can be set to Failure.
This will force the task to fail, which will also cause the package to fail.
After the package fails, take note of the checkpoint file that was created on the file system. The next step is to stop debugging, find the task that was set to fail on execution and set that property back to None. Rerun the package and it will start from where it failed.
The package completes successfully and the checkpoint file is deleted. As shown by this article, configuration is simple and relatively quick. This demonstration was conducted on a small package, but one can imagine the headache that can be avoided by using checkpoints on a much larger package with multiple data flow tasks. In closing, the use of checkpoints can reduce the number of times data is altered during package testing, and save a lot of time when rerunning packages.
The package completes successfully and the checkpoint file is deleted.
As shown by this article, configuration is simple and relatively quick. This demonstration was conducted on a small package, but one can imagine the headache that can be avoided by using checkpoints on a much larger package with multiple data flow tasks. In closing, the use of checkpoints can reduce the number of times data is altered during package testing, and save a lot of time when rerunning packages.