0

I have a question about SSIS Load Excel Files. I have an excel file with data, and I want if in excel file have duplicate values, stop the process, and don't insert to the database, throw an error and send mail. If I have a unique record, insert it in the database.

Can you help me?

enter image description here

enter image description here

sima
  • 13
  • 2
  • I would, initially, start at looking at `UNIQUE INDEX`es/`UNQIUE CONSTRAINT`s. Good luck! – Thom A Nov 01 '21 at 09:38
  • [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/q/284236/2029983) – Thom A Nov 01 '21 at 09:38
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Nov 01 '21 at 16:42

1 Answers1

1

Create a primary key on your destination table and it'll behave as you desire.

Syntax of your SQL should look something like this:

ALTER TABLE TableName
ADD CONSTRAINT PK_TableName
PRIMARY KEY (ColumnName1,ColumnName2)

https://www.w3schools.com/sql/sql_primarykey.ASP

To easy error handling implement an error handler where on error you move the duplicate row to an error table, saves you a lot of time searching for the duplicate row.

Peter Csala
  • 17,736
  • 16
  • 35
  • 75
HansG
  • 167
  • 7
  • yes. i have a primary key on destination table, but i have a lookup ( i added a new image on my post) and i must redirect rows to no match output to destination table. Can i check duplicate values in excel file or not? – sima Nov 02 '21 at 07:40
  • You could do a different lookup first with no match on destination first where AND count > 2 and handle these first – HansG Nov 02 '21 at 07:46
  • Example of this step: https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table – HansG Nov 02 '21 at 07:49