SSIS Lookup component only returns matching rows when using partial mode

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 20开发者_开发问答05 SSIS. Any help appreciated..

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.

Link

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).

Here is an explanation of why this is happening:

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