When you create a Lookup transformation, you can choose a relational table, flat file, or a source qualifier as the lookup source.
Relational Lookup Transformation: When you create a Lookup transformation using a relational table as a lookup source, you can connect to the lookup source using ODBC and import the table definition as the structure for the Lookup transformation.
Configure following options with Relational lookup Transformation:
- Override the default SQL statement to add a WHERE clause or to query multiple tables.
- Sort null data high or low, based on database support.
- Perform case-sensitive comparisons based on the database support.
Configuring Relational Lookups in a Session: When you configure a relational lookup in a session, configure the connection for the lookup database on the Transformation View of the Mapping tab. Choose the Lookup transformation and configure the connection in the session properties for the transformation.
Choose from the following options to configure a connection for a relational Lookup transformation:
- Choose a relational or application connection.
- Configure a database connection using the $Source or $Target connection variable.
- Configure the session parameter $DBConnectionName or $AppConnectionName, and define the session parameter in a parameter file
Flat File Lookup Transformation: When you create a Lookup transformation using a flat file as a lookup source, select a flat file definition in the repository or import the source when you create the transformation. When you import a flat file lookup source, the Designer invokes the Flat File Wizard.
Configure following options with flat file lookup Transformation:
- Use indirect files as lookup sources by configuring a file list as the lookup file name.( example using DEPT1.txt , DEPT2.txt )
- Use sorted input for the lookup.
- Sort null data high or low.
- Use case-sensitive string comparison with flat file lookups.
Using Sorted Input for Flat File Transformation: When you configure a flat file Lookup transformation for sorted input, the condition columns must be grouped. If the condition columns are not grouped, the Integration Service cannot cache the lookup and fails the session. For optimal caching performance, sort the condition columns.
For example: A Lookup transformation has the following condition:
Order_ID = in_OrderID
Customer_ID = in_CustID
In the following flat file lookup source, the keys are grouped, but not sorted. The Integration Service can cache the data, but performance may not be optimal.
The keys are not grouped in the following flat file lookup source. The Integration Service cannot cache the data and fails the session.
Configuring Flat File Lookups in a Session: When you configure a flat file lookup in a session, configure the lookup source file properties on the Transformation View of the Mapping tab. Choose the Lookup transformation and configure the flat file properties in the session properties for the transformation.
