already in  our Production system, Type guess rows registry =8, all package are running in production and Not using IMEX=1 in Excel connection Provider.

Now my problem is:

Now I newly developed a SSIS package moving to production by changing Type guess rows registry =0 in Reg edit.and Excel Connection with

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

Now Is Type guess rows registry =0 any affectto other SSIS Packages??

my guess is Problem METADATA issue will arise?

So please give clarification ??

Thanks

TypeGuessRows property is set to 8 by default. When package is executed it notices the first 8 row values and determine the data type.

E.g. In first 8 rows if you have data of length less than 255 and if after 8th row you have data more than 255 length it will be truncated.

Now you have set TypeGuesRows to 0 so SSIS will not determine the data type by observing first 8 rows.

To avoid truncation issues, you should set TypGuess rows to 0 in production as well.

If you can't, then make sure that your first 8 rows have correct data so that SSIS will assign correct data type.

Setting the TypeGuessRows value to zero (0) will result in checking the first 16384 rows in excel to determine the data type instead of checking only the first 8 rows. It will do this for all packages that use excel (and other applications using the same provider)

Without IMEX=1 the majority within those x rows will determine the datatype. With IMEX=1 the datatype will become string if there are mixed datatypes withing those first x rows.

So if you check the first 8 rows with imex=1 and the first 8 rows are numeric, but the rest is mixed then the data type becomes numeric, but you will get NULL values for all string in row8 and higher. Summarized: try to avoid Excel as source in SSIS :-)

Type guess rows = 0 means any excel it will scan through entire set of rows in all excel files. So any previous vs future ssis pkgs will also have behavior different while parsing Excel files

Type guess rows = 0 means any excel it will scan through entire set of rows in all excel files.

Apparently it is only 16384 rows (at least for jet): "The valid range of values for the TypeGuessRowskey is 0 to 16. However,if the value is 0, the number of source rows scanned is16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large."

Thank you for all,

I understood as per above replies :

1.Meta Data Truncation

2.Data Type

3.Different Behaviour

4.Small Performance

So it should level of REGEDIT so it will effect Entire  System Environment.

means before Package development We need this details :::Type Guess Row: 8/0

Thanks

Madhu