Full Cache lookup is not matching record that should be matched

Question

In my package, when I lookup a value based on FULL Cache Mode, it goes to no match output even when the datatype and value is same in lookup table and source. There is no duplicate row as well. When I put 'No Cache' mode, it's matching correctly. Any suggestion why is it happening so?

Answer 1

Full cache

The default cache mode for the lookup is Full cache. In this mode, the database is queried once during the pre-execute phase of the data flow. The entire reference set is pulled into memory. [...] One thing to note is that the lookup will not swap memory out to disk, so your data flow will fail if you run out of memory.

(source) This means that all compare operations are done by SSIS engine that is case sensitive and sensitive with trailing spaces. This means that 'abc' <> 'Abc' | 'ABC' and also this means that 'abc' <> 'abc ' (abs plus one space at the end of string).

For above reasons, if source value is 'abc' and reference data is 'ABC' or 'ABC ' => No Match.

No cache

As the name implies, in this mode the lookup transform doesn’t maintain a lookup cache (actually, not quite true – we keep the last match around, as the memory has already been allocated). In most situations, this means that you’ll be hitting the database for every row.

(source)

1. If [SQL Server] database is touched and
2. If column collation is case insensitive (all my databases are CI => 'abc' = 'Abc' | 'ABC') and
3. Because trailing spaces don't matter ('abc' = 'abc ' | 'Abc ' | 'Abc ')

For above reasons, if source value is 'abc' and reference data is 'ABC' or 'ABC ' => Match.