Hi all, I have several lookup linked to each other to fetch some config. values stored in the same table. From time to time, those lookups fail with error "Row yielded no match during lookup". What is strange is that the problem is directly connected with caching: when I disable it the values are found but not when caching is activated ! Since that table is very small (20 records or so) I can't figure out why it is connected with caching. Any idea ? Thanks.

worth noting that in full cache mode the cache is generated only once during data flow pre execution phase. it could be that the rows it isn't finding are inserted after the pre execution phase. Also remember ssis lookups are case sensitive whereas t-sql joins are usually not (depending on case sensitivity), so you may have to apply an UPPER or LOWER function to both your dataflow and lookup set. http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspxMy Blog "Karl Beran's BI Mumble"

Thanks for ur reply. Some more info: it is config. values set up once (we don't change them anymore, they are permanently stored in the table). What is disturbing is that the lookup might fail now but execute the package a few hours later without any change and it works perfectly. Any idea as to get the root of the problem ? Thanks.

Do you get any warning or errors messages? Have you placed data viewers on the separate branches of the data flow? Are you dumping the failed rows out to an error table/file? If these are config values, wouldn't it be more sensible to set them as variables through package configurations or alternatively populate an object variable with an execute sql task?My Blog "Karl Beran's BI Mumble"

Hi Karl, I set up a dump to file for the failed rows on those lookups, I always get the same error: Error code = -1071607778, Error column = 0. When I review the data on the failing row nothing is wrong (uppercase is ok on both side, no trailing blank and so on). I did a query on the config. table and the rows are foudn according to the lookup criteria. Since the lookups never fail when caching is off, I guess u r right about a pre-execute problem ...still hard to debug.

I would suggest double checking the string lengths just in case. Do this by pulling the columns from both sources into a row count destination and have a look at the column meta data. Dump both columns out to a single field .csv file and check for trailing spaces. Is there a loop in there somewhere hence the pre execute cache problem, or do you have a lookup cache file?My Blog "Karl Beran's BI Mumble"