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 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.

Configuration of 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:

• Common Properties

• Transformation Custom Properties

Related Tasks

For details about how to set properties, see the following topics:

• Set the Properties of a Data Flow Component

• Sort Data for the Merge and Merge Join Transformations

Related Content

Curated Answer, Create an XML Destination for an SSIS Package.

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: Visit the Integration Services page on MSDN For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Concepts

Merge Join Transformation

Union All Transformation

Data Flow

Integration Services Transformations