Excel format problem in SSIS package

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″;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.