Lookup

Common problems with caching and matching values

Users sometimes run into a puzzling situation when they use caching in the lookup. With caching settings set to Full Cache they get no hits - all the rows flow to the error output. However, with caching set to Partial or none, (memory restriction is enabled), things work as expected.

The common reason for this is that they are comparing strings which have leading or trailing spaces, for example: “Gary” with “Gary “

When caching is Full (memory restriction is off) SSIS builds a hash table for the cache in order to do the comparisons more quickly. The hash values for the strings quoted are, of course, different.

When memory restriction is on, string compares are used and the two quoted strings match.

You can trim incoming datea in the source query or using an expression.

Trim reference data using a SQL query rather then selecting a table in the lookup. SQL queries are better in lookup anyway because you only cache the columns (and rows) you asked for, rather than the entire table.

Case Sensitivity Problems

Lookups are case sensitive, so you may need to force the case for the test to succeed. Force the case of your source data in the source adapter if possible. For the lookup data, you can change the lookup Reference Table to be a SQL query and force the case in the statement. The Advanced tab will allow you to change the SQL statement for the lookup test, and force case in here as well.

Data Type Problems

Data types must match exactly. The join columns, between the input or source data and the reference data must be of exactly the same type. The following error indiactes two columns are used in the join, and the types do not match:

 The following columns cannot be mapped:


 [DDD, BackOfficeSoftwareKey]


 One or more columns do not have supported data types, or their data types do not match.

To correct this you must convert one of the columns to match the other. There are several options available, such as convert in the source adapater, such as a SQL query. You can change the lookup Reference Table to be a SQL query and include a CAST or CONVERT within the SQL Statement. You can also use a transform, such as the Data Conversion transform, or the Derived Column transform.

The Lookup does not support the full range of SSIS data types. The following types cannot be used as join columns:

• DT_R4
• DT_R8
• DT_TEXT
• DT_NTEXT
• DT_IMAGE