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