Numeric gets converted to Text in Excel from SSIS

This is with reference to a question asked in MSDN forum where a User was trying to fill an Excel sheet with one the Numeric Column and field was changing to Text. 

OR

SSIS Excel Data Source: Text was truncated or one or more characters had no match in the target code page

 

This happens as Excel identifies data type for a column based on sampling of top few rows(8 by default) and in that sample it doeskin get correct data type.

 

 Problem:

Lets see the problem with an example I have a simple table with following structure
CREATE TABLE Emp
( ID INT IDENTITY (1,1)
, [Name] VARCHAR (10)
, Salary NUMERIC (10,2)
);

Here ID and Salary are Integer and Numeric receptively and if we try to export them to Excel using SSIS, Excel will store the correct values but would convert them into Text as below( see the Green mark over cell)

Unforunatly there is no way that we can control or change  the format/ datatype of a column of Excel sheet form SSIS.Even if we change the datatype using Advance Editor in Excel Destination , it wont reflect the change.

Solution:
So is there is solution to this, well not excatly a solution but a workaround Yes.
When Excel decide about format of cells in a column it takes sample from first few cells (other than first row-header) and keeps the same format for rest of cells down below. We can use this property of Excel and create one dummy row with the format as we are excepting for rest of the cells.

Now during actual writing of data Excel will pick the format from Dummy Row and keep it for rest of them. You can even hide Dummy Row after putting some values in it.

OR

We can increase the sampling Rows for Excel and it can guess Correct Data Type based on given Range of Rows. Sample Range for Rows can be increased from Registry Key TypeGuessRows
 

Path in Registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel 

For 64 bit machines it may be under
 HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel 

You can set any values to this editable field
a) Default: 8
b) 0 will take all the rows in the Sheet -This may slow down validation if no. of rows are large.
c) Recommend is to give a value where you can be sure of having correct data type rows.




Cheers!!