Unable to load the csv file in the package
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'.".
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'.".
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.
- "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.
Just to add...you can also turn ignore condition ON to disable preventing the job from terminating
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thnks
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