Aim :- This post under SSIS Tutorial category will enlighten you with a very interesting transformation i.e. Merge transformation in SSIS. In this article we will learn about the Input requirements, working and Configuration of SSIS Merge transformation.
Description :- Listening the term “MERGE”, it sounds like combining something. In SSIS, this something is Data which comes from various sources (can be of same type or heterogeneous). After configuring Data sources, we will Combine/Merge the data from these data sources to generate a final single output.
In other words, Merge transformation combines two data-sets into single data-set. One very important point to remember is that before merging the data from these data-sets, it should always be Sorted. These two sorted data-sets can be anything like Flat file/Excel workbook/Relational table etc.
SSIS Merge Transformation
Input Requirements :-
- It will always accept only 2 inputs and these should be sorted (compulsory). If the requirement is to Merge/Combine more than 2 inputs then Union all transformation can be considered as an option.
- It has 2 Inputs and 1 Output and does not support an Error output.
- It requires merged columns in both the inputs have matching metadata (Data about data such as data type of columns). For example – a column with character data type cannot be merged with a column having numeric data type. Moreover, if columns have the same data type say string, then the length of the column in the first input must be equal to or less than the length of the column in second input to which it is merged.
- One very intelligent thing of Merge transformation is that it will automatically map the columns of the 2 inputs that have same metadata. However, if some columns are left out then you can manually map those columns with the columns having compatible data types.
I hope the above theory is enough to understand about SSIS Merge transformation. Now, we will be implementing one scenario in which 2 input data sources will be merged to one output file. Let’s learn about the functionality of Merge transformation in SSIS with step by step example explained below.
STEP 1 : Create/Identify Data Sources.
- Create 2 Data Sources (Inputs). Here, in this current example, our data is coming from two sources i.e. Flatfile and SQL Server table. Please take a look at the below screen shot
STEP 2 : Connecting Data sources with Sort transformations.
- Open SQL Server Data Tool (SSDT)/BIDS and Create one Integration services project (If you already created any SSIS project just ignore this point).
- Create one SSIS package and give it a desired name, say “Merge Transformation”.
- Drag & Drop Flat file source and configure it to connect to the first input i.e. Textfile (named EMP).
- Drag & Drop OLEDB source and configure it to connect to a SQL Server table (named EMP).
- I hope you all know how to configure the FlatFile and OLEDB data sources (If anyone have doubt please take a look at my previous articles).
- Now, Drag & Drop 2 Sort transformations under these sources and create a connection to them (one Sort transformation should be link to one Data source). We have placed a screenshot below if you face any difficulty while implementing it.
STEP 3 : Configuring both SSIS Sort transformations.
- Double click on first sort transformation which was connected to FlatFile source.
- Once you double click on that you will able to see a screen like below. Here, select column “ID” and give the sort type as “Ascending”.
- Click on “OK” button.
- Repeat the same steps for 2nd Sort transformation also and click on OK.
- Once you configure both the Sort transformations, the error marks (red cross symbol) what we had initially on sort transformations is now gone. Now, the screen should look like below picture.
STEP 4 : Configuring Merge transformation in SSIS.
- Drag and drop Merge transformation from the tool box.
- Now, drag the green arrow from “Sorted Input 1” to the Merge transformation, you will get a new window with the name “Input Output Selection” likes below.
- Select “Merge Input 1” and click on “OK” button.
- Repeat the same above steps with “Sorted input 2”.
- Here, we need to select “Merge Input 2” then click “OK”. Below is the screenshot for same.
- Now, if the error mark on Merge transformation is resolved then that means we have configured it properly else check the above steps carefully.
STEP 5 : SSIS Derived column transformation act as Final destination.
NOTE – As, I do not wish to dump final output into any destination, so I am using Derived column as an alternative just to see the result after package execution. If you want to dump the merged data into any destination such as FlatFile or Excel or SQL Server, etc then use that destination from the toolbox.
- Drag and drop derived column transformation and create a connection with “Merge Transformation”.
- Now, enable the data viewer on it to see the result window. Please look at below screen shot if you have any doubt.
STEP 6 : SSIS package execution (f5).
- Once you are finished with the above steps for the SSIS Merge transformation example, you are all set for executing the SSIS package to view the results.
- Press F5 or click on debugging button to execute the package.
- Yikes !!! Package executed successfully and now we can see the result in the data viewer window.
- In the beginning of this article, we understood what, why and where SSIS Merge transformation is used.
- Then, we have seen about the input requirements for Merge transformation.
- Later, we implemented an easy step by step example to understand Merge transformation practically.
- Finally, I am wrapping up this article and hope you all must have enjoyed it. If you have any queries, doubts or need any technical help, then kindly drop in as comments below. We will highly appreciate your feedback and comments.
Editor – Chander Sharma