Merget Transformation is used to combines the two datasets into one single dataset, Rows from each dataset will be inserted in the output table based on the values in their key columns
Which Scenario we can use Merge transformation. ?
Merge data from two data sources, such as tables and files.
Create complex datasets by nesting Merge transformations.
Remerge rows after correcting errors in the data.
Megre Transformation is equivalent to UNION ALL Transformation,Use the Union All transformation instead of the Merge transformation in the following situations:
The Transformation inputs are not sorted.
The combined output does not need to be sorted.
The transformation has more than two inputs.
Where we can see the Merge Join?
Let go with some examples, how we can use the Merge join. Now we have two data coming from source as Student Details 1 and Student Details 2. We will see how we can merge the two source data. Have look on the below source data.
Step 1: First create the source connect one for the first source data. Have look at the below images.
Step 2: Create the source connection econd for the first source data. Have look at the below images.
Step 3: Before use Merge Transformation, we need to sort the data from the source, have look at the below snapshot. SSIS engine through the error when using merge without sorting the data.
Step 4: By using Sort Transformation, we can sort the source data. When you joining the Sort Transformation with Merge Transformation, the pop will appear to select the Merge Input 1
Now you see, Still an Error message in Merge Transformation. You can double-click and see the error message.
Error Message: The input “Merge Input 2” does not have sort-key positions assigned correctly to its columns.
Now our design is ready to merge the data.
Final Result: Now we can see that our two source data are merged.
NOTE: If you have a scenario you need to join more than two sources, you go with the below approach to join multiple sources in Merge Transformation.
Thank You..!!
Leave a Reply