Lowell (3/19/2013)
i think it has nothing to do with SQL, but rather the ACE drivers that are used to access via Excel;the ACE driver does the peek at the first 100 rows, and does a best-guess datatype casting of the data base on that;
that's the registry setting you can tweak to make it peek at more rows.
so it sounds like your issues' have more to do with that.
if the actual raw data is CSV, then i would take the ACE /excel drivers out of the equasion and use a text driver , whether via SSIS bcp or bulk insert to get the data instead.
To put the dot on the i:
The ACE driver looks by default to the first 8 rows. You can set the TypeGuessRows registry setting to any number between 0 and 16. The value of 0 makes the driver scan the first 16384 rows. However, this is useless until you add IMEX=1 to the end of the connection string, otherwise you'll end up with NULL data if your column has mixed data types.
Shameless plug with more info:
What’s the deal with Excel & SSIS?
Bottomline:
convert to a flat file 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP