Solved

How to make sure the existing record is not inserted into the table

Posted on 2014-09-16
4
162 Views
Last Modified: 2014-09-30
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PKC_ET_Ut_Base_EmailAddress'. Cannot insert duplicate key in object 'dbo.ET_Ust_Base'. The duplicate key value is (mi05@aol.com).".

Hi experts, since I have a primary key I cannot insert the duplicate value, is there a way I can only insert records that are not in the table. I am using SSIS to load the contents of CSV file into a table, please suggest.
Thanks!
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40325151
You can use the LOOKUP component from SSIS. What it does is to check the existence of a row and then you can define the actions for when it's true or false.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40325163
If I read the question correctly (which I might not be doing, as you have given few specifics apart from the error message), you have a Flat File Source and an OLE DB Destination, and some of the export into the OLE DB fails, and you want it not to fail.

Then go into the Error Output tab of the OLE DB Destination Editor and change "Error: Fail component" to "Error: Ignore failure", or "Error: Redirect row" if you want errors to be documented.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40326929
i would handle it differently:  Never attempt to add data directly into Production.  Instead add the data to a staging table.  you can then validate as much as you need and then and only then import in your case non-duplicate data into Production using simple T-SQL techniques (GROUP BY, DISTINCT, CTEs, etc.).
0
 

Author Closing Comment

by:sqlcurious
ID: 40353131
thanks
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question