Today I got some interesting topic regarding the usage of Checkpoints used in SSIS. I will be explaining this topic now.

For Instance, we have a package with 10 tasks in the Control Flow and we have started executing the package, when the package is executed it has suddenly failed at task 5, then we need to do see why this task has failed and will rectify the error in order to run the package successfully. But, when we execute the package now, it will start executing from the very First Task in the package. In order to run the package from the task where it has failed, then we need to use Checkpoints in our package.

  By Default, Checkpoints are not enabled.When checkpoints are configured, the values of package variables as well as a list of tasks that have completed successfully are written to the checkpoint file as XML. Checkpoint file is created when the package starts executing, if the package is executed sucessfully, then the Checkpoint file is deleted. If the package is failed, then the checkpoint file retains until the package execution is succeded.

Below are the steps for configuring a package with Checkpoints. In this demo, we will see inserting of values into a table in SQL Server.

Step 1:

Open BIDS,

Go to Start –> All Programs –> Microsoft SQL Server 2008 (depends on the version you installed) –> SQL Server Business Intelligence Development Studio.

Step 2:

Create a OLEDB Connection to the database. In My Package I have configured a Data Source which points to the destination database. Righ Click on the Configuration Managers –> Select New Connection from Data Source.

Drag 3 Execute SQL Task’s in Control Flow.

1. Execute SQL Task – This task has SQL Statement typed as “INSERT INTO TEST_CHECKPOINT VALUES(‘AAA’)”

2. Execute SQL Task 1 – This task has Syntax wrongly typed in SQL Statement as “INSERT INTO TEST_CHECKPOINT VALUES(bb)”

3. Execute SQL Task 2- This task has SQL Statement typed as “INSERT INTO TEST_CHECKPOINT VALUES(‘ccc’)”

Step 3: 

Go to package properties by right clicking on properties in the package. In the Checkpoints section, specify as below

CheckpointFileName – Specify the path where the checkpoint file need to be created in XML Format.

CheckpointUsage – Specifies whether checkpoints are used.

Never – Specifies that the checkpoint file is not used and that the package runs from the start of the package workflow.

Always – Specifies that the checkpoint file is always used and that the package restarts from the point of the previous execution failure. If the checkpoint file is not found, the package fails.

IfExists – Specifies that the checkpoint file is used if it exists. If the checkpoint file exists, the package restarts from the point of the previous execution failure, otherwise, it runs from the start of the package workflow.

SaveCheckpoints – This Indicates whether the package saves checkpoints. This property must be set to True to restart a package from a point of failure.

Step 4:

Go to the properties of each task in Control flow in the package and specify as below:

Set the FailPackageOnFailure to True.

Now execute the package, the package will fail in the second task

The task in the second step fails as the Syntax used in the SQL Statement is not correct. Now make the modification in the SQL Statement in the  Execute SQL Task 1 to”INSERT INTO TEST_CHECKPOINT VALUES(‘bb’)”. We can see that the checkpoint file is created and the checkpoints are saved in the checkpoint file.

Once again after modifications, re-execute the failed package. Now we can see that the package get successfully executed and also we can see that the checkpoint file is deleted.

Hope this will be useful…..