Merge Transformation in SSIS
SSIS merge is one of the component of SSIS, available in toolbox.
Merge Transformation in SSIS is used to merge two inputs and produce one output.
The Merge Transformation is similar to the union all transformations.
SSIS is all about collecting data from different sources, If you want to merge the collected data from different sources then we can use merge component.
MERGE takes ONLY TWO inputs and ONLY ONE output and does not have any Error output.
SSIS Merge Transformation with example:
Firstly, I have created two files with Name File1 and File2 and inserted few records into each file.
Drag and drop Flat File Source components and gave the name File1. Right Click on File1 and configure it.
There after, configure the properties used to connect to and obtain data from a text file.
After that, gave the name of connection manager and browse the file. check mark the column names in the first data row.
Check out the columns, advance and preview option then click ok.
Go to the Columns option and check the external column and output column then click ok.
Select an input and an output to connect the components then click ok.
Similarly, Drag and drop Flat File Source components and gave the name File2 and configure it.
Subsequently, I took merge component and then configure it.
Then, two sorted outputs let’s input them to Merge Transformation component. Now just right click on it and configure it as shown in below image.
after that, I took Flat file destination and configure it.
there after, go to the connection manager and click on New option.
Then, Choose the format of the destination flat file and click ok.
Browse the file name and click ok.
Go to the Mappings and click ok.
Finally, Execute the Package successfully.
Both of the data sources are merged into single file output.
Check the output following.
If you are new to MSBI start with the following must-watch video: -
