We help IT Professionals succeed at work.
Get Started

Sending DT_NTEXT data to Excel Destination in SSIS

11,795 Views
Last Modified: 2013-09-06
Hi,

I need to output some string data from a couple of columns from a SQL Server OLE DB source into an Excel destination, where the source data type is nvarchar(max). I am using the template approach for the Excel destination, copying across the template and then using a SSIS data flow to pump the data in. My problem is that the external column metadata of the two columns defaults to DT_WSTR with length of 255. The trouble is that I am unable to change the external data type to DT_NTEXT. I try to set this via the Advanced Editor, exit the data flow but the change doesn't stick and it just goes back to DT_WSTR.

In the mappings of the Excel Destination, there is a mapping of DT_NTEXT from the input column (from the data flow pipeline) to DT_WSTR in the Excel external column. Consequently when I run the data flow it fails immediately on a validation error.

I understand from Microsoft that Excel only supports the following SSIS data types:
Numeric      double-precision float (DT_R8)
Currency      currency (DT_CY)
Boolean      Boolean (DT_BOOL)
Date/time      datetime (DT_DATE)
String      Unicode string, length 255 (DT_WSTR)
Memo      Unicode text stream (DT_NTEXT)

What I can't understand is how to make the two external columns be DT_NTEXT instead of DT_WSTR. Why does it ignore my change made via the Advanced Editor. Is there some other step required to ensure the column mapping is DT_NTEXT to DT_NTEXT?

Many thanks in advance.

Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE