Excel Destination Error: Column"xx" cannot convert between unicode and non-unicode string data types
Good afternoonTrying out the Excel Destination in SSIS, I ran into the above error. I did try to ignore my 2 varchar fields and then it works fine. Thanksfor any inputs.
July 29th, 2005 12:08am

Yes, string fields in Excel are treated as Unicode string fields by the driver. Here is some content written about moving data in the other direction that applies here too:Data types. The Excel driver uses only six data types, which Integration Services maps as follows: Numeric double-precision float (DT_R8) Currency currency (DT_CY) Boolean Boolean (DT_BOOL) Date/time date (DT_DATE) String Unicode string, length 255 (DT_WSTR) Memo Unicode text stream (DT_NTEXT) Data type and length conversions. The Excel driver recognizes only a limited set of data types, as listed above. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services does not implicitly convert data types. As a result, you may need to use Derived Column or Data Conversion transformations to convert Excel data explicitly before loading it into non-Excel destinations. In this case, it may be useful to create the initial package by using the Import and Export Wizard, which configures the necessary conversions for you. Some examples of the conversions that may be required include the following: From Unicode Excel string columns to non-Unicode destination string columns with specific codepages From 255-character Excel string columns to shorter destination string columns From double-precision Excel numeric columns to integer destination columns
Free Windows Admin Tool Kit Click here and download it now
July 29th, 2005 1:18am

You can try by using casting at the query level, which will make the source column as unicode string, then the transformation will be successful, Select Cast(xx as nvarchar(50)) xx from <tablename> Hope this helps,
January 6th, 2006 7:51am

I get this error when I use the Export Data wizard when exporting table data into an Excel-Sheet.Is it a bug?EDIT: Just to be a bit more explanatory: The wizard automatically converts a memo field into DT_NTEXT but the string is not been converted into DT_WSTR, so you have to save thepackage and work on it to extend the Data Conversion Transformation.Fridtjof
Free Windows Admin Tool Kit Click here and download it now
February 13th, 2006 12:18pm

I don't quite understand how to resolve this situation. I am encountering the same issue and I've passed the source through the data conversion transformation. This does not resolve the situation, so I am thinking I need to pass this through the derived column transformation as well, but I'm not sure on how to perform this action.Is this even necessary? Someone help please.
February 16th, 2006 5:38pm

I have the same error trying to import an Access database with a memo field type. SQL Server 2005 SSIS can't convert the memo to a varchar and gives the unicode error. Odd.
Free Windows Admin Tool Kit Click here and download it now
February 20th, 2006 10:28pm

Are you using the Data Conversion Transformation Tool? If you are, then make sure you go into the SQL Destination Editor to change the mappings. The Conversion tool adds new source fields - it does not replace the fields for which it is converting.
March 6th, 2006 10:01pm

I used the Data Conversion Transformation tool and the mapping as above as descrivedalso and the problem stopped.
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2007 1:20am

Leo H wrote:I used the Data Conversion Transformation tool and the mapping as above as descrived also and the problem stopped. Yep. Or a derived column transformation using a cast function...(DT_WSTR,255)[column] will convert non-unicode to unicode.
January 20th, 2007 1:27am

