Lookup allows duplicate primary key

I have a Transform Data Task that copies a lot of data from my source system. Unfortunately, I cannot use a DISTINCT in the SQL from the source system, due to a very poor ODBC driver!
So, when I am creating my primary key, I am trying to do a lookup on the PK column before I insert the record to see if it exists. If it does, then I skip the row. The lookup references the target database of the task.

The problem I have is that the lookup doesn't find any duplicates loaded from the database. It allows them through and causes the database to throw a primary key error.

Has anyone experienced this, or think they know what I'm doing wrong?

i would say that the error you are getting is just what you want

the db is preventing a duplicate entry, so just ignore it

How would I make the database skip that row? Iwas using the lookup to check for existing primary keys and using the TransformStat_SkipRow to skip the row if one existed. It's just that the lookup never found that row...

no idea. sorry

don't know what TransformStat_SkipRow is

i thought you said "If it does, then I skip the row."

TransformStat_SkipRow is the status that the ActiveX script of a Transform Data Task is set to when you want to skip processing of the current row for any reason. It is this condition I'm setting once I use the lookup. i.e. :

if isempty (lookup.Execute(id)) then
Main=TransformStatOK
else
Main = TransformStatSkipRow
end if

It's the lookup that's not returning a null value for some reason.

haven't got a clue

maybe ask in the ActiveX forum

(do we even have an ActiveX forum?)