SSIS Data Flow OLE DB To Excel Nvarchar Size Issue

Question

Hopefully, this is not an ignorant question as I am still working to build SSIS Skills.

I Have a package that takes an excel sheet and loads it into an SSMS SQL table so that I can run analysis and update statements to the data. I am now looking to load that SQL table back into an Excel sheet. I have made an excel sheet as a template of a replication of the SQL table.

The issue I am now having is I have a field named "Comment" that datatype is Nvarchar(MAX) in my SQL table. This column does contain NULL values as well. When I am trying to load these back to the Excel column I am having an error.

[Excel Destination [28]] Error: An error occurred while setting up a binding for the "Comment" column. The binding status was "DT_NTEXT".

I thought perhaps I could do a Data Conversion to a string with the max character (Which is 757) but it truncates and errors on that size.

This data came from an excel column so I would think I can load it back to a column.

Thanks for the help!!

Answer 1

Previously I thought that Excel does not allow exporting data with longer than 255 characters. After running several experiments, exporting DT_NTEXT values to Excel can be done using SSIS:

You should create an Excel file with one dummy row that contains long text values (> 255) then use this Excel as a destination. If the Excel contains previous data, make sure to add this dummy row directly after the file header and add ;IMEX=1 to the OLE DB connectionstring.