Importing Excel XLS to SQL SSIS error
Posted by Dan Israel on Mar 2, 2010 in Information & Technology
While trying to execute an SSIS import package on a customer’s Excel file, SQL Server 2008 SP1 was returning a “truncated” error. Although, the SSIS package was set to ignore these, the execution halted and data was not written to the SQL table.There were a number of posts including
David Klein’s and
Waxtadpoles regarding SQL 2005 and the “TypeGuessRows” setting in the registry. Their suggestion was to set the value to 0 (zero), in order to force SQL to parse all rows in the excel sheet, not the first 8 (
http://support.microsoft.com/kb/189897)This was ultimately the solution, but not as stated. For us, possibly because we were running 64bit or differences in SQL 2008/Server 2008, the registry entry was not where either the Microsoft Bulletin or the two posts above stated. By searching the registry for this Dword Value we found several other instances of it at:
- HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftJet4.0EnginesExcel
- HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftJet4.0EnginesLotus
After making the change to “0”, our error was resolved. Thanks to both the posters above, and Philip Michael’s persistence.
UPDATEDIt turns out this is indeed related to the 64bit OS. Apparently the Jet Engine runs in “Windows On Windows 64” 32 bit run-time. Here is a little information on
WoW64. And David Jennaway has some more information on SQL 64bit, Excel providers and DLLs in this article:
The Registry settings for the 32 bit OLEDB provider for Excel have moved.
