Tuesday, 14 May 2013

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.




Posted by: MR. JOYDEEP DAS

1 comment:

  1. Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my visitors will find that very useful.
    http://www.sqlservermasters.com/

    ReplyDelete