In this SSIS Tutorial I will show you to use SSIS 2012 Merge transformation. For the purpose of this tutorial I will use SSIS 2012.
Other Versions of SQL Server:
- SSIS 2012
- This blog post has been written using SSIS 2012
- SSIS 2008 / R2
- The examples we used should work in SSIS 2008 and R2
- SSIS 2005
- We suspect that this will work in SSIS 2005 as well
What is Merge Transformation?
We can combines two sources together using Merge. This is very similar to SQL UNION ALL and should not be confused with MERGE JOIN Transformation which is a different type of transformation.
Merge Transformation requires input sources to be sorted and we can dot hat using Sort Transformation that we will use in this tutorial. Sort requires additional resources so if sorted input/output is not beneficial for you than consider using UNION ALL Transformation instead.
As we can see below I have two sources and one connection to AdventureWorksDW2012.
In my first source I have 10 rows with First name, LastName and Gender for Male. In second source the same but for Female.
In this tutorial I will show you how to merge the rows together so we get 20 rows, 10 with male 10 with female.
First step is to use merge transformation.
You can merge rows together using Union all, however merge that we will use in this tutorial allows only 2 inputs ( where union all accepts multiple inputs).
Below, I used the sort transformations to sort the input , otherwise the merge won’t work.
NOTE: You can also sort using source component but ensure you understand SQL Collation and Windows Collations that is used for some SSIS Transformations. To find out more check our videos or attend our free SSIS webinars.
In the next step we used the sort transformation to sorted by gender and connect to the merge.
We have to select merge input 1, and next merge input2.
In the merge editor we have got output column name, which is the column name we want to get after the transformations (Changed to gender) and we have got columns from our first input and our second input.
In the next step I use derived column to preview the data.
As we can see our data was merge together and we have twenty rows.
In this tutorial I showed you example of using merge transformation. I hope this tutorial will be helpful for you.
Katie & Emil