Share via

  • Article

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.

Input Requirements

The Merge Transformation requires sorted data for its inputs. For more information about this important requirement, see How to: Sort Data for the Merge and Merge Join Transformations.

The Merge transformation also requires that the merged columns in its inputs 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.

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:

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.