How to avoid 'number stored as text' error when exporting data from SQL server to excel (SSIS)

Question

This is what I am trying to do in the SSIS package:

1. Copy blank excel sheet with headers as new file
2. Run a data flow task populate data in the sheet.
3. When a open the excel, all numeric data is shown with 'number stored as text' error.

This stops all my charts from working. I would like to know how can I inform excel to treat the particular column data numeric.

Answer 1

One possible solution that I haven't tested is to convert the numeric fields to either DT_R8 or DT_CY. The Excel destination only appears to support these numeric data types and the SSIS destination is probably implicitly converting all other data types to text.

Another possible solution is to add numeric values to the numeric columns so that it knows to format the destination as numeric in your template sheet. You can either hide the values or overwrite them with the new data that you are posting to the file.

Personally, if I know I need to export to Excel then I use a SSRS report and run the report subscription by using a sp_start_job for the schedule job. The import and export to Excel features in SSIS are pretty bad since they rely on the Jet 4.0 engine.