Lookup problem.

I'm having a problem which seems simple enough, although I can't pinpoint the failure. The problem is that I have a lookup component that always fails for all rows if caching is enabled, but succeeds for all rows if caching is disabled (for peformance reasons I need to have caching enabled). The dataflow to which the lookup component belongs to references the table exactly once - in the failing lookup component, and the containing package does not have any other references to the table in question. I have tried to use "Use Results of an SQL query" with the same results. Any suggestions?

Thanks in advance

The cached Lookups are case sensitivite, while non-cached Lookups use DB collation rules to match data, this may cause different behavior.

A common workaround is to force case of the data in caching SQL statement and in the incoming data (e.g. make both upper case).

(update)

See also another recent thread on same issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2105670&SiteID=1

|||

Thanks Michael for your help. I can get the lookup component to succeed if on the Advanced tab, I check "Enable memory restriction" and check "Modify Caching SQL Statement". However, I didn't actually modify the SQL statement, I just re-used the existing one.

To debug this, I've redirected rows which fail (when full caching is enabled) to a text file. The field used for matching is a Unicode string and has the same case in both the database table and the within the SSIS data stream (in fact, they come from the same XML input file - an earlier package populates the tables, and this package retrieves the key for the inserted values).

I hope to understand this failure before I enable the workaround. Is there anything else I could try to debug this?

|||

Some more investigating revealed that this problem is related to the length of the strings being compared. Currently the table in the database has the field declared as nvarchar(10). However, in my testcase which fails, all strings have a length of 7. If I modify the table to use nvarchar(7) for the field being compared, the Lookup component with full caching succeeds.

When the field is defined as nvarchar(10) in the table, SSIS inserts the strings without any whitespace, that is the strings are stored in the table with a length of 7. This must mean that the lookup component is comparing the string plus padding. Is there way to eliminate this behaviour? I tried using a derived column before the lookup which calls TRIM on the field to compare, but it seems that a fixed number is required for the field's length.

Thanks!

|||Try putting LTRIM(RTRIM(col)) in the lookup's source sql, and trim the value coming into the lookup as well.