7

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.

Improve this question

5 Answers 5

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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!
The second solution works; however, it skips a row, which is annoying but not the end of the world.
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

-1

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()),

Improve this answer

1 Comment

Please format your answer with markdown

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.