Introduction
In this blog, we are going to learn to how to compare records in SSIS using hash method. We will use FREE Template Transform component along with Sort Transform, Merge Join Transform and Conditional Split Transform. We will see how to produce hash value from multiple columns and later compare source\target hash values to detect changes\new rows.
Prerequisites
Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven’t already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
Data Hashing
Here is sample design how to compare records using hash method
- In Step 1, we need two data tables for record comparison from source and destination.Source Data:
Target Data:
- In Step 2, both the source and target data table records would be hash using the ZS Template Transform component as below. You can use FUN_MD5 to produce hash from multiple columns and. Please make sure that you change the Output Column name as below e.g. HashCrm or HashSQL
- In Step 3, both the source and target data table need to be sorted by primary key using SSIS “Sort Transformation Editor” component as shown in above sample design screenshot.
- In Step 4, both the source and target data table need to be merge using SSIS “Merge Join” component providing appropriate join type and key mapping
as below
- In Step 4, we adding the conditional split with appropriate condition based on your need e.g. we want to check similar records as below
- From conditional split, we can feed the modified or same records into destination component based on our needs.
- This is how we can compare the data for source and target tables for updated records and also we can set up different Merge Join and conditional split for desired set of output records set.
Detect Update/Insert
- In previous section, we have chosen the updated records using hash. Here are going to see the Updated/New records using Left Outer Join as belowLeft Outer Join
- Here is conditional split criteria for Update/New records as below
Conclusion
In this tutorial we learned how to compare the records in SSIS using hash method. If you want take a look it then download the ZappySys SSIS PowerPack here.