SSIS Lookup component only returns matching rows when using partial mode

Question

I am attempting to use a lookup transformation in my data transformation package and all of the other lookup transformations went well but one component returns the matching rows only when I enable the partial cache mode. If I use the full cache mode, all data is routed to the error path. I am using SQL 2005 SSIS. Any help appreciated..

Answer 1

Here is an explanation of why this is happening:

http://technet.microsoft.com/en-us/library/ms137820.aspx

Answer 2

Sounds like it could be a collation issue, but if all the rows are being routed, I'm not so sure.

Anyway, if it is, here's some stuff you need to know:

Lookup actually has three modes, full, partial, and no cache.

http://blogs.msdn.com/b/michen/archive/2007/10/03/ssis-lookups-modes.aspx

http://geekswithblogs.net/dtotzke/archive/2007/02/06/105585.aspx

When there is no caching, the database is effectively doing the matching (using the database collation). When there is caching, the lookup component is doing the matching (using a case-sensitive collation).