Here is a duplicate of a post I did on another topic. This should work for all the above problems. Cheers.-------------------------------Katrina, you are a goddess among men. For anyone out there who still has this problem, Katrina has led me to the solution. I was still a little confused when I read it, so here are the exact steps I took to change it. I was loading from a flat text file. In the edit page of the file connection manager (not flat file source), go to the advanced tabs to see the columns. Here you can set the load type for the columns (I wanted non-unicode, so I chose String). Then you connect to (let's say) an OLE DB Destination. If you double click to open the connection, you can choose "metadata" and see the types are correct. Right click on the OLE DB Destination, and choose "Show Advanced Editor". Then click on the "Input and Output Properties" tab. Expand "OLE DB Destination Input" and you will see two folders ("External Columns" and "Input Columns"). If you open "Input Columns" and choose one of your columns, you will see the correct data type (ie. DT_STR). Now, if you expand the "External Columns" folder and choose one of your columns, you will see that it thinks an incorrect data type is coming in (ie. "Unicode string [DT_WSTR]"). This is what is causing your error. This makes the package think that it needs to implicitly convert what it THINKS is coming in (DT_WSTR) to what it wants (DT_STR), which it refuses to do. In the "External Columns" folder, change all of your data types to the correct incoming types, and all your worries will float away. To aid in the discussion on many forums about why this is a problem, this is most definitely a Microsoft error. When you connect the source to the destination, the connection should force the destination to update its input types. Again, thanks to Katrina for leading the way!
Free Windows Admin Tool Kit Click here and download it now
February 1st, 2007 3:14am

Tim, I am trying to perform the flat file to sql server table and I keep getting the conversion error. I have tried to follow your steps but I am still getting the error. Once you edited the file connection manager did you still need to do a data conversion step? I see what you mean by the external files showing as DT_WSTR and I change them to dt_STR but for some reason it does not keep. Any suggestions? Greg
February 22nd, 2007 9:25pm

I too am having an issue with the file connection keeping the changes I made to the Destination columns. Any suggestions on how to keep it from changing back to string DT_STR? FYI: I am using an excel file but I also have a tab delimited flat file of the same extract if I could get that to work either.
Free Windows Admin Tool Kit Click here and download it now
March 7th, 2007 7:43pm

Hi guys, This problem happens usually when the source or destination is an Excel file. I don't concidered this a bug at all(just more work to set it up) What you need is a DataConversion object. So use a DataConversion (from Data Transformation toolset) between your source and destination and changed the incomming datatypes from DT-STR to DT_WSTR(non unicode) for text fields The best way of seeing this is to use the Import/Export Wizard and let SSIS do the job for you then you can see the DataConversion objects,... Good Luck
March 16th, 2007 7:32pm

Azita -- The DataConversion object worked great. Excel files always seem to demand more. Thank you very much for the hint.seth j hersh
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2007 4:56pm

I'm jumping into this thread rather late, but I've had an experience with this problem that might be helpful to others. I've been trying to dump SQL query results into an EXCEL spreadsheet using SSIS, and that's when I first got this error message. After reading through the entries in this thread (and several others), I thought that the most direct method would be to edit the SQL Task that contained the query so that it would convert all of the columns I was returning to NVARCHAR. However, that didn't work, which was really frustrating. I tried doing the conversion in a number of other places, always getting the same error (and the same thing happened when I tried to dump the results in an Access table as well). I finally discovered that if I edited the query to return NVARCHAR (as described above), copied and saved the query to a safe place, then deleted the SQL Task from the package, then created a new SQL task and pasted in the very same code that I had just copied, it would then work perfectly. It seems as though modifications made to tasks that have already been saved are not always properly retained, and I assume this to be some sort of VS2005 problem. I had a similar problem modifying existing parameters in Reporting Services, by the way -- there were cases where I changed properties of a parameter, but those changes didn't make their way properly into the .rdl file, and I had to either delete and recreate the parameter or edit the .rdl directly. So, I think there are some nooks and crannies of the development environment that aren't quite ready for prime time.
May 4th, 2007 1:03am

You can use Data Conversion option in between DB Source and Excel Destination Data Flow
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2007 2:54pm

HI Tim Rupe, your post solved my problem . Thnx a lot to you and Karina too.. Katrina, Step by Step Explanation is really helpful. Take care Thnx Ram.
May 31st, 2007 5:20pm

Using TheData Conversion transformation did resolve the error .But .. Do we have to use the conversion for every varchar/Char columnI have in my source table to move it into Excel ? Assuming that I have more than 20 - 30 columns in my source which are varchar fields .. do i have to manually go about changing each and every field ? Or is there any option out there?
Free Windows Admin Tool Kit Click here and download it now
June 25th, 2007 1:09pm

Using TheData Conversion transformation did resolve the error .But .. Do we have to use the conversion for every varchar/Char columnI have in my source table to move it into Excel ? Assuming that I have more than 20 - 30 columns in my source which are varchar fields .. do i have to manually go about changing each and every field ? Or is there any option out there?
June 25th, 2007 1:36pm

blah, blah, blah, blah...all you've got to do is import the data into SQL Server, into a new table.The wizard creates the table for you. Then go into the new table andadjust the datatypes however you want. Then write a small queryor copy the data from the new table into the existing one.
Free Windows Admin Tool Kit Click here and download it now
July 17th, 2007 9:48pm

Hi, In SSIS, I am getting the same error while trying to import an ODBC datasource (driver given by the vendor) into sql server. I tried writing Cast function in the input data source query. But the Query gave errors. Can anyone give some suggestions for me to fix this ? The below is the error i get Error at Data Flow Task [OLE DB Command [248]]: Columns "Change_ID_" and "@SRCvchChangeID" cannot convert between unicode and non-unicode string data types. Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB Command" (248)" failed validation and returned validation status "VS_ISBROKEN". Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration)
December 3rd, 2007 7:47pm

