SSIS - CHECKSUM and Dynamic Changes

Introduction

In this article I am trying to demonstrate the SSIS CHECKSUM Transform to determine the dynamic changes in sources.

Case Study

Please understand the case study very well before drilldown to package level.

There is a text file named "DuynamicData.txt". We are just going to extract this flat file and load it in our SQL Server table objects called "tbl_DynamicData".

The source file structure is mentioned bellow

PRD1;TOOTH BRUSH;100;25

PPD2;SAVING LOTION;214;250

PRD3;SAVING CREAM;240;130

PRD1                   :  is the product number and we are taking is as primary key.

TOOTH BRUSH: is the product name

100                       : is the stock quantity

25                         : Price of the product

So we have to load it into our MS SQL Table named "tbl_DynamicData". But the text file changed frequently, for example

PRD1;TOOTH BRUSH;100;25

PPD2;SAVING LOTION;200;250

PRD3;SAVING CREAM;290;130

Here the Product quantity changed from 214 to 200, 240 to 290 and when we run the package it automatically determine the changes and changed it to our destination table accordingly. It juts copy the entire data at first time and each and every time it just look at the changes of source and act accordingly to change the destination table.

Step-1 [ Create the Destination Table ]

/*

   PRD1;TOOTH BRUSH;100;25

   PPD2;SAVING LOTION;214;250

   PRD3;SAVING CREAM;240;130

   Destination Table

*/

IF OBJECT_ID('tbl_DynamicData') IS NOT NULL

   BEGIN

     DROP TABLE tbl_DynamicData;

   END

GO

CREATE TABLE tbl_DynamicData

       (PRONUM          VARCHAR(50)   NOT NULL PRIMARY KEY,

        PRODNAME    VARCHAR(50)   NOT NULL,

        STKQTY      DECIMAL(10,0) NOT NULL,

        PRICE       DECIMAL(10,2) NOT NULL,

        Hash        INT           NULL);      

Step-2 [ Enable CHECKSUM ]

Go to the given link and down load the checksum transform and install it.
http://www.sqlis.com/sqlis/post/Checksum-Transformation.aspx

Right click on tool bars à Select the Choose Item from the shortcut menu. The choose toolbox item appears. In the SSIS Data flow Items select the Checksum Check box and click ok. The Checksum appears in the toolbars dataflow transform tabs.

Step-3 [ Prepare the SSIS Package ]

Property of CheckSum Transform

Property of Lookup Transform

Property of Conditional Split Transform

Property of OLE DB Command

The SQL Command

UPDATE tbl_DynamicData

    SET PRODNAME =?,   

            STKQTY=?,     

            PRICE=?,      

            Hash=?

WHERE PRONUM=?                  

 Property of RowCount

Hope you like it.