I am using a lookup and full cache, occasionally i get this warning:

[Lookup [150]] Warning: The component "Lookup" (150) encountered duplicate reference key values when caching reference data. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Now I know it is only a warning but it is highlighting a real issue.

Is there a way of capturing that this has happened?

If you want to know up front, do a duplicate check before processing. If just something you need to know to investigate the result, scan the logs - reasonably easy when using a DB to log

Thank you for the quick answer.

The job runs every night. Typically there are no duplicates but one could come along.  Are you saying this warning goes into the SQL Server logs?

or do you mean the SSIS logs?

I would have to enable onWarnings, I don't normally have those enabled

• Edited by John Edwards NZ 4 hours 6 minutes ago

Which version of SQL Server are you using? I have the joy of 2012 which has the integration catalog, for earlier versions then yeah, I guess it is a case of enabling logging first

currently 2008 r2

or do you mean the SSIS logs?

I would have to enable onWarnings, I don't normally have those enabled

• Edited by John Edwards NZ Wednesday, July 29, 2015 3:01 AM

Hi John,

Full cached mode means Lookup reads all the reference table into memory using single SQL command, and then uses this cached data. In this mode, the database is queried once during the pre-execute phase of the data flow. The entire reference set is pulled into memory. When building internal data structures, Lookup detects duplicates and issues this warning.

Please use the query below to verify that you dont have duplicate values on the columns that you've connected your input and the reference table.
If you are connecting A and B in reference Table, then
SELECT A,B,Count(*) FROM REFTABLE
GROUP BY A,B
HAVING COUNT(*)>1

Thanks,
Katherine Xiong