SSIS Merge Transformation explained with example

9
2209

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 :-  

  1. 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.
  2. It has 2 Inputs and 1 Output and does not support an Error output.
  3. 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.
  4. 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
2 Data Sources - Flat File and SQL Server Table
2 Data Sources – Flat File and SQL Server Table

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.
2 Data Sources and 2 Sort Transformations
2 Data Sources and 2 Sort Transformations

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”.
Sort Trasformation editor in SSIS
Sort Trasformation editor in SSIS
  • 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.
Configuration of Sort Trasformation in SSIS
Configuration of Sort Trasformation in SSIS

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.
Configuration of Merge Trasformation in SSIS
Configuration of Merge Trasformation in SSIS
  • 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.
Successful Configuration of SSIS Merge Trasformation
Successful Configuration of SSIS Merge Trasformation

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.
SSIS Derived column transformation acting as Final destination
SSIS Derived column transformation acting as Final destination

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.
Merge transformation SSIS package executed Successfully
Merge transformation SSIS package executed Successfully

Summary :-

  1. In the beginning of this article, we understood what, why and where SSIS Merge transformation is used.
  2. Then, we have seen about the input requirements for Merge transformation.
  3. Later, we implemented an easy step by step example to understand Merge transformation practically.
  4. 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

View articles written by Chander Sharma

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.3/10 (12 votes cast)
VN:F [1.9.22_1171]
Rating: +4 (from 6 votes)
SSIS Merge Transformation explained with example, 9.3 out of 10 based on 12 ratings

9 COMMENTS

  1. Sir,i have installed visual studio to use SSIS on sql server 2008.
    I am unable to use but it is mandatory to install visual studio 2008 sp 1.

    • Hi,i often read your article..i really appreciate it, can you send me some important ssis 2008 project or provide me the link,pls help me..i need very urgent…

Comments are closed.