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

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

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.
12,481 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,437 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,535 questions
{count} vote

8 answers

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

    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

    1 person found this answer helpful.

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

    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

    0 comments No comments

  3. Olaf Helper 40,156 Reputation points
    2022-06-20T12:17:03.937+00:00

    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. nononame2021 256 Reputation points
    2022-06-20T13:16:22.273+00:00

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


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

    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)

    0 comments No comments