SQLServerCentral.com / Integration Services / Data Warehousing / SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excel / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 16:54:26 GMT20RE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxDT_(N)TEXT is a CLOB format. In order to convert it to a regular string, you need to use a script component.Google for [i]ssis script component convert blob to string[/i] and you will surely find some examples.Tue, 18 Feb 2014 00:04:12 GMTKoen VerbeeckRE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxcan 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 !!Mon, 17 Feb 2014 22:51:57 GMTwondermorris8RE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxGood 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?!Wed, 30 Jan 2013 01:31:29 GMTMr QuillzRE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxWould you mind to advise how to convert it?Sun, 09 Sep 2012 21:53:36 GMTJimmy.LiewRE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxThe 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...Thu, 09 Feb 2012 13:37:05 GMTgetoffmyfootRE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxI 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 anyany help will be extraordinarily appreciated.Thu, 08 Dec 2011 23:03:15 GMTjohnnykRE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxTwo steps involved in this:1. Convert DT_NTEXT to DT_WSTR2. Convert DT_WSTR to DT_STRThats it.Sun, 01 May 2011 07:58:28 GMTsudhanvaRE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxI 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.Tue, 04 Aug 2009 07:55:42 GMTQuestion GuyRE: SSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxWhat 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?Tue, 04 Aug 2009 07:52:50 GMT Jack CorbettSSIS DT_NTEXT to DT_STR conversion failure unicode and non-unicode Excelhttp://www.sqlservercentral.com/Forums/Topic764336-364-1.aspxJust 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?Mon, 03 Aug 2009 13:29:29 GMTQuestion Guy