This blog has been moved to: http://sqlblog.com/blogs/jorg_klein/
This blog post has been moved to: http://sqlblog.com/blogs/jorg_klein/archive/2009/11/18/ssis-let-the-excel-connection-manager-pick-the-right-column-data-types-from-an-excel-source.aspx
Pingback from Twitter Trackbacks for SSIS - Let the Excel connection manager pick the right column data types from an Excel source - Jorg Klein's [sqlblogcasts.com] on Topsy.com
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.
I much prefer leaving Excel's guesswork out of it, and using IMEX=1. Full details of that on Dougbert's blog: dougbert.com/.../default.aspx
Hi Todd,
Thanks for your comment! Very useful.
I did not know 16 was the max so I will add that to the blog post, thanks for the addition!
If I read the note on Microsoft Support about the IMEX=1 option it seems the TypeGuessRows property is still important. IMEX=1 only seems to affect the way Excel determines the data type, or am I missing something here?
This is the note from support.microsoft.com/.../194124 (got there by a link on Dougbert's blog):
"NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the data type. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default data type to Text; it will remain numeric. "
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.