sql server 2008 - Text was truncated or one or more characters had no match in the target code page When importing from Excel file

Question

I have an excel file with four text columns: one of them is called ShortDescription which has the longest value. I created a table in SQL Server 2008 database, with four columns and the ShortDescription column type is set to NvarChar(Max).

but when using the SSIS import and export dialog, I keep getting the mentioned error in the title, even when I set the OnTruncation option to Ignore.

I tried to clear the column data, and it succeeded (so I made sure that the problem is in the ShortDescription column). I tried to copy the whole data to another excel work book, and still no luck.

any ideas ???

Answer 1

I assume you're trying to import this using an Excel Source in the SSIS dialog?

If so, the problem is probably that SSIS samples some number of rows at the beginning of your spreadsheet when it creates the Excel source. If on the [ShortDescription] column it doesn't notice anything too large, it will default to a 255 character text column.

So to import data from a column that contains rows with large amounts of data without truncation, there are two options:

1. You must make sure that the [ShortDescription] column in at least one of the sampled rows contains a value longer than 255 characters. One way of doing this is using the REPT() function, e.g. =REPT('z', 4000), which will create a string of 4000 of the letter 'z'.
2. You must increase the number of rows sampled by the Jet Excel driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel (of if your system is x64 then under the HKEY_LOCAL_MACHINESOFTWAREwow6432nodeMicrosoftJet4.0EnginesExcel) registry key.

You can see more information at these two links:

• http://waxtadpole.wordpress.com/2008/04/28/hello-world/
• http://technet.microsoft.com/en-us/library/ms141683.aspx

To further explain, SSIS creates 3 objects behind the scenes of the wizard, an Excel data source object, a SQL table destination object, and a data flow operator between them. The Excel source object defines the source data and exists independent of the other two objects. So when it's created, the sampling I described is done and the source column size is set. So by the time the data flow operator executes and tries to pull the data from excel to put in your table, it's already looking at a data source that's been limited to 255 characters.