Issue with Excel Destination character size in SSIS

Question

I am trying to export data from a database table in SQL server 19.1 version to a Excel file using SSIS 2019 excel destination task. One of the fields 'External Notes' coming from SQL database is VARCHAR(5000). I can export everything to excel but the 'External Notes' field size in excel is restricted to unicode 255 and no matter what I try it does not allow me to export the data over 255 characters.

I tried to change SQL field as nvarchar(max) or varchar(max). Althought the max keyword was working fine in SSIS 2014 sql task. This is the error message I get in log file when I execute the SSIS.

Error: 2024-05-14 12:03:40.25

Code: 0xC002F210

Source: Execute SQL Task 2 Execute SQL Task

Description: Executing the query "CREATE TABLE Detail (Provider Full Name NVARCH..." failed with the following error: "Syntax error in field definition.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

End Error

Also tried changing the datatype in excel destination task advanced editor and data conversion task to dt_ntext or dt_text after making it External Notes varchar(255) in the query . The error message that I get-

Error: 2024-05-13 09:35:34.29

Code: 0xC0202009

Source: Data Flow Task Excel Destination [120]

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

End Error

Error: 2024-05-13 09:35:34.29

Code: 0xC002F445

Source: Data Flow Task Excel Destination [120]

Description: An error occurred while setting up a binding for the "External Notes" column. The binding status was "DT_NTEXT".

End Error

Also tried changing the excel version in excel connection manager from excel 97-2003 to 2007-2010 and 2013. But got a different error as mentioned below-

Error: 2024-05-14 06:48:36.25

Code: 0xC0209302

Source: SSIS package Connection manager "Excel Connection Manager"

Description: The requested OLE DB provider Microsoft.ACE.OLEDB.15.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

End Error

But none of attempts worked. Could anybody please help me how can I keep using ``` external notes` `` nvarchar(max) in my sql query in the ssis sql task and still get the the complete data of that field in the xls zip file generated at the last step by ssis package?

Answer 1

coming from SQL database is VARCHAR(5000).

JET / ACE / Excel export supports a maximum size of char 255 and there is no way around.

Answer 2

Hi @Dhanvi Bansal,

What about run the query in SSMS and then save the result to csv file.

And then you may open with excel and save it.

In SSIS, there will be a limit of 255 characters.

Regards，

Zoe Hui

---

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