Unable to load the csv file in the package

Posted on 2011-04-19
Medium Priority
Last Modified: 2012-05-11
Hi experts, I am getting the following error when trying to insert data into the table via ssis package:
Pls help how to resolve this.

[Data Load into source [102]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E2F  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E2F  Description: "Violation of PRIMARY KEY constraint 'PK_source_test'. Cannot insert duplicate key in object 'dbo.source_test'.".
Question by:sqlcurious

Expert Comment

ID: 35429947
it would appear that the table you are inserting your data into has a primary key that the csv isn't adhering to.
first i would try to insert the csv into a table with no constraints on it.

if that works then i would look at the table you are trying to import the information into.
have you already tried to import the table once?

and finally(if you can''t find the problem with the PK) i would add an identity column to the table to be the primary key, that way the records are always unique
LVL 23

Expert Comment

ID: 35430066
hi sqlcurious,
- "record is available" - means the record you are inserting already exist in that destinatin table.
- "Violation of PRIMARY KEY constraint 'PK_source_test" - same as above error, the Primary Key data you are inserting already exist in that table.

- i would suggest you to create a dummy/temp table similar to the production table that you are trying to load to. make sure that dummy/temp table is empty and doesn't have any constraint created. load using SSIS package to that dummy/temp table.
- once it's loaded, check if there is duplicate data in that dummy/temp table itself
- next compare Primary Key data between the existing table and the dummy/table to find duplicate record. you might want to delete the duplicate in the dummy/temp table first to clean-up the records to make sure no duplicate record exist.
- then do insert from dummy/temp table into the existing table.

LVL 13

Expert Comment

ID: 35430096
Just to add...you can also turn ignore condition ON to disable preventing the job from terminating
LVL 21

Accepted Solution

Alpesh Patel earned 2000 total points
ID: 35430613
hey, there is a primary foreign key relation ship problem. Please check your CSV data first. or your can ingnore the record which have errors.

Author Closing Comment

ID: 35749719

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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