How to avoid re-inserting data (duplicates) into SQL Server table while re-running SSIS package that loads data?

Question

I have created a package is SSIS. It's working fine for first time insertion. When I am running the package through SQL Server agent jobs, I am getting duplicates inserted when the scheduled job is inserting data.

I don't have any idea about how to stop inserting multiple duplicate records.

I am expecting to remove duplicates insertion while running deployed package through SQL Server Jobs

Answer 1

There are 2 approaches to do that:

(1) using SQL Command

This option can be used if source and destination are on the same server

Since you are using ADO.NET source you can change the Data Access mode to SQL Command and select only data that not exists in the destination:

SELECT *
FROM SourceTable
WHERE NOT EXISTS(
                SELECT 1
                FROM DestinationTable
                WHERE SourceTable.ID = DestinationColumn.ID)

(2) using Lookup Transformation

You can use a Lookup transformation to get the non-matching rows between Source and destination and ignore duplicates:

• UNDERSTAND SSIS LOOKUP TRANSFORMATION WITH AN EXAMPLE STEP BY STEP
• SSIS - only insert rows that do not exists
• SSIS import data or insert data if no match
• Implementing Lookup Logic in SQL Server Integration Services