THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Jorg Klein, Microsoft-only BI consultant from the Netherlands

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

The excel connection manager scans every first 8 rows to determine the data type for a column in your SSIS source component. 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.

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".
Search

3. Double click "TypeGuessRows" and edit the value.
Edit

Todd McDermid (MVP) commented the following useful addition:
"Unfortunately, that reg key only allows values from 1 to 16 - yes, you can only increase the number of rows Excel will "sample" to 16."

Robbert Visscher commented:
"The reg key also allows the value 0. When this value is set, the excel connection manager scans every row to determine the data type for a column in your SSIS source component."

Thanks Robbert, I think setting it to 0 can be very powerful in some scenario's!
 

So the conclusion of the comments of Todd and Robbert is that a value from 0 to 16 is possible:
  • TypeGuessRows 0: All rows will be scanned. This might hurt performance, so only use it when necessary.
  • TypeGuessRows 1-16: A value between 1 and 16 is the default range for this reg key, use this in normal scenario's.
Published Wednesday, November 18, 2009 12:01 AM by jorg
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jcridge said:

Does anyone know, in a loop to read many Excel files, does the excel connection manager RESCAN the first N rows for each file it opens

OR does it set the data types based only on the first file it finds ?

October 15, 2012 8:35 AM
 

Sven Aelterman said:

Thanks for sharing this.

Setting the value to 0 actually still only scans the first 16384 rows, which in some scenarios can still be a limitation (as discovered by http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6496b806-c0d9-4ab7-b309-aa34550aaa1d/ole-db-connection-error-failed-to-retrieve-long-data-for-column?forum=sqlintegrationservices&prof=required).

We should encourage Microsoft (through Connect) to just accept the fact that the developer knows best and if we specify a certain data type for a column to accept that's what it is.

February 1, 2014 7:22 PM
 

Sven Aelterman said:

@jcridge: it will rescan for every file it opens.

February 1, 2014 7:23 PM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft-only BI consultant from the Netherlands, blogging about BI on SQL Server with a focus on SSIS.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement