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

Question

I have SSIS package that takes data from OLE DB source and loads to an Excel. The Field in the SQL table is nvarchar(max) and the data conversion I put for it when it goes into the excel is DT NTEXT. But I still keep getting this error. I would appreciate any help on this.

Excel Destination [140]: An error occurred while setting up a binding for the "Value_Comments" column. The binding status was "DT_NTEXT". Error: 0xC0202025 at Data Flow Task, Excel Destination [140]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: Excel Destination failed the pre-execute phase and returned error code 0xC0202025.

Answer 1

You want to use DT_WSTR with an nvarchar(max) column. DT_NTEXT is for the now-deprecated ntext data type. If you need to be able to output unicode character data longer than 4000 characters that DT_WSTR is limited to (IIRC) then I would cast the column to an ntext column in your query, but I believe you will lose some SSIS functionality when you do that.

See the SSIS data types documentation here.