A community member has associated this post with a similar question:
SSIS - how to export csv to display long number instead of display 13.4+3E

Only moderators can edit this content.

SSIS - export to csv file but can't show the long number correctly (text column)

nononame2021 256

my SSIS is to output the CSV file. but i found that my data is stored in SQL server is nvarchar(50). how can i display the value in CSV correctly.

the actual data stored is: 44011164011111100
but it show in excel contains special character as :

my column has the following data now. the first one can't show correctly if it contain too many numeric.

nononame2021 256 Reputation points

2022-06-20T16:26:13.94+00:00

anyone know can I format the output column to text in derived column, if yes, what should I set to display long integer property in csv file type?

4 answers

ZoeHui-MSFT 32,821 Reputation points

2022-06-20T08:35:04.307+00:00

Hi @nononame2021 ,

It should be related with the cell format.

Edit the csv file and right-click format cells and choose number.

Or you may use Data Conversion to convert the nvarchar to DT_DECIMAL and then load to the csv.

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
nononame2021 256 Reputation points

2022-06-20T08:39:48.08+00:00

from your suggestion, my column possiable value are listed in my post 12345678(4) and 666666666666-000
is it good to convert to DT_DECIMAL for all my possible value? or other data type is suit for my case?

Or you may use Data Conversion to convert the nvarchar to DT_DECIMAL and then load to the csv."

finally, i causes error when i convert to DT_DECIMAL, any other workaround for this?

[Data Conversion [2]] Error: Data conversion failed while converting column "pre_no" (105) to column "Copy of pre_no" (19). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

ZoeHui-MSFT 32,821 Reputation points

2022-06-20T08:49:23.187+00:00

No, you cannot do that if the column contains other symbols. Use the first method or just open the file with notepad.

nononame2021 256 Reputation points

2022-06-20T08:53:06.453+00:00

can't use the first method, as every time need to convert to another column type, it is not a good solution.

anyone can help for this?

nononame2021 256 Reputation points

2022-06-20T16:27:51.613+00:00

should I covert data type in derived column to show long integer?

do uou know what data type should be set in derived column ?
nononame2021 256 Reputation points

2022-06-20T08:57:16.24+00:00

how to format to special in SSIS

below is csv setting.
Please sign in to rate this answer.
ZoeHui-MSFT 32,821 Reputation points

2022-06-20T09:06:56.513+00:00

SSIS could not do that. Choose number to format the whole column manually. You do not need to convert it everytime you load the data, it will keep the number format in the csv file once you have formatted.

nononame2021 256 Reputation points

2022-06-20T13:10:09.513+00:00

I have a csv template to copy to csv destination before exporting to csv. but I can't format the csv template in advance, how can I format the whole column in csv template?

I try to format the whole column to numeric or special in csv and save, and then I re-open the csv, it become general in cell format dialog.
Olaf Helper 40,816 Reputation points

2022-06-20T09:21:00.987+00:00

but it show in excel contains special character as

Has nothing to do with SSIS and your export.
That's the way how Excel represent large number, as exponential value / scientific notation, see
https://en.wikipedia.org/wiki/Scientific_notation
Please sign in to rate this answer.

0 comments No comments
nononame2021 256 Reputation points

2022-06-20T09:27:49.523+00:00

or how can i output to format the cell when it contain long number
Please sign in to rate this answer.

0 comments No comments