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
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.
That's the behavior of Lookup Transformation in SSIS.
Subscribe to:
Post Comments (Atom)
Google Dataset Search
Google's Vision statement is “ to provide access to the world's information in one click. ” Google’s mission Statement is “ ...
-
Q1. WHAT is SQL Server Reporting Services(SSRS)? SQL Server Reporting Services is a server-based reporting platform that you can use to cre... -
Many times I have been asked by SSIS programmers that they face difficulties when they need to select some specified columns and rows or the... -
This is a reference answer to a post on MSDN forum but is very useful Question: While copying and renaming a file to a dynamic location...
No comments:
Post a Comment