CODEWITHSUNDEEP
sql-serverssislookup-tablesssis-2008msbi
SChowdhury
SChowdhury

Reputation: 163

Full Cache lookup is not matching record that should be matched

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?

Upvotes: 0

Views: 3659

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 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.

Upvotes: 3

sandeep rawat
sandeep rawat

Reputation: 4957

Lookup matches exact values if there is any mismatch in case or datatype or data length it will tell mismatch.

Check data type, length, case for input and lookup data.

Upvotes: 0

Related Questions