How to use the CheckPoints in SSIS to handle Package Failures and re-running of Packages

Handling Package Failures and re-running of Packages using CheckPoints in SSIS
The Check Points is one of the most powerful, and useful feature in SQL Server Integration Services(SSIS). We can use the Checkpoints in SSIS to restart packages from the point of failure, instead of re-running the whole package (all tasks).
A SQL Server Integration Services (SSIS) package can fail for many reasons like data source might be offline, a server might go down, the network might be unavailable and any other problems might arise.
In this case, in general we can simply restart the package after the problem has been resolved and everything is back to normal. However, if your package is loading large datasets(terabytes of data) or performing numerous other operations, a failure can have a significant impact on your schedule and resources.

The One best way to help & protect against such a issues is to implement checkpoints in your SSIS package. The Checkpoints will save a lot of time & resource effort by restarting the packages from the point of failure, instead of re-running the whole package (all tasks).

Now we will discuss in detail about the how to define the CheckPoints in SSIS, using the following Package
Example::
Suppose we have a package as follows, where are performing the below Tasks..
Truncating the Destination Table :
                                                    Truncate Table [dbo].[Tbl_Sample]
Looping through the input files from a Folder :

Variables used in this Package :Data Flow Task to Load Data from each Input to the Destination Table :

                                                           [dbo].[Tbl_Sample]

Next and Execute SQL Task to perform Region Update on the Destination Table :

Update [dbo].[Tbl_Sample] Set Region ='Middle_East' Where Region = 'East'

Finally an Execute SQL Task to perform NetSales Update on the Destination Table : 

Update [dbo].[Tbl_Sample] Set NetSales =5555.75 Where NetSales <= 3000

Now we will make small change(incorrect SQL Statement) in the Execute SQL Task of the 'Update Region' to make the above Package failure, to explain about the CheckPoints..

Update [dbo].[Tbl_Sample] Set Region =='Middle_East' Where Region == 'East'

Now if we run the above Package it will Fail at the Excecute SQL Task "Update Region". But, before running this Package, we need to configure the Checkpoints as discussed below to capture this Failure stage information so that we can re-run from that Task, without re-running the whole Package again.

Configuring Checkpoints in SSIS ::
Now will discuss first about the Configuration of the CheckPoints for this Package using a XML File, where the Package execution details will be stored.

1) Package Properties > CheckPoints :
Set the Package Properties for the CheckPoints section as follows.

CheckPointFileName :
The XML file which which are using as a CheckPoint to store the Package execution status, the success and failure details of the tasks in the Package flow.
We can create an XML CheckPoint File by Save as the Notepad in XML Format.

Once we created an XML File, then it can be updated to CheckPointFileName.

CheckPointUsage: This property has three options:

Never  : Package will not use the Checkpoint.

IfExists: Package will look for the Checkpoint File for the execution Information, and if there is any Information then it will use that information. If no Checkpoint file exists/no information, then it will skip using it. This is the common option in most cases.
Always: Package will always look for the Checkpoint Information File,uses that Information. If it doesn’t have any Checkpoint File/Information then it will throw an error.

SaveCheckPoint: Choose whether you want to Save the CheckPoint or not.

Now the CheckPoints Configuration is ready. 

2) Task(s) Properties > Execution :
Once the CheckPoints Configuration is ready, the next most important thing is to set the Execution property 'FailPackageOnFailure=True', to each Task(Eg : Execute SQL Task, ForEachLoop Container, DataFlow Task..etc), by selecting each Task individually.
For the Containers and Tasks in side them, we also need to set the property 'FailParentOnFailure =True'.

Note:

If you forget to set this Property to any of the Task, the Package will not use the CheckPoint File to save execution information and also it deletes the existing XML File.

3) Finally Save all the Package, and run it to see how CheckPoint File is useful.
Once we run the Package it will fails, as we know there was some thing wrong with Execute SQL Task.

Since we configure the CheckPoints with a XML File, the Package returns the execution details into XML File as shown below.

We open that XML File with Notepad to see the details. This file contains the Information of the Package, the Tasks that completed and the Failed stage.

We don't Try to understand the XML Code here, as the System will take care the Process when we re-run the Package after fixing the error in the Task where the Package failed.

Now stop the Package and then Fix the Error in the 'Execute SQL Task' then re-run the Package. 

Update [dbo].[Tbl_Sample] Set  Region ='Middle_East' Where Region = 'East'

Since we configured the CheckPoint File, in the Next run the Package will start from the Task, where it was failed before, without re-running the all the Tasks from the beginning.

Note :

Once the Package runs successfully without any error, it will delete the existing Checkpoint File. We need to re-configure again if needed.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------