Tuesday, February 03, 2009

Matching in Lookup Component in SSIS

Many SSIS programmers get confused with the comparison results of Lookup transformation and feels baffled ;-| . Lookup transformation in SSIS is used to lookup for a reference in the lookup table where it performs a equi-join and returns the referenced column.

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

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.

That's the behavior of Lookup Transformation in SSIS.


Post a Comment