SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excel

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS DT_NTEXT to DT_STR conversion failure...

SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excel

Rate Topic

Display Mode

Topic Options

Author

Message

Question Guy

Posted Monday, August 03, 2009 1:29 PM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 65, Visits: 587

Just google "derived column dt_ntext to dt_str not working" and you will find numerous cases where people have difficuilties importing Excel sheets into SQL 2005.

Basically, I have an Excel sheet with a comments field that I need to import into SQL 2005. The length could be up to 4000 char(don't ask), and therefore it is read as a data type DT_NTEXT. I need it to be DT_STR so that it can be saved into a varchar field in SQL. I have not been able to get the Data Conversion Task nor the Derived Column task to work. It seems to pretty much ingnore the end conversion attempt....
In the derived column I have this expression:
(DT_STR,4000,1252)(DT_WSTR,4000)CommentsCopy where CommentsCopy is a copied column of my Comments field which is of type DT_NTEXT. The name of my derived column is CommentsConv. The error I get is, "CommentsConv" and "Comments" cannot convert between unicode and non-unicode string data types. Well, why not? I see other posts that use this syntax and they don't have a problem? Any advice out there?

Post #764336

Jack Corbett

Posted Tuesday, August 04, 2009 7:52 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

What are CommentsConv and Comments? Are you attempting to do this all in one derived column transform?

Why not do one derived column transform to DT_WSTR and then a second to DT_STR?

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #764803

Question Guy

Posted Tuesday, August 04, 2009 7:55 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 65, Visits: 587

I found that the error was occuring in a place I didn't expect. In the Excel Source task, the Input and Output Properties did not have matching data types. I had to set them both to Unicode text stream [DT_NTEXT]. Not sure why the default values didn't match when I tried redoing this a couple times but that was the main cause. I then just used a derived column with (DT_STR, 4000, 1252)Comments and it worked. I also tried using two Data Conversion Tasks. That also worked, BUT, where I got stuck on that is, when mapping fields to the final OLE Destination, I had to map the original Comments field to the database Comments field...I would have expected that I should use the CommentsConv field that I created in the Data Conversion Task. But if I mapped what I thought was the converted CommentsConv field, it would give an error. How odd.

Post #764809

sudhanva

Posted Sunday, May 01, 2011 7:58 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, October 05, 2012 3:23 AM
Points: 79, Visits: 232

Two steps involved in this:

1. Convert DT_NTEXT to DT_WSTR
2. Convert DT_WSTR to DT_STR

Thats it.

Post #1101296

johnnyk

Posted Thursday, December 08, 2011 11:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, August 20, 2012 11:53 AM
Points: 36, Visits: 207

I have an issue at hand right now similar to this but its the other way around.

I have an stored procedure which uses an openquery to query a linked server and brings a result set with one of the columns with string data more than 8000 characters. My target is to load this result set into an excel sheet (.xlsx)

The dev server is running ace12.0 driver and ssis 2008 r2. I used an execute sql task to create a table in the excel sheet with all the other columns as varchar(50) except the column that holds the large character data which is created with the datatype as memo since i did read somewhere that memo in excel maps to dt_ntext in ssis.

Now, when i use as OLE DB Source task to run the stored procedure and then check the output column datatype of the large data column returned by the stored procedure, it is dt_text.

I used a data conversion task to convert all the other columns into dt_wstr (unicode) and the large column into dt_ntext(unicode), i mapped the columns to the columns in the table that i created in the excel sheet using an excel destination task.

It gives me this strange error that i am just finding really hard to understand .. it is as following:
"Error: 0xC0202025 at Data Flow Task, Excel Destination [185]: Cannot create an OLE DB accessor. Verify that the column metadata is valid."

i can provide additional information if you need any

any help will be extraordinarily appreciated.

Post #1219068

getoffmyfoot

Posted Thursday, February 09, 2012 1:37 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 1:09 PM
Points: 335, Visits: 391

The solution to your problem, johnnyk, is the same. Convert the DT_TEXT field to a DT_STR. If the field is unicode, convert the DT_NTEXT to DT_WSTR, then convert the DT_WSTR to DT_STR.

Hope that helps...

Post #1249936

Jimmy.Liew

Posted Sunday, September 09, 2012 9:53 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:50 AM
Points: 26, Visits: 308

Would you mind to advise how to convert it?

Regards,

Jimmy

Post #1356546

Mr Quillz

Posted Wednesday, January 30, 2013 1:31 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:09 AM
Points: 60, Visits: 175

Good day Guys,

The solution of converting from DT_NTEXT to DT_WSTR AND from DT_WSTR to DT_STR will work. As long as tet being converted does not exceed DT_STR 8000 characters. Which is the problem that I'm getting. Sad

Any work around that issue?!

Post #1413507

« Prev Topic | Next Topic »

Permissions