Merge Transformation in SSIS is used to merge two inputs (such as tables or files) and produce one output. Merge Transformation is very useful when we want to merge the error path data (after handling the errors) and normal data. This SSIS Merge transformation uses key column values to insert the data into destination columns.
For example, We split the data using Conditional Split Transformation in SSIS as per the condition. After performing few more operations on them we want to merge them back.In these situations we can use Merge Transformation to Merge them back.
NOTE: SSIS Merge Transformation will produce only one output and it does not support error output.
Difference Between Merge and Union All Transformation
|Merge Transformation in SSIS||Union All Transformation in SSIS|
|Data should be in sorted order before applying Merge Transformation||No need to sort the data|
|Output of the Merge Transformation will produce Sorted data||Output will be unsorted data.|
|It only accepts 2 inputs||It can accept more than 2 inputs|
Merge Transformation in SSIS Example
We have sales data of a products stored in two different SQL Server tables such as Sales 2, Sales 3 and we want to merge all the related information in one database table.
STEP 1: Drag and drop the data flow task from the toolbox to the control flow region and rename it as Merge Transformation in SSIS
Double click on the data flow task will take us to Data flow region.
STEP 2: Drag and Drop two OLE DB Sources from the toolbox to data flow region and then double-click on first OLE DB source in the data flow region will open the connection manager settings and provides option to select the table holding the source data. Here we are selecting Sales2 table as source
STEP 3: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
Double click on second OLE DB source to configure it with Sales 2 table in SQL Server.
Repeat STEP 3 to verify the columns
STEP 4: Drag and drop two Sort Transformations to sort the SQL Server table’s inputs. Please refer Sort Transformation in SSIS article to understand, How to configure Sort Transformation
STEP 5: Double click on the sort transformation to configure it. Here we are sorting by color and then by English product name in ascending order
TIP: Merge transformation will not work without sorting the input rows. So sort transformation is mandatory before merge
Do the same for Sort Transformation1 also.
STEP 6: Now drag and drop the SSIS Merge Transformation to combine the sorted tables into one table
When you drag the output arrow of the Sort Transformation into Merge Transformation an Input Output Selection window will be appeared to select whether it is Merge Input 1 or Merge Input 2 as shown in the below screenshot
Click ok and drag and drop the output arrow of the Sort Transformation 1. Here Merge Input 2 is automatically selected by the intelligence.
STEP 7: Double click on SSIS Merge Transformation and configure it
Data will be inserted into the destination table based on the Color and English Product Name columns
STEP 8: Drag and drop the OLE DB destination into the data flow region and double-click on it to configure the destination server and destination table to store the merge transformation output
Here we selected [SSIS Tutorials] as database and [Merge Transformation in SSIS] as destination table
STEP 9: Check whether input columns are matching with the destination columns using Mappings Tab
We finished designing the SSIS Merge Transformation package. Let us execute the package
Now we will check the output in SQL Management studio
Thank You for Visiting Our Blog