The source table in this mapping includes information about the manufacturer ID. However, you want the manufacturer name in the target table to make the summary data easier to read. In the following steps, you use a Lookup transformation to find each manufacturer name in the MANUFACTURERS table based on the manufacturer ID in the source table.
Create a Lookup transformation and name it LKP_Manufacturers.
The naming convention for Lookup transformations is LKP_
A dialog box prompts you to identify the source or target database to provide data for the lookup. When you run a session, the Integration Service must access the lookup table.
Select the MANUFACTURERS table from the list and click
to close the
The Designer adds the transformation.
Use source and target definitions in the repository to identify a lookup source for the Lookup transformation. Alternatively, you can import a lookup source.
Open the Lookup transformation.
Add an input port, IN_MANUFACTURER_ID, with the same datatype as MANUFACTURER_ID.
In a later step, you connect the MANUFACTURER_ID port from the Aggregator transformation to this input port. IN_MANUFACTURER_ID receives MANUFACTURER_ID values from the Aggregator transformation. When the Lookup transformation receives a new value through this input port, it looks up the matching value from MANUFACTURERS.
By default, the Lookup transformation queries and stores the contents of the lookup table before the rest of the transformation runs, so it performs the join through a local copy of the table that it has cached.
tab, and click the
An entry for the first condition in the lookup appears. Each row represents one condition in the WHERE clause that the Integration Service generates when querying records.
Verify the settings for the condition.
The following table describes the settings for the condition:
Lookup Table Column
If the datatypes, including precision and scale, of these two columns do not match, the Designer displays a message and marks the mapping invalid.
Do not change settings in this section of the dialog box.
You now have a Lookup transformation that reads values from the MANUFACTURERS table and performs lookups using values passed through the IN_MANUFACTURER_ID input port. The final step is to connect this Lookup transformation to the rest of the mapping.
Connect the MANUFACTURER_ID output port from the Aggregator transformation to the IN_MANUFACTURER_ID input port in the Lookup transformation.
Connect the MANUFACTURER_ID port in the LKP_MANUFACTURERS Lookup transformation object to the MANUFACTURER_ID port in the T_ITEM_SUMMARY target defintion object.