Monday, May 6, 2013

SSIS: Warning: The component "Lookup 1" (79) encountered duplicate reference key values


The component "Lookup 1" (79) 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.

Solution and Explanation: 

It is OK to ignore this warning, 

The default cache mode for the lookup is Full cache. 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.Full cached mode is usually the fastest.

In Partial cache mode, the lookup cache starts off empty at the beginning of the data flow. When a new row comes in, the lookup transform checks its cache for the matching values. If no match is found, it queries the database. If the match is found at the database, the values are cached so they can be used the next time a matching row comes in.

In No Cache mode, As the name implies, in this mode the lookup transform doesn't maintain a lookup cache (actually, not quite true - we keep the last match around, as the memory has already been allocated). In most situations, this means that you'll be hitting the database for every row.

Therefore its ok to ignore the warning, as full cache mode is the fastest. and can be used when you're accessing a large portion of your reference set or you have a small reference table.

Please provide your feedback for the post, if you find this post useful. 

No comments:

Post a Comment