I am developping  a SSIS package with VS2013 to send data from SQL Server 2014 to an Excel Destination. But in the SSIS package, from the excel destination advanced editor, when I set the format of the excel destination external columns to double precision float DT_R8, it is returned to DT_WSTR automatically.

Due to that, data sent to Excel are not processed as numeric but as text and formatted as such. I need the column to be created as numeric.

Thank you for you

The Excel conn string must include IMEX=1 which puts it into data ingress mode.

Visit http://blog.concentra.co.uk/2013/05/15/why-ssis-always-gets-excel-data-types-wrong-and-how-to-fix-it/ for more info or help.

Hi Patrick,

Based on your description, the issue is that when you set the format of the excel destination external columns to double precision float DT_R8, it is returned to DT_WSTR automatically. So you cannot load the column data with numeric data type in Excel.

After testing the issue in my environment, the issues is caused by the data type of external columns is decided by the column data type in Excel, so you cannot change them successfully.

To fix this issue, please refer to the following tips:

• If the destination table already contains rows of data, the existing data should be numeric. Because Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR).
• If the destination table is created during package design or at run time, then the CREATE TABLE statement must use LONG (or one of its synonyms) as the data type of the columns.

Thanks,
Katherine Xiong

I tried to modify the connection string as you mentionned, but problem was not solved.

Thank you for your help. The second tip correpond to my situation. But The CREATE TABLE statement already include Numeric (10,2) for all numeric fields. 

Hi Patrick,

If the CREATE TABLE statement already use Numeric (10,2) as the data type of the numeric columns, then the issue should be resolved. One reason is that you use a excel file with Microsoft Excel 2007 or later version with .xlsx extension.

To avoid this issue, please use a .xls excel with Microsoft Excel 97-2003 version instead.

Thanks,
Katherine Xiong