Excel source data types

When loading Excel files using SSIS you may find that text columns are truncated at 255 characters, irrespective of the contents of the column. This happens when the Excel ODBC driver infers the maximum width of the column – by default it does this by inspecting only the top 8 rows.

The number of rows that the driver inspects is specified in registry key TypeGuessRows. You can increase this value as required, or set it to 0 to force the driver to inspect the full file (but bear in mind that this may increase the SSIS load time for large spreadsheets, because you're reading the file twice – once to obtain type information and once to import it).

The location of TypeGuessRows varies between systems:

Excel 1997 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
Excel 2000+ HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
32-bit Excel on 64-bit systems HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\ Engines\Excel
Microsoft.ACE.OLEDB.12.0 driver HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines