Problem with Lookup warning.

Question

I am received the following warning on my lookup.

[LKP - idDimCustomer for Issuer [7830]] 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 way that I constructed the SQL for the lookup originally would have returned duplicated keys but I changed it to guarantee the keys were unique.  When we use the term 'key' in this context we are talking about the 'joins' we make between the Available Input columns and the Available Lookup Columns on the 2nd tab of the component yes?

I have proved that my keys are unique by using a group by clause on the 'key' columns and a COUNT() column and returning records HAVING count() > 1.  Nothing is returned thereby proving the uniqueness of my key.  But I continue to receive this warning.

So either I am not understanding the warning correctly OR there is a bug in SSIS.

Assuming that my understanding is the problem can anyone help me out by explaining more precisely what this warning means?

Just in case it might be a bug this is the version of IS designer I am using (BIDS)

Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

against this version of SQL server

9.00.3042.00 SP2 Enterprise Edition (64-bit)

TIA


This is the SQL that I am using for the lookup ...

select Convert(int,IntStoredAsCharInDB) as IntData, idDimCustomer from dimCustomer where FrontOfficeCustomerReference <> '-' and FrontOfficeCustomerReference is not null
and IsCurrentRecord = 1

This is the SQL that I used to prove the uniqueness of my data

select IntStoredAsCharInDB, idDimCustomer, COUNT(IntStoredAsCharInDB) from dimCustomer
where IntStoredAsCharInDB <> '-' and IntStoredAsCharInDB is not null
and IsCurrentRecord = 1
GROUP BY IntStoredAsCharInDB,idDimCustomer
HAVING COUNT(IntStoredAsCharInDB) > 1

 

 

This returned no records.  I am using Convert(int,IntStoredAsCharInDB) as the field to join to.
For good measure I also checked this

select IntStoredAsCharInDB, idDimCustomer, COUNT(idDimCustomer) from dimCustomer
where IntStoredAsCharInDB <> '-' and IntStoredAsCharInDB is not null
and IsCurrentRecord = 1
GROUP BY IntStoredAsCharInDB,idDimCustomer
HAVING COUNT(idDimCustomer) > 1

 

 

It also returned no records.

Answer 1

Your queries are not the same.  The lookup query is constraining where FrontOfficeCustomerReference IS NOT NULL, yet your "proof" queries are constraining by IntStoredAsCharInDB IS NOT NULL.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

In addition the CONVERT() that you use could potentially lead to duplicates that dont exist in char format.  I would make the queries exactly the same for the proof..

Answer 3

Also do one with COUNT(*) rather than COUNT(column) which I believe eliminates individual NULL values e.g.

select    Convert(int,IntStoredAsCharInDB) as IntData
        , idDimCustomer 
        , count(*)
from      dimCustomer
where     FrontOfficeCustomerReference <> '-'
and       FrontOfficeCustomerReference IS NOT NULL
and       IsCurrentRecord = 1
group by  Convert(int,IntStoredAsCharInDB)
        , idDimCustomer
having    count(*) > 1

--Also try the following

select    Convert(int,IntStoredAsCharInDB) as IntData
--        , idDimCustomer 
        , count(*)
from      dimCustomer
where     FrontOfficeCustomerReference <> '-'
and       FrontOfficeCustomerReference IS NOT NULL
and       IsCurrentRecord = 1
group by  Convert(int,IntStoredAsCharInDB)
--        , idDimCustomer
having    count(*) > 1

To see the offending row you could also try the following:

with testCTE AS
(
select    Convert(int,IntStoredAsCharInDB) as IntData
        , idDimCustomer 
        , counter = ROW_NUMBER() OVER (PARTITION BY Convert(int,IntStoredAsCharInDB) ORDER BY idDimCustomer)
from      dimCustomer
where     FrontOfficeCustomerReference <> '-'
and       FrontOfficeCustomerReference IS NOT NULL
and       IsCurrentRecord = 1
)
SELECT *
FROM testCTE
WHERE counter > 1

---

Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

Answer 4

Adam,
Yes, COUNT([Column]) will return a count of only non-null values in the column, [Column].

COUNT(*), COUNT(1), COUNT('count me please') will count all rows including NULLs.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 5

Thanks a lot and apologies for posting the confusing code.
You got straight to the bottom of the issue which was my niave use of COUNT().  Adam your later two queries flushed out the offending duplicate.


Cheers,

Answer 6

Thanks a lot and apologies for posting the confusing code.
You got straight to the bottom of the issue which was my niave use of COUNT().  Adam your later two queries flushed out the offending duplicate.


Cheers,

In the future, use the same query as your source.  Just add COUNT(*) as a new column, and group by all of the others.  The group by should include the exact SELECT statements including any derived columns (like your CONVERT functions).

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer