There are various problems with importing data from Excel files.
Today I came across an article from 2012 by Koen VerBeeck which has been re-blogged on the SQLServerCentral.com website which very succinctly summarises the problems and solutions:
Please note my comment at the bottom which I will repeat here:
One thing I would add is that if you are in a situation where changing registry settings is difficult or you don’t want to have to bother with changing settings every time you move machines/re-image machines i.e. you have to stick to the default of 8 rows being sampled. Then just add 8 dummy rows to the beginning of your Excel tables with the relevant type of data inserted – bunch of ‘A’s for strings, ‘9’s for numeric etc. – and then delete all the dummy records out with conditional split as mentioned. With this setup you can be certain the SSIS routine will work on any machine with default JET settings.
