In this article I am trying to demonstrate the SSIS CHECKSUM Transform to determine the dynamic changes in sources.
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 : 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
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 ]
IF OBJECT_ID('tbl_DynamicData') IS NOT NULL
DROP TABLE tbl_DynamicData;
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.
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
SET PRODNAME =?,
Property of RowCount
Hope you like it.
Posted by: MR. JOYDEEP DAS