SSIS – Incremental Process by CHECKSUM() Function

Introduction

In the journey of my SSIS here we are going to demonstrate one of the processes of Incremental data Load by using CHECKSUM() Function. Hope it will be interesting.

What the Scenario is

Scenario is simple load data from a staging table to Destination table. The Staging table is populated from Flat File source. Here in this article we are not interested to Load the Staging Table but interested to understand how we use the CHECKSUM() function in SQL Server for Incremental data load.

How we do That

 Step – 1 [ The Data Flow of the Package ]

Step – 2 [ The Staging and Destination Table ]

CREATE TABLE [dbo].[tbl_EmployeeStage]

  (

      EmpId   INT            NOT NULL PRIMARY KEY,

      EmpName  VARCHAR(50)    NOT NULL,

      EmpGrade CHAR(1)

  )

GO

CREATE TABLE [dbo].[tbl_Employee]

  (

      EmpId   INT            NOT NULL PRIMARY KEY,

      EmpName  VARCHAR(50)    NOT NULL,

      EmpGrade CHAR(1)

  )

GO

INSERT INTO [dbo].[tbl_EmployeeStage]

    (EmpId, EmpName, EmpGrade)

VALUES(1, 'Joydeep Das', 'A'),

      (2, 'Rajesh Mondal', 'C'),

      (3, 'Santi Nath', 'B');

Step – 2  [ OLEDB – Source for Retrieving data from Source and Destination Table ]

The SQL Command Text for Staging Table

SELECT EmpId, EmpName, EmpGrade, CHECKSUM(*) AS [CheckSum]

FROM   [dbo].[tbl_EmployeeStage]

ORDER BY 1;

The SQL Command Text for Destination Table

SELECT EmpId, EmpName, EmpGrade, CHECKSUM(*) AS [CheckSum]

FROM   [dbo].[tbl_Employee]

ORDER BY 1;

Step – 3  [ The Sort Transform and The Merge Join Transform ]

 Not going to Describe.

Step – 4 [ The Conditional Split ]

Step – 5 [ The Conditional Split named Record Change ]

Please look at the Condition. Here we use the Columns that is made by CHECKSUM() Function. We are not going no check like
[Source Col-1] <> [Destination Col-1] OR [Source Col-2] <> [Destination Col-2] Approach.

Think if you have 150 columns in your table…. What the Situation you face over here.

Step – 6 [ First Time Execution of Package ]

Step – 7 [ Execute Package When Some Records UPDATED in Staging Table ]

UPDATE [dbo].[tbl_EmployeeStage]
       SET    EmpName = 'Suman Das'
WHERE  EmpId = 1

Step – 8  [ Execute Package When Records UPDATED and INSERT in Staging Table ]

UPDATE [dbo].[tbl_Employee] SET EmpName = 'Sree Devi' WHERE EmpId = 2;

GO

INSERT INTO [dbo].[tbl_EmployeeStage]

    (EmpId, EmpName, EmpGrade)

VALUES(4, 'Sunny', 'C');

GO

Hope you like it.

Posted by: MR. JOYDEEP DAS