SSIS incorrect datatype at Excel destination from SQL database 2005

I'm hoping someone can help me, I've explored google to death and I just can't seem to find the answer. I'm new to using SSIS, so I don't know if this is possible...I am exporting data from SQL server database to an Excel file, which works well but it exports all numeric as string. I then have to go in a manually convert it to numbers. The source of the data is definitely numeric but SSIS recognises the destination cells as UNICODE STRING. I've tried changing the format of the cells in the excel template and tried to use the impor开发者_JAVA技巧t export wizard but to of no avail. Is there something I am missing?

I use an Excute SQl task to crete the excel file and define the columns and datatypes. Use a connection type of EXCEL and then a create table statment to create the tab in the excel file. See example below:

CREATE TABLE `MyExcelFile` (
     `FIRST_NAME` NVARCHAR(100),
    `MIDDLE_NAME` NVARCHAR(50),
    `LAST_NAME` NVARCHAR(255),
    `BUSINESS_NAME` NVARCHAR(255),
    `ADDR1` NVARCHAR(200),
    `ADDR2` NVARCHAR(200),
    `ADDR3` NVARCHAR(200),
    `CITY` NVARCHAR(100),
    `STATE` NVARCHAR(50),
    `COUNTRY_CD` NVARCHAR(2),
    `ZIP` NVARCHAR(50) 
     `TOTAL_COUNT` INT,
    `TOTAL_AMOUNT` NUMERIC (10,2),
) 

The trick is to get SSIS to see that you want the Excel data to be numeric. Try this:

1. format the cells in the sheet and save.
2. edit the SSIS Excel destination
3. in the Mappings tab hover your mouse cursor over the destination column that is supposed to be numeric. Look at the tool tip to see if it's showing a numeric data type.