Ever had SSIS fail when loading an excel spread sheet? This could be the cause (and solution)

If you ever have problems loading EXCEL based data into SQL via SSIS, you are not alone.  It can cause all kinds of problems with data type selection for the columns.  The real problem is not with how SSIS selects the data types for all the columns, but actually how far down a spread sheet that SSIS scans to process the data.

I think of this as a deliberate Microsoft bug (or a feature, as they like to call it).

I am going to suggest to you that you change an excel setting to force your PCs to scan ahead more rows.  However, a word of caution – this involves a change to your PC registry.   If you are not confident doing this, grab somebody from your IT support team to help you change it.

Ready – here we go….

SSIS – Let the Excel connection manager pick the right column data types from an Excel source

The excel connection manager scans every first xx rows to determine the data type for a column in your SSIS source component. Depending on your version of Excel, this may be 8, 12 or 20!!!!   So if an Excel sheet column has integers on the first 8 rows and a string value on the 9th row, your data flow task will crash when executed because SSIS expects integers.

Fortunately you can change the number of rows that Excel will scan with the TypeGuessRows registry property.

How to Change TypeGuessRows:

1. Start Registry Editor by typing “regedit” in the run bar of the Start menu.

2. Search the register (CTRL-F) on “TypeGuessRows”.

3. Double click “TypeGuessRows” and edit the value.

4) Now, change to DECIMAL and type a new number of rows.  I am going to suggest to you to put “0” (zero) – this means “scan to the end of the work sheet”.   It adds a very very slight delay to the processing of SSIS, but you will thank me later.

5) Repeat the search (press F3 for “Find next”), and replace any occurrences found (there may be 2 or 3 on your PC).

6) Once all occurances have been changed, just exit from the registry settings.