x

sql import error

I'm getting the below errors when I try to import from an Excel file. I've ensure that the field types were correct to handle the data. I'm importing into a new table. Can someone help me understand why I'm getting the below errors?

Thanks in advance. Doug

Copying to [TestSQL].[dbo].Point of Sale 1 Messages • Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error". (SQL Server Import and Export Wizard)

• Error 0xc020901c: Data Flow Task: There was an error with input column "Distributors ID" (60) on input "Destination Input" (48). 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: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (48)" failed because error code 0xC020907D occurred, and the error row disposition on "input "Destination Input" (48)" 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: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Point of Sale 1" (35) failed with error code 0xC0209029. 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)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)
more ▼

asked Nov 11, 2010 at 05:31 PM in Default

Doug gravatar image

Doug
43 4 4 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

• Error 0xc020901c: Data Flow Task: There was an error with input column "Distributors ID" (60) on input "Destination Input" (48). The column status returned was: "The value violated the integrity constraints for the column.". (SQL Server Import and Export Wizard)

that part says it failed integrity constraints of the column. Normally it is becuase it is too long, or if you have specified a number field, there may be a characters in the field.

If you are having problems, then either save the SSIS package and redirect the errors on the destination so you can see the rows that are failing, or you can import everything as text and then you can check the whole table.

Good luck.
more ▼

answered Nov 11, 2010 at 06:57 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 11 13 14

I'm not certain how to redirect the the errors to another destination, I'm running the import from SQL management studio using import.

I've changed the file to the following field types: Distributor ID nvarchar (255) Nullable Company ID nvarchar (255) Nullable Company Name nvarchar (255) Nullable Country nvarchar (255) Nullable Date datetime Nullable Invoice No nvarchar (255) Nullable Amount float

There are only 10 records and none of the record fields contain more than 20 characters. I was able to import the table and it looks like the import did not accept the field types I changed. For example, field Distributor ID "C105951" was imported as NULL or Company ID "12586" was imported as NULL. Not certain why the import does not accept the field type I entered above?? Any ideas?

Thanks.
Nov 12, 2010 at 08:32 AM Doug

@Doug Usually, the errors like you get for Distributor ID are caused by the value sniffing, meaning Excel examines first few (usually 10) rows to determine the type and if it thinks that the column values are numeric then it will place null for the other values. The workaround is usually to sort the data suitably first so the texts end up on the top. In your case, this is very unlikely though because you only have but 10 records.

The drivers could be a culprit though in case if you have a 64-bit SQL Server, because, sadly, there are no 64-bit Excel drivers. I had a problem like this one time, and there was nothing I could do except to find a 32-bit SQL Server, import data from Excel and then export it out to the 64-bit SQL Server. Pretty annoying, but I could not find any better solution.
Nov 12, 2010 at 09:06 AM Oleg
I've converted the file to CSV and there are no empty rows. I was able to import all the fields in from the CSV file using Flat file source and leaving all fields as nvarchar and NULLable. Not certain why it did not accept the Excel file.
Nov 12, 2010 at 09:07 AM Doug

Oleg, Just to verify, are you saying the Excel driver or SQL driver? I've had Excel crash a few times in the past couple of weeks.

Thanks.
Nov 12, 2010 at 09:23 AM Doug

@Doug Whatever the driver is used when Excel File is selected as a source. It depends on the Excel file version. I believe that for Excel 2003 Microsoft.Jet.OLEDB.4.0 for Excel is used. If this is the version you have, you can also try to use openrowset from SSMS query window just to see if it runs or not:

select * -- into #SomeTable
    from openrowset
    (
        'Microsoft.Jet.OLEDB.4.0',
        'Excel 8.0;Database=C:\SomeFolder\SomeFile.xls', 
        [SheetName$]
    );

If you have Excel 2007 then the driver should be Microsoft.ACE.OLEDB.12.0, so the openrowset becomes:

select * -- into #SomeTable
    from openrowset
    (
        'Microsoft.ACE.OLEDB.12.0',
        'Data Source=C:\SomeFolder\SomeFile.xls;
            Extended Properties="Excel 12.0" ', 
        [SheetName$]
    );
From what I understand, if you have 64-bit SQL Server then both SSMS and openrowset are error prone.
Nov 12, 2010 at 10:38 AM Oleg
(comments are locked)
10|1200 characters needed characters left
You should check that the Excel worksheet does not contain empty rows. You can do this by saving the Excel file in CSV format. Then open the CSV file in any text editor or viewer and you will notice the empty rows (the ones containing only separator characters) if there were any. You can remove them in the Excel file by selecting the rows by their headers and then remove them. The other and more roboust solution is if you allow the destination table's columns to contain nulls and deal with them after the data is imported.
more ▼

answered Nov 12, 2010 at 07:07 AM

dillinzser gravatar image

dillinzser
91 1 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x991
x346

asked: Nov 11, 2010 at 05:31 PM

Seen: 8151 times

Last Updated: Nov 11, 2010 at 05:31 PM