Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Checkpoints in SSIS

by suresh

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 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 SSIS checkpoints demo, we are going to use the below shown data.

Checkpoints in SSIS 0

Checkpoints in SSIS Example

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.

Checkpoints in SSIS Example 1

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.

Checkpoints in SSIS Example 2

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 to OLE DB Source in the SSIS article.

Checkpoints in SSIS Example 3

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

Checkpoints in SSIS Example 4

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

Checkpoints in SSIS Example 5

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

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

Checkpoints in SSIS Example 7

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
Checkpoints in SSIS Example 8

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 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.

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 SSIS Checkpoint property.

  • CheckpointFileName: Please select the file name. SSIS 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: Package will not use the Checkpoint.
    • IfExists: 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: 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.

Checkpoints in SSIS Example 13

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.

Checkpoints in SSIS Example 17

Now let me run the SSIS checkpoints package.

Checkpoints in SSIS Example 18

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

Checkpoints in SSIS Example 19

Next, let me correct the query in Execute SQL Task

Checkpoints in SSIS Example 20

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.

Checkpoints in SSIS Example 22

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

Checkpoints in SSIS Example 23

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy