September 8, 2011 at 3:01 pm
Hi,
I have developed a package and completed the performance test also for which I had to make few changes. Suddenly today it started hanging on execution. I moved back to previous versions where it didn't have the latest changes and it used to work fine. But, still its hanging on at old version too. When I started debugging, its givign error at one lookup which was completely fine before. At the progress tab its giving following error:
[Lookup ncoa initial [2605]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data.
The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only.
Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.
The lookup query:
select c.biCustomerID, c.dtLastEmailMatchAttempt, c.dtLastPhoneMatchAttempt, c.dtCustomerDateOfBirth, ca.biAddressID, c.vchCustomerFirstName, c.vchCustomerLastName, nf2.vchName from ActiveCustomer c
inner join ActiveCustomerAddress ca on c.biCustomerID = ca.biCustomerID
left outer join NicknameFamilies nf1 on c.vchCustomerFirstName = nf1.vchName
left outer join NicknameFamilies nf2 on nf1.iNicknameFamilyID = nf2.iNicknameFamilyID
I am completely lost. Please help me.
September 9, 2011 at 6:48 am
Apparently the reference data contains some duplicate values, so you'll need to clean that up a bit.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 9, 2011 at 6:48 am
I assume you probably have new data inputted in one of your tables which is causing the rows you are returning to have some duplicates.
Try running the same query in SSMS, using GROUP BY and HAVING COUNT(*) > 1:
select c.biCustomerID, c.dtLastEmailMatchAttempt, c.dtLastPhoneMatchAttempt, c.dtCustomerDateOfBirth, ca.biAddressID, c.vchCustomerFirstName, c.vchCustomerLastName, nf2.vchName from ActiveCustomer c
inner join ActiveCustomerAddress ca on c.biCustomerID = ca.biCustomerID
left outer join NicknameFamilies nf1 on c.vchCustomerFirstName = nf1.vchName
left outer join NicknameFamilies nf2 on nf1.iNicknameFamilyID = nf2.iNicknameFamilyID
GROUP BY c.biCustomerID, c.dtLastEmailMatchAttempt, c.dtLastPhoneMatchAttempt, c.dtCustomerDateOfBirth, ca.biAddressID, c.vchCustomerFirstName, c.vchCustomerLastName, nf2.vchName
HAVING COUNT(*) > 1
I expect you'll find some rows. If you fix whatever is causing the duplicate rows to appear, then youl'l be fine. If you can't, then you'll need to leave the GROUP BY clause in there.
September 9, 2011 at 7:58 am
Thank you so much for your responses. I have tried group by but, I need duplicate values (but not vchName) as I am trying fuzzy lookup manually (as existing direct fuzzy lookup not works for remote systems). So, I am collecting all the related (sounds similar) name for given last name. But, I will try to change the query according to your suggestions.
Thanks
September 9, 2011 at 8:21 am
Something you might want to look into is SOUNDEX encoding.
Might be useful - try it out:
SELECT
SOUNDEX('John'),
SOUNDEX('Jon'),
SOUNDEX('Jonn'),
SOUNDEX('Jhon'),
SOUNDEX('Smith'),
SOUNDEX('Smth'),
SOUNDEX('Smithh'),
SOUNDEX('Smit')
All the firstnames and lastnames end up with the same SOUNDEX code. So you could use that maybe as a way of "fuzzy" matching your lastnames.
September 12, 2011 at 11:40 am
Sorry for the late reply. But, very thankful to your advice.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply