Sending DT_NTEXT data to Excel Destination in SSIS


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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aaron ShiloChief Database ArchitectCommented:
you should try using a DataConversion trasformation
irb56Author Commented:
I already use a data conversion, which sets the column in question to DT_NTEXT. It doesn't make any difference. The problem is that the Excel destination has decided the column I want to map to is DT_WSTR and nothing I do will change this. I've tried putting long text (over 255 characters) into the first 10 rows of the two columns of the Excel worksheet in question, then hiding those rows and recreating the Excel connector and destination again, but it makes no difference at all. The external columns of the Excel destination remain as DT_WSTR with length 255.

I'm going to try a different approach by creating the Excel file using Execute SQL tasks that use a CREATE TABLE statement and set the column data types in question to Longtext (as per Books Online). Hopefully this will work although it will sacrifice the benefit I get from the template approach (i.e. pre-setting the column widths to aid readability of the Excel worksheet post data input). I'll post an update with my findings in due course.
Alpesh PatelAssistant ConsultantCommented:

Convert it to string using conversion task. and amp the converted column with destination column
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

irb56Author Commented:
I have just recently achieved success by using an Execute SQL task to create the Excel workbook and worksheet. The following statement in this task will create a worksheet:

(`id` nvarchar(255),
`amount` float,
`description` longtext)

When I do a data flow into the Excel destination that contains the worksheet named "MyReport" that was created by the aforementioned Execute SQL task, the metadata of the Excel external column is set to DT_NTEXT, which enables the mapping and data to flow in properly.

However, this method will only work with xls files (2003 and earlier), not with xslx. When I try it with xlsx, the metadata still stubbornly persists as DT_WSTR with length of 255. Is it only the older versions of Excel that recognise the longtext data type? Does anyone know of a different data type to specify in the Create Table statement that Excel 2007 will recognise as the SSIS database DT_NTEXT?
To irb56:
You sir are a real lifesaver.
You have not just resolved the issue but spoke about the most important tiny detail that could be easily overlooked (atleast i did) when you look at the error from ssis. THE FILE TYPE !!.
Guess what .. I was already doing everything that you had mentioned in your comments. All the data conversion and derived data column tasks. NOTHING WORKED!!
I had the exact same issue. Browsed the whole internet for a day looking for the answer and you have exactly and i must say precisely pointed out the issue.
The issue was the excel file extension itself. I was using an .xlsx file. Damn thing always used to revert back to DT_WSTR when i was using it in the excel destination task.

Here is what i was doing first:
i had an empty .xlsx file that i am using as an template. I am creating the target table for data load from the ole db source within the package itself. I was using the longtext datatype for the column which will hold the huge data string but still the package used to throw me an error of [Excel Destination [91]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

I checked the datatype (external, in/out) .. even at the excel destination task. I corrected the datatype for the external column in excel destination task but then as soon as i exit the DFT, it was getting reversed. i had no idea why .. NTEXT maps to MEMO so i even tried to create the excel column with the datatype memo. Still No Go!!

Then i read your post. It was indeed the excel sheet itself.

I will try and find the answer for the question that you had. Why .xlsx acts wierd with the input DT_NTEXT value.

Thanks a million for the wonderful tip.
irb56Author Commented:
Hi John,

I'm pleased to hear you found this thread useful, and thanks for the nice comments. If you happen to find an answer to the question of DT_NTEXT columns in a SSIS load of an xlsx file, please share. SSIS is a bit limited I think in respect to working with Excel files, which I find a little surprising considering it's all Microsoft technology.


I am working on a solution for this issue with the incompatibility for .xlsx extension. I agree with you on the datatype incompatibility. i was beginning to wonder if this has to do anything with the type of driver installed on the server. I currently have the ACE 12.0 64-bit installed on the server and the excel .xslx file that i created was from Office 2010 on my workstation. I am not sure if the ACE 12.0 only works with Office 2007 files and not with Office 2010. May be or May be not. But what i am sure about is when using an Office 2003 .xls file, the datatype concern does not surface even if i am forcing the driver to be used in my package as an ACE 12.0. I also came across suggestions from other sources for manually inserting a large text possibly more than 10000 characters in the first row and in the column that will be holding the large text but this is not a standard approach. A workaround - Yes but not a standard approach since it is highly unreliable and not professional. I will try to continue to research into this and will post my findings. Hopefully microsoft will consider about the concern of product interoperability between its products when supporting interproduct accessibility better going forward.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
irb56Author Commented:
Forced to close question before I can ask any more!
Seems like a silly problem, but for anyone working with Excel 2007 files and SQL Server 2012 BI, putting a bunch of text in the first row of your sheet, and then hiding that row forces the integration package to load the column metadata as dt_ntext instead of dt_wstr(255).  I guess this never used to work, but worked fine for me.  Seems like a silly workaround still, not very intuitive or obvious, but at least it works.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.