Ecxel Destination - nvarchar(max)

Johannes Hohmann 1 Reputation point
2021-09-20T15:35:35.86+00:00

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
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,586 Reputation points
    2021-09-22T06:58:04.163+00:00

    Hi @Johannes Hohmann ,

    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

    0 comments