I have 2 tables Staging and Master. I would like to add a new row in the Master if the data from staging table does not exists in Master. Staging table might have duplicate data, so I don't want to add the duplicate data in the Master. I have 2 lookups, First on Id, FirstName_Soundex and LastName_Soudex and if there are records with no match then another lookup on LastName and FirstName. Staging Table : Id, FirstName,FirstName_Soundex, LastName and LastName_Soundex Master Table : Id, FirstName,FirstName_Soundex, LastName and LastName_Soundex My main question is how can I avoid adding duplicate data into Master from Staging? and I don't want to remove duplicate records from Staging.

Yoiu could switch your Data Access Method of the Destination Adapter on the Data Flow to "Table or view" and drag and drop the red Error Output onto a Row Count or Flat File Destination. But that also reauires something like a PK or other Unique Index on the Destination table. or, Instead of using Soundex, try a Fuzzy Lookup (if you have Enterprise edition).Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

When you are selecting rows from Staging, use DISTINCT or GROUP BY to avoid having dupes get into the datapipe in the first place.-Tab Alleman