Duplicates

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 ...

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

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

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

Please provide more details

|||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 ...|||

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?|||

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

|||

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

|||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.|||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 ?|||

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...|||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.|||

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.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

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.|||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 = 199sql