Error Control

hi,

I'm trying to insert files from one table to anotherone. The problem is that the source table doesnt have any primary key, and it has duplicated PK that the destination needs to be unique. It's possible to ignore this kind of errors? I am using an OLEDB Destination transformation, and trying to omit this error configuring the error output, but it doesnt work.

Thanks!

There are several ways to deal with duplicates. But first you have to tell how would you like to handle them.

You can use a sort transformation with the remove duplicates checked; the thing is that you don't have control over which row get passed and which gets discarded. If the rows are identical; then this approach is easy enough.

Notice that Sort transformation will cache all incoming rows before sending them to the out put, so memory consumptions and performance could suffer.

|||

Thnaks Rafael,

The problem is that i need to control these duplicated records. Is any other way to handle this situation? what i need is to omit this error , continue executing over the flow, and record it on the log.

|||

You could do an insert of the full data set and on the errors (rows with duplicates) use the error output to write them to a "duplicates" table.

|||

Albertoim wrote:

Thnaks Rafael,

The problem is that i need to control these duplicated records. Is any other way to handle this situation? what i need is to omit this error , continue executing over the flow, and record it on the log.

You still are not providing the details on how do you want to handle the duplicates. Do you want to send them all to the duplicate tables, or do you want to insert at least one and reject the rest?

E.g if you have set of 3 duplicate rows, do you want to insert 1 and log the other 2 as duplicates or you want to log the 3 of them and insert none?

For the first case, and when the RANK() function is available in the source DB, like Oracle or SS2005 I use this:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

|||

I only want to insert the first record, all other duplicated records must by registered in the log (the error warning) not in other table, but it could be an alternative...

thanks both of you for your answers.