Merge Transformation
The Merge transformation combines two sorted datasets into a single dataset. The rows from each dataset are inserted into the output based on values in their key columns.
By including the Merge transformation in a data flow, you can perform the following tasks:
- Merge data from two data sources, such as tables and files.
- Create complex datasets by nesting Merge transformations.
- Remerge rows after correcting errors in the data.
The Merge transformation is similar to the Union All transformations. Use the Union All transformation instead of the Merge transformation in the following situations:
- The transformation inputs are not sorted.
- The combined output does not need to be sorted.
- The transformation has more than two inputs.
The Merge transformation requires that both its inputs be sorted and the merged columns have matching metadata. For example, you cannot merge a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.
The input data can be sorted either by setting sort options on the outputs of the source or the upstream data flow components, or by inserting a Sort transformation into the data flow before the Merge transformation. If the sort options indicate the data is sorted, but the data is not actually sorted, then the result of the merge operation is unpredictable.
In the SSIS Designer, the user interface for the Merge transformation automatically maps columns that have the same metadata. You can then manually map other columns that have compatible data types.
This transformation has two inputs and one output. It does not support an error output.
Configuring the Merge Transformation
You can set properties through the SSIS Designer or programmatically.
For more information about the properties that you can set in the Merge Transformation Editor dialog box, see Merge Transformation Editor.
For more information about the properties that you can programmatically, click one of the following topics:
For more information about how to set properties, click one of the following topics:
- How to: Set the Properties of a Data Flow Component Using a Component Editor
- How to: Set the Properties of a Data Flow Component in the Properties Window
- How to: Set Sort Attributes on an Output
See Also
Concepts
Merge Join Transformation
Union All Transformation
Creating Package Data Flow
Integration Services Transformations