Excel makes datatype according to the first few rows to overcome the use of IMEX or TYPEGUESSROWS in the SSIS package

Excel checks only the first 8 rows to define the datatype. The parameter TypeGuessRows=8 is set in the registry. The IMEX=1 option might cause the IMEX feature to set in after just 8 rows.

Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD “TypeGuessRows”. That’s the key to not letting Excel use only the first 8 rows to guess the column’s data type. Set this value to 0 to scan all rows. This will hurt performance in ongoing ETL processes.

To override the problem, we need to set the property in the Excel Task of the SSIS Package. So, Only this ETL package will scan complete excel for uploading data.

IMEX parameter and HDR parameter means

IMEX=1 option might cause the IMEX feature to set in after just 8 rows.
IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

“HDR=Yes;” indicates that the first row contains column names, not data.
“HDR=No;” indicates the first column as data.

Following is the format need to be set in the SSIS Package to override the excel problem in the Properties of the upload task.

Standard Format:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
Extended Properties=”Excel 8.0;HDR=Yes;IMEX=1″;