Solved

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

Posted on 2014-09-16
4
161 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 49

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql update 2 37
how to make geography query faster?  SQL 7 45
SQL syntax question 6 42
SQL 2012 Instance Problem 3 58
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

749 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