SSIS Usage of Checkpoint File

Checkpoint usage with events
I found that saving checkpoints at event execution is bit different too. And it is very important to be aware of it because we often write codes in events. Once the package is restarted using a checkpoint file, depending on the point of failure, it fires some events but some are not. Let’s configure one package for testing. Create a new package and add a Script Task. Set the checkpoint configuration like below:


   Property Value
   CheckpointFileName  D:CheckpointFilesSamplePackageCheckpoint.xml
   CheckpointUsage  IfExists
   SaveCheckpoints  True
 Script Task    
 FailPackageOnFailure  True

Add code like below to the Script Task. It just pops-up a message.

‘ code 1
MsgBox(“Script task executed!”)
Dts.TaskResult = Dts.Results.Success

Configure the events below in the Script Task. Add a Script Task to every event and code for displaying proper message boxes about the event.

Event    Event Script Task message
 OnPreExecute  “OnPreExecute event fired!”
 OnPreValidate  “OnPreValidate event fired!”
 OnPostValidate  “OnPostValidate event fired!”
 OnInformation  “OnInformation event fired!”
 OnPostExecute  “OnPostExecute event fired!”

Note that no checkpoint-related settings are configured in events. Run the package for testing. You will get messages ordered as the above list. The message “Script task executed” will be shown between OnPostValidate and OnInformation events. Change the code of the main Script Task in Control Flow.

‘ code 2
MsgBox(“Script task failed!”)
Dts.TaskResult = Dts.Results.Failure

This causes the package to fail. Run the package and notice the messages. Package displays event messages from OnPreExecute to OnPostValidate, main script task message and then fails. This causes the checkpoint file to remain in the folder. If you correct the error by replacing code 2 with code 1 and execute the package again, you will notice that the task starts but the first three events are not fired. It starts firing events from OnInformation event onwards. Remember this happens even if the script tasks inside the events are set with FailPackageOnFailure = true. Be cautious when you implement checkpoints  with packages that have events implemented.

Other constraints
Checkpoints work with Control Flow tasks only. Tasks related to Data Flow cannot be controlled by checkpoints. In addition to that, the ForEach Loop Container cannot be controlled either. It starts iterating the loop from the beginning without starting from the point of failure. If you need to start the iteration from the failure task inside the ForEach Loop, you need to implement your own codes for it.

You may be holding some secure information inside the package. If the checkpoint is enabled, it may store secure information in the checkpoint file at a failure. Because of this, it is better to set a secure location for the checkpoint file that everyone cannot access.


Leave a comment

Your email address will not be published.