Hi, I ran into the same problem, tried different things (the step by step)and than realized that when I create the columns not with varchar but with nvarchar ... problem is gone. So in the preparation task I do a scripting like: -- recreate extr_xls_Objektfamilienif object_id('[extr_xls_Objektfamilien]') is not nulldrop table [extr_xls_Objektfamilien] CREATE TABLE [extr_xls_Objektfamilien] ([pkobjektgruppe] nvarchar (255) NULL, [objektgruppe] nvarchar (255) NULL, [pkobjektfamilie] nvarchar (255) NULL, [objektfamilie] nvarchar (255) NULL )GO hth Michael
Free Windows Admin Tool Kit Click here and download it now
April 16th, 2008 4:34pm

Anonymous_money said: blah, blah, blah, blah...all you've got to do is import the data into SQL Server, into a new table.The wizard creates the table for you. Then go into the new table andadjust the datatypes however you want. Then write a small queryor copy the data from the new table into the existing one.Yes,I have no problem doing this.ButSSIS seems to recognise2 String types onlyfrom Excel: Any text column in Excel with value less than 255 characters is a Unicode string (DT_WSTR) Any text column in Excel with value greater than 255 characters is a Unicode text stream (DT_NTEXT)It doesn't do me any good when the number of characters in the columns flutuates between 255 to 300. That means, the original data type defined in the SSIS package works for the spreadsheet with columns ofcharacters < 255 will not work for the spreadsheet with columns of characters > 255.Is it possible to use SQL query statement to source from an Excel source in SSIS?physics is phun ...
March 16th, 2009 8:53am

Once again leave it to Microsoft to make our lives difficult. I'm sick of this ____. I'm going tohave to spend months converting dts to ssis.
Free Windows Admin Tool Kit Click here and download it now
April 2nd, 2009 6:22pm

Hi kcj63 What about using DTSxChange as recommended by Microsoft above their own conversion tools? SQL Server Developer Center > SQL Server Forums > SQL Server Integration Services > DTS Conversion Tool - DTS xChange http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e3226dbb-dd1e-4738-9aad-b490d9b4b1b0 DTS xChange Product Overview https://partner.microsoft.com/global/program/competencies/40053735Explore this presentation to learn more about the benefits of Data Transformation Services (DTS) xChange, which can help you migrate tasks from SQL Server 2000 to SQL Server 2005. Includes information about SQL Server Integration Services (SSIS). Cheers Alan
August 4th, 2009 3:36pm

THANKS, Doug L -- That finally explains why a VARCHAR column in a CREATE TABLE (for an Excel destination) still shows up as a DT_WSTR column!!! ((With a database designed with all VARCHAR (no NVARCHAR), this means alot of extra work in Data Flows.)) My question: Is this improved in SSIS 2008?1) Does SSIS 2008 support non-Unicode strings, in Excel destinations? OR:2) Does SSIS 2008 have an automatic (or bulk) way to convert non-Unicode to Unicode? THANKS again, -- Doug I.LovisMirac@aol.com
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2010 9:33pm

