asked
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] (Error) 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)
• 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.
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.
@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.
@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.
Try adding IMEX=1 to your connection string for the EXCEL connection.
http://support.microsoft.com/kb/194124
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.
