Edit

Share via

Merge Transformation

  • Article

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

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 programmatically, click one of the following topics:

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

Merge Transformation Editor

Use the Merge Transformation Editor to specify columns from two sorted sets of data to be merged.

Important

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.

Options

Output Column Name
Specify the name of the output column.

Merge Input 1
Select the column to merge as Merge Input 1.

Merge Input 2
Select the column to merge as Merge Input 2.

See Also

Merge Join Transformation
Union All Transformation
Data Flow
Integration Services Transformations