SSIS Merge Transformation

The Merge transformation is used to combine 2 sorted inputs into a single sorted output. The key columns of the output are defined by the same keys of the inputs. Like Union All transformation, The data types of the mapping columns in input datasets must be the same. The following will demonstrate the difference between the Merge transformation and the Union All transformation.

ItemsMergeUnion All
How many input datasets are there in the transformation?2Multiple
Must the input datasets be sorted in the transformation?YesNo
Is the output dataset sorted?YesNo

In the following example, I'll make a copy of the Union All package and change the Union All to Merge transformation. Also we'll have to add Sort transform before each inputs. Let's get started.

  1. Open the project LearnSSIS2 which was created in a previous section in Visual Studio 2012 and make a copy of the package UnionAll.dtsx and rename it to Merge.dtsx.

  2. Open the Merge.dtsx package and click Data Flow to prepare editing the data flow.

  3. Remove the "Union All", the OLE DB Destination and its connection manager. Then drag & drop Sort, Row Count and Merge transforms into the package and link them as shown below.
  4. [Data Flow For Merge]

    The two sort transforms were renamed as "Sort A" and "Sort B".

  5. Right-click ""Sort A" and choose "Edit..." to open its editor and click on "Copy of Book Name" to choose it as sort column.
  6. [Sort A Editor]

  7. Click OK button and open the editor of "Sort B" and choose "Book Name" as the sort column.
  8. [Sort B Editor]

  9. Click OK button and drag the output of "Sort A" to Merge transform. An Input Output Selection dialogue will be popped up.
  10. [Input Output Selection]

  11. Choose "Merge Input 1" and click OK. Then drag the output of "Sort B" to Merge transform.
  12. This time there is no pop-up dialogue any more because the last option "Merge Input 2" was chosen automatically.

  13. Right-click the Merge transform and choose "Edit" to open Merge Transformation Editor and make the changes as below.
  14. [Merge Transformation Editor]

    Choose "Book Name" for the output column "Copy of Book Name" in the "Merge Input 2" column and add a new output column "Store". If you want to delete one line, you just right-click on the line and choose "Delete".

  15. Click OK button. Then create a variable "Count" with data type "Int32" and default value 0.

  16. Double click the "Row Count" transform and select the variable and click OK.
  17. [Row Count Setting]

  18. At last, add a data viewer between Merge and Row Count transform and run the package.
  19. [Result With Data Viewer]

    The data from 2 data sources are merged and sorted. The new column "Store" was added.

  20. Click "Stop Debugging" to terminate the execution.