Checkpoints in SSIS

The SQL Server Integration Services includes the most powerful, and useful feature called Checkpoints. You can use this SSIS checkpoints to restart packages from the point of failure, instead of re-running the whole package (all tasks). This is very useful while we are loading large data, or importing or exporting an image.

In this article, we will show you the steps involved in configuring the Checkpoints in SSIS package with an example. For this, we are going to use the below-shown data.

Checkpoints in SSIS Example

In order to explain the Checkpoints in SSIS concept, Drag and drop the data flow task from the toolbox to control flow and rename it to SSIS Checkpoint as we shown below.

Double click on the Data Flow Task will open the data flow tab. Drag and drop OLE DB Source, and OLE DB destination on to the data flow region.

Double click on OLE DB source will open the Source Editor. From the below screenshot you can see that we are selecting the [Employee] table present in the SQL Tutorial database. Please refer OLE DB Source in SSIS article to understand the available options in OLE DB source editor.

Next, Double-click on the OLE DB Destination to open the to OLE DB Destination Editor. For the time being we are selecting the existing table Table called [SSIS Checkpoint Example] table. Please refer OLE DB Destination in SSIS article to understand the available options in OLE DB Destination editor

Click on the Mappings tab to check whether the source columns are exactly mapped to the destination columns.

Click OK to close the OLE DB Destination editor. Next, Drag the Execute SQL Task from SSIS Toolbar and drop it into the Control Flow region

Now, Double click on the Execute SQL Task will open the Editor to configure it. Let me select the Connection Type as OLE DB Connection, which is connecting to SQL Tutorial database. Next, we are using the Direct Input as the SQL statement so, click the … button to write the custom SQL command.

Please write your custom SQL statement here. As you can from the below screenshot, we are writing an UPDATE statement to update the EmpId with 5, and Education to Masters for Employee whose ID is 1.

-- SQL statement for Checkpoints in SSIS example
USE [SQL Tutorial]
GO
UPDATE [dbo].[SSIS Transactions Example]
   SET   [EmpID] = 5 
        ,[Education] = 'Masters'
WHERE [EmpID] = 1

Let me run the package. From the below screenshot you can see that, the Execute SQL Task is failed because we can not update the Identity column (i.e EmpID)

but we successfully inserted the 14 records into the Destination table.

Somehow you realized the mistake you have done and removed the EmpID = 5 from the Update statement. Now you can see, both Data Flow Task, and Execute SQL Task has successfully executed.

Here you are rerunning the whole package, which is OK for this 14 records but it becomes costlier for a large amount of data. In this situation, you can use the SSIS checkpoints to start the package from the point of failure (i.e., Execute SQL Task).

Configuring Checkpoints in SSIS

Please select the package in which you want to configure the checkpoints, and go to its properties windows to find the Checkpoint property.

CheckpointFileName: Please select the file name. SSIS will use this file to save the information related to a checkpoint. By clicking the .. besides this option will open the file system to select, or create a new file.
CheckpointUsage: This property has three options:
- Never: Package will not use the Checkpoint.
- IfExists: Package will look for the checkpoint information, and if there is any information then it will use that information. If not, it will skip using it. This is the common option in most cases.
- Always: Package will always look for the checkpoint information, and use that information. If it doesn’t have the information then it will throw an error.
SaveCheckpoint: Choose whether you want to save the checkpoint or not.

We are creating a new file called SSIS Checkpoints in D folder to save the checkpoint information.

Next, we selected the most common CheckpointUsage property i.e., IfExists.

Next, we changed the Save Checkpoints option from default False to True.

Before we start executing the package, we have to change one more property called FailPackgaeOnFailure of all the tasks in the Control Flow region. First, let me select the Data Flow Task and change the property value from False to True.