Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excel Expand / Collapse
Author
Message
Posted Monday, August 3, 2009 1:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:09 PM
Points: 72, Visits: 686
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
Posted Tuesday, August 4, 2009 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 11,213, Visits: 12,969
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
Posted Tuesday, August 4, 2009 7:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:09 PM
Points: 72, Visits: 686
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
Posted Sunday, May 1, 2011 7:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 5, 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
Posted Thursday, December 8, 2011 11:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 4, 2013 12:36 AM
Points: 36, Visits: 212
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
Posted Thursday, February 9, 2012 1:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:36 AM
Points: 340, Visits: 406
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
Posted Sunday, September 9, 2012 9:53 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 7:08 AM
Points: 27, Visits: 329
Would you mind to advise how to convert it?

Regards,

Jimmy
Post #1356546
Posted Wednesday, January 30, 2013 1:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:53 AM
Points: 60, Visits: 202
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.

Any work around that issue?!

Post #1413507
Posted Monday, February 17, 2014 10:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:13 AM
Points: 2, Visits: 195
can you pl guide how to convert from the DT_NTEXT to DT_WSTR, then convert the DT_WSTR to DT_STR.

I am trying to connect share point lists to SQL tables. I have data conversion in data flow tab. But unable to figure out how to perform this double conversion on same column.

Thank a lot !!


Post #1542371
Posted Tuesday, February 18, 2014 12:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 13,550, Visits: 10,423
DT_(N)TEXT is a CLOB format. In order to convert it to a regular string, you need to use a script component.
Google for ssis script component convert blob to string and you will surely find some examples.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1542390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse