To use a Joiner transformation, add a Joiner transformation to the mapping, set up the input sources, and configure the transformation with a condition and join type and sort type.
To create a Joiner transformation:
In the Mapping Designer, click Transformation > Create. Select the Joiner transformation. Enter a name, and click OK.
The naming convention for Joiner transformations is JNR_
. Enter a description for the transformation.
The Designer creates the Joiner transformation.
Drag all the input/output ports from the first source into the Joiner transformation.
The Designer creates input/output ports for the source fields in the Joiner transformation as detail fields by default. You can edit this property later.
Select and drag all the input/output ports from the second source into the Joiner transformation.
The Designer configures the second set of source fields and master fields by default.
Double-click the title bar of the Joiner transformation to open the transformation.
Click the Ports tab.
Click any box in the M column to switch the master/detail relationship for the sources.
To improve performance for an unsorted Joiner transformation, use the source with fewer rows as the master source. To improve performance for a sorted Joiner transformation, use the source with fewer duplicate key values as the master.
Add default values for specific ports.
Some ports are likely to contain null values, since the fields in one of the sources may be empty. You can specify a default value if the target database does not handle NULLs.
Click the Condition tab and set the join condition.
Click the Add button to add a condition. You can add multiple conditions.
The master and detail ports must have matching datatypes. The Joiner transformation only supports equivalent (=) joins.
Click the Properties tab and configure properties for the transformation.
You can edit the join condition from the Condition tab. The keyword AND separates multiple conditions.
Click the Metadata Extensions tab to configure metadata extensions.