Solved

What does this SQL Import Error Message mean?

Posted on 2008-10-27
7
1,472 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

929 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

12 Experts available now in Live!

Get 1:1 Help Now