Often by importing data from a file Excel happen to receive from SQL SERVER 2005 the following error:
Error 0xc020901c: Data Flow Task: There was an error with output column “Nome Campo” (63) on output “Excel Source Output” (9). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”
Il Jet Driver by default it checks only the first 8 columns to guess the maximum size of the rows. So if your first 8 columns are smaller than 255 characters and column 9 has more characters SQL Server returns the above error.
The solution is to go to the Configuration register Windows and follow Microsoft's instructions:
To change the value of TypeGuessRows, use these steps:
- On the menu, click . In the dialog box, type , and then click .HomeRunRunRegedt32OK
- Open the following key in the Registry editor:HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
Notes For 64-bit systems, the corresponding key is as follows:
HKLMSOFTWAREwow6432nodemicrosoftjet4.0enginesexcel - Double-click TypeGuessRows.
- In the DWORD editor dialog box, click Decimal for Base. Type a value between 0 and 16, inclusive, for Value data.
- click OK, and then exit the Registry Editor.
A second way to work around this problem (without modifying the registry) is to make sure that rows with fields, which have data 255 characters or greater, are present in the first 8 rows of the source data file.
The valid range of values for the TypeGuessRows key is from 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero could cause a small performance hit if the source file is very large.
