Excel upload data type choose problem in SSIS package

Excel check only first 8 rows to define the datatype. The parameter TypeGuessRows=8 is set in registry. Use 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 columns data type. Set this value to 0 to scan all rows. This will hurt performance in ongoing ETL processes.

To override of problem, we need to set the property in Excel Task of 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 set in SSIS Package for override the excel problem in Properties of upload task.

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