Checkpoints

      Using checkpoints we can restart the package from the failure point. For creating checkpoints first we need to set three property values. Those are

• CheckpointFileName
• CheckpointUsage
• Never
• IfExists
• Always
• SaveCheckpoints
• True
• False

     In 'Control Flow', right click and select properties (don't select any task or container). In properties window you can find the above options.

CheckpointFileName:

We need to specify the path, where we can create checkpoint file (.xml file).

CheckpointUsage:

This is having three options; if we select 'Never' then the checkpoint will never create. If we select 'IfExists' then if the package is failed then only it will create, other wise it won’t create. If we select 'Always', then check point will create always.

SaveCheckpoints:

If we select 'True' then only checkpoint will create.

Note: After setting above properties, select the required task (on which task we need to implement checkpoints) right click and select the properties. set the 'FailePackageOnFailure' option as 'True".

Example:
      I am having an SSIS package. That package extracts data from file and loading it into a table. Before loading the data, table will get truncate. After loading the data, moving the source file from source location to archive folder.

     You can see the the ETL package in the above screen. In that package i am going to implement checkpoints.

• In 'Control Flow' right click and select properties (Don't select any task).
• Properties window will open, Set the 'CheckpointFileName'
• Select the IfExists option from the 'CheckpointUsage' drop-down list.
• Select 'True' option from the SaveCheckpoints drop-down list.

• Select the 'Data Flow Task', right-click on that and select properties.
• From the properties set the 'FailPackageOnFailure' as 'True'.

• I have removed the source file from source location before running the ETL, so ETL will fail during file load.

 

• We can see in the above screen that the ETL is failed during file load. We can see the check point file in the given path. If we delete the checkpoint, and restart the ETL. Then the ETL will start from the beginning. Don't delete check point if we need to restart the ETL from the failure point.
• Stop the ETL and place the source file in the source directory and restart the ETL.

• In the above screenshot we can observe that the ETL is restarted from the failure point.