Unable to load the csv file in the package

Posted on 2011-04-19
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
    LVL 6

    Expert Comment

    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

    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

    Just to can also turn ignore condition ON to disable preventing the job from terminating
    LVL 21

    Accepted Solution

    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


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now