0

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?

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
SChowdhury
  • 163
  • 1
  • 11

2 Answers2

3

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.

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
  • 3* SQL Server: at database level, trailing spaces don't matter for = operator ('abc' = 'abc ' | 'abc '). Note: see also [this](https://www.timmitchell.net/post/2009/09/29/space-sensitivity-in-ssis-lookups/). – Bogdan Sahlean May 15 '16 at 06:30
  • I used UPPER(), and it worked. However, when I check data on both tables, they all are already in upper case. The collation of the columns are also same. Can you tell me why is it appearing to be in upper case when I select the record in the tables? – SChowdhury May 15 '16 at 11:51
  • You have to be more specific. SSIS and also T-SQL have the UPPER function. My assumption: very likely, you have/had issues because of `Full cache` setting and trailing spaces. – Bogdan Sahlean May 15 '16 at 12:18
0

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.

sandeep rawat
  • 4,797
  • 1
  • 18
  • 36