I'm jumping into this thread rather late, but I've had an experience with this problem that might be helpful to others. I've been trying to dump SQL query results into an EXCEL spreadsheet using SSIS, and that's when I first got this error message. After reading through the entries in this thread (and several others), I thought that the most direct method would be to edit the SQL Task that contained the query so that it would convert all of the columns I was returning to NVARCHAR. However, that didn't work, which was really frustrating. I tried doing the conversion in a number of other places, always getting the same error (and the same thing happened when I tried to dump the results in an Access table as well). I finally discovered that if I edited the query to return NVARCHAR (as described above), copied and saved the query to a safe place, then deleted the SQL Task from the package, then created a new SQL task and pasted in the very same code that I had just copied, it would then work perfectly. It seems as though modifications made to tasks that have already been saved are not always properly retained, and I assume this to be some sort of VS2005 problem. I had a similar problem modifying existing parameters in Reporting Services, by the way -- there were cases where I changed properties of a parameter, but those changes didn't make their way properly into the .rdl file, and I had to either delete and recreate the parameter or edit the .rdl directly. So, I think there are some nooks and crannies of the development environment that aren't quite ready for prime time. What you did is exactly what I ended up doing: putting CONVERT(NVARCHAR... in place, in the t-sql, for EVERY COLUMN... because our databases are all non-unicode...... and then, similar to what you said, deleting and recreating data flow tasks altogether. Here's what's NEW: It looks like Microsoft is going to FIX (or maybe by this time HAS fixed) sql server 2008, to automatically convert non-unicode columns to unicode. Check out the link below, at Microsoft's feedback site: https://connect.microsoft.com/SQLServer/feedback/details/337679/ssis-cannot-convert-unicode-to-non-unicode?wa=wsignin1.0#tabs What's NOT new:Microsoft has not offered a fix for SSIS in SQL Server 2005, to automatically convert unicode to non-unicode. So, please: USE THE FEEDBACK SITE TO GIVE FEEDBACK!!!!!1) Visit the link above, and VOTE UP FOR THE TOPIC.2) If you're up for it, add a comment of your own, requesting the 2005 fix.THANKS!
April 7th, 2010 2:47am

Recreating those elements using VS2008 components seems to address the issue - I no longer get that "cannot convert between unicode and non-unicode" nonsense. As painful as it sounds, I have to agree with Doug Ivison. In my case, the issue must have been originated by the conversion from a VS2005 SSIS package to VS2008 SSIS. The conversion was successful as the initial VS2008 wizard indicated, but remnants of VS2005 conversion issues lingered in the background. Manual conversion (creating everything manually from scratch) was my solution. It's lousy, but it seems to be working. Dominik
Free Windows Admin Tool Kit Click here and download it now
June 3rd, 2010 4:39am

I was able to recreate the same issue in slightly different circumstances. This time, as I kept adding Execute SQL Task Control Flow Items, I ended up copying a SQL command from VS2008 9.0.30729.1 SP1 sql editor window into one SSIS element within VS2005 SQL Server Business Intelligence Development Studio. After saving my changes in that VS2005 SSIS solution, I could no longer execute the package due to and error: Column"xx" cannot convert between unicode and non-unicode string data types. To resolve the issue I had to do the following: 1. Removed Execute SQL Task item with the query pasted from VS2008 studio 2. Removed Execute SQL Task items which pulled information from the table populated by that troublesome SQL task 3. Deleted the OLE DB Connection used by that copied query 4. Saved and closed the solution 5. Reopened the solution, recreated OLE DB Connection, recreated deleted SQL tasks 6. Saved changes and executed the packege successfuly wihtout unicode conversion issues aymoreDominik
June 14th, 2010 11:44pm

Data Conversion Transformation seems optimum solution. I tried different ways but this one works perfectly. When you are ready to load the final data into Excel, use Data Conversion Transformation and change data type from DT_STR to DT_WSTR of respective columns. It will create copies of those columns. In Excel Destination Task when you perform mapping, just map these new copies of columns to the final columns in excel so it will take only those data whose data type has been changed during Data Conversion. This will solve the unicode error.
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2011 8:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics