Merge Join transformation is common transformation and It is very similar to Merge Transformation but It performs Inner, Left, Full Join,.This transformation has only Two Inputs and One Output.
Both Datasets should be sorted because If Datasets are not sorted, we can not use merge join transformation to combine datasets.
For this transformation,We are explaining the example How to combine data of two sources by using Merge Join Transformation.
In previous articles, We have explained to open BIDS and create a package. Now we are going to explain how to use Merge Join Transformation.
Step 1) Drag and drop Two OLE DB Sources, Two Sort Transformations,Merge Join Transformation and OLE DB Destination from SSIS toolbox into data flow tab.
Step 2) Now Double click on OLE-DB source, OLE-DB source editor window will open and need to select table.
Step 3) Click on the columns tab to check the columns and click on “ok” button.
Step 4) Now Double click on OLE-DB source1, OLE-DB source editor window will open and need to select table.
Step 5) Click on the columns tab to check the columns and click on “ok” button.
Step 6) Double click on the Sort Transformation, Sort Transformation editor window will open to specify the columns to sort and set their sort order.
In screenshot, We have selected column(“DepartmentId”) and set their Sort type as “Ascending” order. Now Data will be sorted by “DepartmentId” in ascending order.
Remove rows with duplicate sort values: If you select this option, This Transformation will remove duplicate records otherwise It will copy all the columns, including duplicate rows.
Step 7) Double click on the Sort Transformation1, Sort Transformation editor window will open to specify the columns to sort and set their sort order.
In screenshot, We have selected column(“Id”) and set their Sort type as “Ascending” order. Now Data will be sorted by “Id” in ascending order.
Step 8) Once you drag the output arrow of the Sort Transformation to Merge Join Transformation, Input Output Selection window will open.There will be two inputs (Merge Join Left Input,Merge Join Right Input).
Step 9) Double click on the Merge Join Transformation, Merge Join Transformation editor window will open to select join types and then specify the columns to be used as the Join key.
Lets suppose You have selected INNER JOIN and put JOIN ID as “DepartmentID” to get common records from both tables. If “DepartmentID” of Employee Table and “Id” of Department table is matched, matched records will be displayed .
Step 10) Double Click on OLE DB destination , OLE DB destination editor window will open to configuration table in which we need to combine data of OLE DB source based on joins.
Step 11) Click on the Mappings tab to check the columns.
Step 12) Click on “Ok” button to complete the configuration and execute the package to check the result in database.
Step 13) Open SQL Server Management Studio to check the output.
If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.
Clients will find it using our advanced search filter and will contact you directly.
No any charge for the product lsiting.