The Lookup transformation performs lookups by joining data in input columns with
columns in a reference dataset.
You use the lookup to access additional information in a related table
that is based on values in common columns.
You can configure the Lookup transformation in the following ways:
The Lookup transformation has the following
inputs and outputs:
Let me explain the comparison/matching behavior
of lookup transformation: It matches data differently when in cached (default)
and non-cached (Enable memory Restriction) mode. In cached mode comparison will
be case sensitive where as in non-cached mode comparison will be based on
collation level of column being matched. Mode :: Behavior
Cached (default) :: Case sensitive
Non-cached (Enable memory restrictions) :: Collation level of column Why?
It basically depends where the comparision is done. In cached mode SSIS
reterives data from the source and comparision is done at the client on the byte
level which is CASE SENSITIVE. Whereas in Non-Cache mode ( Enable memory
Restriction) comparision is done at database and is based on the collation level
of the column being matched.