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_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
- HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Lotus
After making the change to “0″, our error was resolved. Thanks to both the posters above, and Philip Michael’s persistence.
UPDATED
It 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.