Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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

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
sqlcurious
Asked:
sqlcurious
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Phillip BurtonCommented:
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
 
Anthony PerkinsCommented:
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
 
sqlcuriousAuthor Commented:
thanks
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now