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
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
This blog very meticulously captures and covers the best essence of formulae and elements especially useful for mathematical calculation and complex problem solving.
ReplyDeleteSSIS Postgresql Read