asked on

How to export VARCHAR(MAX) data from Ado.net source to Excel Destination .xlsx?

SSIS Package, How to export data from Ado.net Source to Excel Destination (.xlsx)?
I have Varchar(MAX) in Sql Server. But when I use the ADO.net source field datatype changes to DT_NTEXT.

Data not exported to excel

Excel Destination Editor: Create Table 'Sheet1'('ID 'INT, 'Comment 'NTEXT)
Getting the Error: Excel Destination An OLEDb error is occured. An error is due to binding for 'Comment' column, The binding status was DT_NTEXT. Cannot create an OLEDB Accessor.

lcohan

As is documented in the Excel specifications and limits here https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 the maximum characters that can be stored in a single Excel cell is 32,767 characters therefor your data exceeding 32,767 characters would be truncated. You might be better off trying to export to a CSV instea if your data exceeds 32,767 characters. You can check that by running a SQL query like below against your database table:

SELECT MAX(LEN(VarCharMAX_ColumnName)) from TableName;

Open in new window

You can also have a look here https://blog.sqlauthority.com/2014/02/05/sql-server-retrieve-all-the-data-from-varcharmax-column/ at how to get all data from a SQL Server VARCHAR(MAX) column into a file.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.