Upload to excel files through SQL Server Management Studio

Question

I am trying to import the excel file with country list into my SQL Server database, but I get this error. What might be wrong?

Validating (Error)
Messages
Error 0xc020201b: Data Flow Task 1: The number of input columns for Destination - Countries.Inputs[Destination Input] cannot be zero. (SQL Server Import and Export Wizard)

Error 0xc004706b: Data Flow Task 1: "Destination - Countries" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)

Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
(SQL Server Import and Export Wizard)

Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
(SQL Server Import and Export Wizard)

This is a preview of my list in Excel:

1 Afghanistan 2 Albania 3 Algeria 4 American Samoa 5 Andorra
6 Angola 7 Anguilla 8 Antigua & Barbuda 9 Argentina 10 Armenia
11 Aruba 12 Australia

So when i first imported into the temporary table, and then tried to copy into my table, i get this error:

    - Copying to [dbo].[Countries] (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 11.0"  Hresult: 0x80004005  Description: "Unspecified error".
 (SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with Destination - Countries.Inputs[Destination Input].Columns[countryId] on Destination - Countries.Inputs[Destination Input]. 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 "Destination - Countries.Inputs[Destination Input]" failed because error code 0xC020907D occurred, and the error row disposition on "Destination - Countries.Inputs[Destination Input]" 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 - Countries" (46) failed with error code 0xC0209029 while processing input "Destination Input" (59). 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)

When i perform the query, i get:

Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK_dbo.Countries'. Cannot insert duplicate key in object 'dbo.Countries'. The duplicate key value is (1).
The statement has been terminated.

(26 row(s) affected)

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

You have to do data cleaning in your excel file. I think you are trying to import to an EXISTING table.

I suggest following workflow.

import to brand new table, like tempExcelImport
Insert Into OLD_TABLE Select * from tempExcelImport
This will give you more informative errors for your data, giving you row numbers.
clean your data (fix errors in your rows) and go to step 2

EDIT:

When you are using import/export wizard Column Mappings, choose your destination table as new table name, like tempExcelImport

Destination

Identifies the selected destination table, view, or query.

Create destination table/file

Specify whether to create the destination table if it does not already exist.

SqlServer Import Wizard

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 15 '14 at 21:28

Atilla Ozgur

14,339
3
49
69

Yes, i am importing to an existing table. – Harihar Shankar Nov 15 '14 at 21:34
due to existing constraints, you can not import it to that table. Try my workflow – Atilla Ozgur Nov 15 '14 at 21:34
You use IDENTITY in your target table, You need to set identity insert on. Look to this link http://stackoverflow.com/questions/20948819/error-0xc0202049-data-flow-task-1-failure-inserting-into-the-read-only-column – Atilla Ozgur Nov 15 '14 at 21:57
You are still using wizard. Are you not using INSERT INTO Part. Some of your rows are not suitable for insertion into that table. You need to fix that errors. – Atilla Ozgur Nov 15 '14 at 22:05
Ok, i did what you said, and now i have this error that i edited. – Harihar Shankar Nov 15 '14 at 22:29
As you can see you have duplicate row in your data (The duplicate key value is (1)). This means you have existing row in your existing table. – Atilla Ozgur Nov 15 '14 at 22:31

Upload to excel files through SQL Server Management Studio

1 Answers1