Checkpoints in SSIS

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

This article shows the steps involved in configuring the Checkpoints in SSIS package with an example. For this checkpoints demo, we are going to use the below shown data.

Logging Source

Checkpoints in SSIS Example

To explain the concept, Drag and drop the data flow task from the toolbox to control flow and rename it to SSIS Checkpoint.

Data Flow Task

Double click on the SSIS 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.

OLE DB Source and Destination

Double click on the 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 database. Please refer to the OLE DB Source article.

OLE DB Source Editor

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

OLE DB Destination Table

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

Checkpoints Column Mappings

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

Checkpoints in SSIS Example 6

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 the database. Next, we are using the Direct Input as the SQL statement, so click the … button.

Checkpoints SQL Statement

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

UPDATE [dbo].[SSIS Transactions Example]
   SET   [EmpID] = 5 
        ,[Education] = 'Masters'
WHERE [EmpID] = 1
Example SQL Query

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

Checkpoints in SSIS Example 9

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

Checkpoints in SSIS Example 10

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

Checkpoints in SSIS Example 11

Here you are rerunning the whole package, which is OK for these 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 set the checkpoints and go to its properties windows to find the Checkpoint property.

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

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

Save the File

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

Checkpoints in SSIS Example 14

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

Checkpoints in SSIS Example 15

Before we start executing the SSIS checkpoints 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.

Checkpoints in SSIS Example 16

Next, select the Execute SQL Task and change the property value from False to True.

Fail Package on Failure

Now let me run the SSIS checkpoints package.

Checkpoints in SSIS Example 18

As you see, it has created a file with checkpoint information

created File

Next, let me correct the query in the task.

Update Statement

Let me run the SSIS checkpoints package. As you can see from the below screenshot, executed started directly at the Execute SQL Task (Previously stopped position)

Checkpoints in SSIS Example 21

Let me show you the Data Flow region as well.

Data Flow Region OLED BD Source and Destination

Lastly, Please open the Management Studio to check the result.

Destination Table

Comments are closed.