The same apply to SSIS.
When the SSIS package you have deployed in production is running smoothly; it feels great. But an SSIS package can fail due to many reasons. The reason can be attributed to various factors but not limited to the list below:
• Source, Target or Staging servers are down
• FTP connectivity or network connectivity has been lost.
• Bad data in the source file/table.
• User permission has been denied at sql server end.
• SMTP Server is not available when sending email via SSIS
… and many more
So what if your package was in the middle of execution and some adverse reasons have failed it? What will be your next step?
…The choice is to debug and find the exact reason for failure and resolve it. After that re-run your package…correct?
You can do that, but SSIS has provided a mechanism to restart your package from the point of failure. Yes that is right you can run your SSIS package from the task (onwards) which have failed. This mechanism is called Checkpoint.
For example if you have five task in your package with the below status i.e. Task A and Task B has been completed successfully and Task C has been failed.
Checkpoints provides the system by which you can re-run your package from Task C onwards. That’s right. With Checkpoint mechanism you do not need to run your Task A and Task B again as you have already run them successfully in the first run.
What exactly is Checkpoints?
Checkpoints are mechanism in SSIS which stores the information in an XML file when the package is executing. This XML File is called Checkpoint file. This file stores following information:
- Task which have been completed successfully.
- Value of Package variables at the time of failure.
Facts about Checkpoints:
- By default Checkpoints are disabled. To enable the Checkpoints you need to set the following properties at package level.
- When a package is successfully executed, Checkpoint files are removed. So for each successful package completion there is no Checkpoint file.
- When a package fails, the Checkpoint files remains in disk. This file information is used when the package is re-executed. The package status is restored and variable values are re-assigned. In other words Checkpoint feature is there to help you when the package fails. If you package do not fails there is no checkpoint file available for you.
- Checkpoint file do not store the value of Object variables.
This is important to know that Checkpoint mechanism track the success or failure of package at Task level i.e. at Control Flow level. SSIS Checkpoint feature do not keep track of success or failure at Component level i.e. at Data Flow Task level. What essentially it means is with Checkpoint you can re-start your package at task level but not at the component level.
Using Checkpoints mechanism is two step tasks.
#1. Enable the Checkpoints at Package Level
#2. Configure Task(s) for Checkpoints at Control flow tab.
#1. ENABLE THE CHECKPOINT AT PACKAGE LEVEL
At the package level you can set the following properties to ENABLE THE CHECKPOINTS.
CheckpointFileName:
This property is use to define the filename and file location that you want to use for Checkpoint file. In case of package failure you can navigate to that file location and open the file to verify any information you might want to. You can use either a hard code file name or use an expression. By default this property is not set; it remains blank.
CheckpointUsage:
This property has three possible values you can choose from:
Never: This is the default value which indicates you are not using Checkpoints.
ifExists: This value tells that SSIS package should use the Checkpoint file if it exists to restart the package from point of failure by restoring the variable values. If there is no file available start the package execution from very first task.
Always: This value tells that SSIS package should always use Checkpoint files while executing. If the checkpoint file do not exist; it will throw an error.
SaveCheckPoints:
This property has two values – True or False. To implement the Checkpoints mechanism in your package you should set the value True.
Ok..now by setting the three above properties you have enabled the Checkpoint mechanism at the Package Level. #1 step to implement the checkpoints are complete.
#2. CONFIGURE TASK(s) FOR CHECKPOINT AT CONTROL FLOW TAB.
The second step is performed for each task that you want to include in your Checkpoints strategy. For each task you have to setup the following properties:
FailPackageonFailure:
This property has two values – True or False. By setting the value to True, we are telling to SSIS that if this task fails, SSIS package must fail. In a nutshell, this task becomes the point of failure. If this task fails the package will fail. Next time when the SSIS package will be re-executed (after resolving the failure reasons), the SSIS package will start the execution from this task onwards.
FailParentonFailure:
This property has two values – True or False. This property is set when the task is child control inside a Sequence container. By setting the value to True, we are telling to SSIS that if this task fails, the parent task must fails. In a nutshell, this task will force the parent to fails and parent must fail the Package. To achieve this we need to setup FailPackageOnFailure property to true at the parent level (ex:- Sequence containers) and at the child level we need to set FailParentonFailure value to true.
After completing the #1 and #2, you are all set to use CheckPoint at Package level. We will have a demo post on this soon.
Thanks for reading this Post.
