question

JohannesHohmann-0162 avatar image
0 Votes"
JohannesHohmann-0162 asked Zoehui-MSFT answered

Ecxel Destination - nvarchar(max)

Hi all,

Your assistance is needed. There is a problem that I have not been able to resolve.
Here is the situation:
- A table named "Notes" in SQL Server with a field named Text and a data type of nvarchar(max)
- The longest text in this field is approximately 5000 characters.
- I would like to transfer this field to an "Excel Destination" - an import template file.

My current situation is as follows:
As I build the flow, the "Excel Desintation Input -> External columns" are limited to DT_WSTR and 255 characters and I cannot change the datatype (it is overwritten automatically).
As soon as I paste 5000 characters into the first column of the excel-template file, they become "unicode text stream [DT_NTEXT]" as datatype (which I prefer).
Despite reading a lot about the limitation of excel of 255 characters, it does not make logical sense for me if I use the "workaround" with the "dummy record" and then it works.
Is there a way to solve this kind of problem?


sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JohannesHohmann-0162,
Welcome to Q&A forum!
According to your description, your issue seems to be more related to SQL Server, I will remove the tag office-excel-itpro that focuses on general issues about Excel client.
Hope you can get a reply soon. Thanks for your understanding.

0 Votes 0 ·

1 Answer

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @JohannesHohmann-0162,

Here is a workaround for you that you may transfer the data to CSV.

Use a flat file destination and edit Flat File Connection Manager>>Tick 'Unicode', OK.

134197-screenshot-2021-09-22-145604.jpg

Hope it could be helpful.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

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.
Hot issues October


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.