Duplicates

curiousss


Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim
DELETE day from dim_date where day in(Select day from date ...

Phil Brammer


This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date

Rafael Salas

Your post is not clear enough...

How you are getting a violation PK error when running a Select

Please provide more details

curiousss

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".


Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Phil Brammer

curiousss wrote:

Ok, let me clarify.. I have a sql command in OLE db Source: SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine) But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.". Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how... Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key

curiousss

Ah, ok, the destination table primary key is Day (int).

Pipo1

How is this Day columnn populated Is it a surrogate key or is it a date format like 20070214

You say you want to delete the duplicates in the destination. And then insert them again Why not skip the records that are already there

Pipo1

Phil Brammer

See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.

curiousss

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date

Phil Brammer

curiousss wrote:

Thank you so much! How about if I delete and empty the whole table. This command does not seem to work though. DELETE day FROM dbo.Dim_Date

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...

curiousss

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be

[Lookup Salesperson [7405]] 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.

Phil Brammer

curiousss wrote:

Thank you... Problems disappeared when I deleted the tables, and executed them again. So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be [Lookup Salesperson [7405]] 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.

You might want to try a "select distinct column from table" in your lookup.

curiousss

Thanks, not eliminated with DISTINCT. Should I leave them or remove them

Phil Brammer

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.

curiousss

How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this
"DELETE id, salary from dbo.Dim_D where id = 199