I ran into an issue the other day while importing data from an Excel file into SQL Server 2005. I had columns that contained fields that have more than 255 characters. When running the import, SSIS failed because it kept truncating the fields which caused the task to fail. It fails because the Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column. To fix this, you need to go into the registry and modify the ‘TypeGuessRows’ to scan more than 8 rows. The key can be found in the following:
- Excel 97
- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
- Excel 2000 and later versions
- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel