Level: Intermediate
Lookup transformation is an essential ingredient of a Data Flow task accessing multiple database servers. I always use the Full Cache option when the reference table is small enough to be stored in memory. However, this caching option needs more than setting up your Lookup task to work. Here are few things to check when your Lookup transformation fails to detect a match you are expecting it to detect:
Case sensitivity – the columns being compared must have exactly the same case. Otherwise, the match will fail.
Solution: Use UPPER() OR LOWER() on BOTH the Lookup AND the pipeline columns
Trailing White spaces – spaces before and after the column value can cause the match to fail
Solution: Use LTRIM() and RTRIM() on both the Lookup and pipeline columns if you are not confident that white spaces can appear in either the Lookup or the pipeline columns
CHAR data type – When one of the matching columns is of type CHAR and and the inserted value has length less than the defined length of the column, SQL Server will pad white spaces until the defined length is reached.
For example, you defined a column nm_Month CHAR(15) and inserted ‘August’, the value stored in the table will be ‘August ‘ . In this case, the Lookup match will fail.
Solution: Use at RTRIM() on the column with CHAR data type
Furthermore..
1. To avoid having unnecessary trailing white spaces in your Staging and Datawarehouse databases, use the TRIM() function in all the columns in the very first step of your ETL. This is part of your clean-up process and will save you time later when you need to use Lookup transformation
2. Use appropriate data types. Use CHAR only when the size of your column is consistent. Otherwise, use VARCHAR.
Hope that helps !
Vanie's View 