Excel Connection in SSIS

While trying to load Excel files, by default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. So, cases where the first 8-10 rows have empty data, the data type selected by SSIS may not be correct and we may face problems with uploading/transforming the data in these columns.

We can resolve this issue by adding "IMEX=1; MAXROWSTOSCAN=0" to the excel connection string extended properties. A typical excel connection string after adding the above properties will look as follows:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1; MAXROWSTOSCAN=0";