August 3, 2009 at 1:29 pm
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?
August 4, 2009 at 7:52 am
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
Consultant - Straight Path Solutions
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
August 4, 2009 at 7:55 am
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.
May 1, 2011 at 7:58 am
Two steps involved in this:
1. Convert DT_NTEXT to DT_WSTR
2. Convert DT_WSTR to DT_STR
Thats it.
December 8, 2011 at 11:03 pm
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.
February 9, 2012 at 1:37 pm
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...
September 9, 2012 at 9:53 pm
Would you mind to advise how to convert it?
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.
January 30, 2013 at 1:31 am
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?!
February 17, 2014 at 10:51 pm
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 !!
February 18, 2014 at 12:04 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 20, 2015 at 9:50 am
I hope this link works when loading into excel from oledb source
December 20, 2015 at 9:53 am
I Hope this links when working with excel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply