Creating an Unconnected Lookup Transformation
Create an unconnected Lookup transformation when you want to perform a lookup from an expression. You can create a reusable or a non-reusable unconnected Lookup transformation on a flat file, reference table, or relational data object.
1. Select a project or folder in the Object Explorer view.
2. Click File > New > Transformation.
3. Browse to the Lookup wizard.
4. Select Flat File Data Object Lookup, Reference Table Lookup, or Relational Data Object Lookup.
5. Click Next.
The New Lookup dialog box appears.
6. Select a physical data object or reference table in the Developer tool.
7. Enter a name for the transformation.
8. For On multiple matches, determine which row that the Lookup transformation returns when it finds multiple rows that match the lookup condition. Do not choose Return All for an unconnected lookup.
9. Click Finish.
The Lookup transformation appears in the editor.
10. In the Ports section in the Overview view, add ports to the transformation.
Create an input port for each argument in the :LKP expression. Create an input port for each lookup condition that you create. You can use an input port in multiple conditions.
11. In the Ports section in the Overview view, configure one port as the return port.
12. In the Lookup view, add one or more lookup conditions to compare the transformation input values with values in the lookup source or cache.
When the condition is true, the lookup returns a value in the return port. If the lookup condition is false, the lookup returns NULL.
13. Create a :LKP expression for a port in a transformation that allows expressions, such as an Aggregator transformation, Expression transformation, or Update Strategy transformation.
14. When you create a mapping, add the unconnected Lookup transformation to the mapping in the editor, but do not connect the ports to the other transformations in the mapping.