Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Question Guy
Question Guy
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 763
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?
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12176 Visits: 14861
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
Question Guy
Question Guy
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 763
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.
sudhanva
sudhanva
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 236
Two steps involved in this:

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

Thats it.
John Reddy
John Reddy
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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.
getoffmyfoot
getoffmyfoot
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 412
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...
MonkeyDBA
MonkeyDBA
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 381
Would you mind to advise how to convert it?

Regards,

monkeyDBA
We don't grow when things are easy; We grow when we face challenges.
Mr Quillz
Mr Quillz
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 240
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?!
wondermorris8
wondermorris8
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 346
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 !!
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18445 Visits: 13247
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search