SSIS - how to export csv to display long number instead of display 13.4+3E

asked 2022-06-20T10:21:07.467+00:00
nononame2021 246 Reputation points

i am preparing SSIS to export csv, however, when i export long number, it can't display correct upon i format the column to number or special in csv manually, it is very inconvenience.

anyone know how to resolve it in SSIS? the column data type in SQL server is set as nvarchar(50). it contain data are list as below:

1234566666666666
12345678(4)
11111111111111-111

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,481 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,841 questions
1 vote

8 answers

Sort by: Most helpful
  1. answered 2022-06-20T18:34:52.19+00:00
    Yitzhak Khabinsky 19,851 Reputation points

    Hi @nononame2021 ,

    As @Olaf Helper already pointed out:

    You can add a apostrophe in front to force Excel to show it as text instead of a numeric value.

    To do that you need to use SSIS Derived Column Transformation.

    SSIS Derived Column Transformation Expression: "'" + <yourColumnName>

    Tutorial on it: derived-column-transformation-in-ssis


  2. answered 2022-06-20T10:53:48.883+00:00
    nononame2021 246 Reputation points

    can it set in sql script , ssis or somewhere to display long number in csv properly.

    6666666666776 and 12345678(4) can display property without set format manually

    No comments

  3. answered 2022-06-20T12:17:03.937+00:00
    Olaf Helper 25,556 Reputation points

    it can't display correct upon i format the column to number

    Display where?
    If it's MS Excel as in your other tread
    https://learn.microsoft.com/en-us/answers/questions/895439/ssis-export-to-csv-file-but-can39t-show-the-number.html
    then it's an Excel issue, nothing SSIS related.
    You can add a apostrophe in front to force Excel to show it as text instead of a numeric value.


  4. answered 2022-06-20T13:16:22.273+00:00
    nononame2021 246 Reputation points

    if I change to excel xlsx file, can I make the long numeric to display property?


  5. answered 2022-06-20T16:12:13.907+00:00
    nononame2021 246 Reputation points

    anyone knows if I set to xlsx instead of csv, how to set the cell format to numeric or text in order to show the value of 123455555555555 and 12345678(4)

    No comments