Create a reusable Lookup transformation in the Transformation Developer. Create a non-reusable Lookup transformation in the Mapping Designer.
To create a Lookup transformation:
To create a reusable Lookup transformation, open the Transformation Developer.
To create a non-reusable Lookup transformation, open a mapping in the Mapping Designer. If you are creating pipeline Lookup transformation, drag in a source definition to use as a lookup source.
Click Transformation > Create. Select the Lookup transformation.
Enter a name for the transformation. Click Create.
The naming convention for Lookup transformations is LKP_
TransformationName
.
Choose whether the transformation is active or passive. Click OK. You cannot change this option.
In the Select Lookup Table dialog box, choose one of the following options to import a lookup definition:
Source definition from the repository.
Target definition from the repository.
Source qualifier from the mapping.
Import a relational table or file from the repository.
You can manually add the lookup ports instead of importing a definition. You can choose which lookup ports are also output ports.
When you choose the lookup source, the Designer creates ports in the transformation based on the ports in the object that you choose. The Designer configures each port as a lookup port and an output port. The lookup ports represent the columns in the lookup source. The Lookup transformation receives data from the lookup source in each lookup port and passes the data to the target.
If you want the Lookup transformation to return all matching rows, enable Return All Rows on Multiple Match. You cannot change this option after you create the transformation. The Lookup transformation becomes an active transformation.
Click OK or click Skip if you want to manually add the lookup ports instead of importing a definition. You can choose which lookup ports are also output ports.
For a connected Lookup transformation, add input and output ports.
You can pass data through the transformation and return data from the lookup table to the target.
For an unconnected Lookup transformation, create a return port for the value you want to return from the lookup.
You can return one column to the transformation that called the lookup.
Click the Properties tab to configure the Lookup transformation properties. Configure lookup caching.
Lookup caching is enabled by default for pipeline and flat file Lookup transformations.
For a Lookup transformation that has a dynamic lookup cache, associate an input port, output port, or sequence ID with each lookup port.
The Integration Service inserts or updates rows in the lookup cache with the data from each associated expression. If you associate a sequence ID, the Integration Service generates a primary key for inserted rows in the lookup cache.
Add the lookup condition on the Condition tab.
The lookup condition compares the source column values with values in the lookup source. The Condition tab Transformation Port represents the source column values. The Lookup Table represents the lookup source.
ASK INFAPreview
