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.

Ossiferous

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.

Unqualified

I found that the first solution you offered doesn't work. I did have great success with the second solution offered. I put a row below my headers and included 0.00 in the numeric columns. Overwriting didn't work, but I included the row as part of the named range and hid that row leaving only the header row showing. The numbers came in properly. THANKS TONS! – Acquiesce

The second solution works; however, it skips a row, which is annoying but not the end of the world. – Heartily

Answer 2

SQL Server, by default number formatting is 27376.00. If you have your locale different from this format Windows, does not work.

copy example: 2,7376.00 " Your windows format neeed: "2.7376,00"

In my case, change the Regional Settings Spanish Spain to Spanish Mexico. It worked.

Rifle

Answer 3

I would add a row with template data and formatting and hide that row. That worked for me. When the import occurs, it will copy the first (hidden) row formatting.

Lorentz

Answer 4

Just add a temporary record at the first line (2nd if you set the First row has column names) with temporary values with the format you want. Excel sets the first data line or the first data as the field type.

Suggestion

Answer 5

Below are 3 different solutions for 3 different glitches :
Value 1
1234
blank cell
1234
Solution 2
=IIF(IsNumeric(Fields!TEST.Value.ToString()), CDBL(IIF(IsNumeric(Fields!TEST.Value.ToString()), Fields!TEST.Value.ToString(), "0")), " ")

Value 2

1234
Null
345
null
Solution 2
=IIF(IsNumeric(Fields!TEST.Value.ToString()), CDBL(IIF(IsNumeric(Fields!TEST.Value.ToString()), Fields!TEST.Value.ToString(), "0")), "NULL")

Value 3

1234
Null
345
N/A
550
TBA
440
599
NOT APPLICABLE
Solution 3
=IIF(IsNumeric(Fields!TEST.Value.ToString()), CDBL(IIF(IsNumeric(Fields!TEST.Value.ToString()), Fields!TEST.Value.ToString(), "0")), Fields!TEST.Value.ToString()),

Manualmanubrium

Please format your answer with markdown – Grapnel