Solved

What does this SQL Import Error Message mean?

Posted on 2008-10-27
7
1,488 Views
Last Modified: 2012-05-05
I am importing an Excel file in to SQL 2005 but I am receiving the following error message:

- Copying to [dbo].[table] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Unspecified error".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with input column "identity" (184) on input "Destination Input" (141). The column status returned was: "The value violated the integrity constraints for the column.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (141)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (141)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - iida_members_update_new" (128) failed with error code 0xC0209029 while processing input "Destination Input" (141). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 

***** There are no duplicates or non int values in the identity column of the excel file.  Where should I look next?
0
Comment
Question by:trumpman
  • 3
  • 3
7 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22813780
My guess is that you're importing data that violates a primary key or a unique constraint....or possible a foreign key constraint.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22813834

If you are not trying to insert duplicate records check SQL version because "Microsoft SQL Server Native Client 10.0" is the provider for SQL 2008.
0
 

Author Comment

by:trumpman
ID: 22813959
@chapmandew:

How can I check this?

...here is the end of my create table{}...

 CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED
(
      [identity] ASC
) ON [PRIMARY]
) ON [PRIMARY]


@PedroCGD:

What do I change it to to reflect the current version?  I have almost fully upgraded to 2008 now.  However, some old dbases are still on 2005.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 22

Expert Comment

by:PedroCGD
ID: 22814133
Try create a new connection to a SQL Native client 9.0 (SQL 2005)
See image
regards
SQL.JPG
0
 

Author Comment

by:trumpman
ID: 22814295
@PedroCGD:

I tried importing a separate table and was successful.  While the provider difference might not be an accepted standard, I do not believe this is causing my error.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22814372
ok... maybe, but the errro is inserting not reading.
but the error seems that you are trying to insert a forerign key that does not exist in related table...
regards
0
 

Author Comment

by:trumpman
ID: 22814979
@Chapmandew, your original post was correct.  I removed all constraints and noticed that there were four rows at the end of the table after import to SQL with all NULL values.  I went back to the excel file and selected the first four empty rows at the end of the file and deleted them.  Importing the file after this adjustment posted no errors or NULL rows.

- Trumpman
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Passing value to a stored procedure 8 107
How can I get this column in my query? 2 50
SQL Server Insert where not exists 24 46
Email Notifications for SQL 2005 9 35
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 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