• Article

Merge Join Transformation

The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin. For more information, see Using Joins.

You can configure the Merge Join transformation in the following ways:

  • Specify the join is a FULL, LEFT, or INNER join.

  • Specify the columns the join uses.

  • Specify whether the transformation handles null values as equal to other nulls.

    Note

    If null values are not treated as equal values, the transformation handles null values like the SQL Server Database Engine does.

This transformation has two inputs and one output. It does not support an error output.

Input Requirements

The Merge Join 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.

Join Requirements

The Merge Join transformation requires that the joined columns have matching metadata. For example, you cannot join 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.

Buffer Throttling

For information about how to use the MaxBuffersPerInput property to tune the memory usage of the Merge Join transformation, see Improving the Performance of the Data Flow.

Configuring the Merge Join Transformation

You can set properties through the SSIS Designer or programmatically.

For more information about the properties that you can set in the Merge Join Transformation Editor dialog box, see Merge Join Transformation Editor.

For more information about properties that you can set 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.