SQL Server and Excel spreadsheet data import issue using SSIS

0
votes

I am using SSDT for Visual Studio 2013 and SQL Server 2014. I am working on importing data from Excel spreadsheets to SQL Server tables, using Excel source and OLE DB destination. I get this String or binary data would be truncated. error on OLE DB destination. I checked everything and I don't have VARCHAR fields over 255 and using IMEX = 1 in the Excel connection string. I tried to import one field at a time for test and still get the same error and not sure column that is.

SSIS Error:

[OLE DB Destination [28]] Error: 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: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "String or binary data would be truncated.".

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (28) failed with error code 0xC0202009 while processing input "OLE DB 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.

1
sql-serverexcelssis
What you see and what you get are not always the same with Excel, export the data to a text file, import it back to a new Excel file. make sure that not only you do not truncate text columns but also that your numeric rescission is correct or greater on the destination. And as always when using Excel... good luck. This is why I avoid it as the plague and always substitute with text files. – Ricardo C
I have had to force it to work before by just setting the fields to 3000 or something huge then do a couple length commands to find the data length and try again. – Wes Palmer
Somewhere in your Excel file is a field that is too long for the column in the database table. There is no easy built-in way to find it. Just trial and error. – Tab Alleman
Whenever I have imports from files, I have a staging table that is much more broad than my target table. I use the staging table to import without error and use the ETL between the staging table and the target table to clean the data. Particularly with Excel, I have issues with hidden characters. – Andrew O'Brien

1 Answers

0
votes

If you import one field at a time, the field that errors out should be the column with the problem. What I have experienced is the excel file has quotes ("") or a special character in a field and is combining two fields as one when loaded into the SQL table. So I save the excel file as CSV and import the CSV.