I am using a Transform Data Task to copy data from one source to another. In order to make sure I ignore duplicate rows, I am using a lookup on the primary key field of the target table that I use to check if the key already exists. If it does, then I perform a TransformStat_SkipRow as opposed to a TransformStat_OK in my ActiveX transform.
The only problem is, the isempty status of the lookup is not being fired when I come across a duplicate. The query in the lookup uses the primary key as a parameter and returns another field if it finds the target record.
Can anyone suggest either what I might be doing wrong or an alternative?