Good afternoon
Trying 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. Thanks
for any inputs.
Excel Destination Error: Column"xx" cannot convert between unicode and non-unicode string data types
Hot Topic
- Random Failure of Lookup in Pre-Execute with 0x8007000E
- how to conditionally get the data
- SSIS Flat file connection manager not seeing carriage return-line feed
- SQL Server 2005 JDBC Driver 1.0 error in using XA datasource
- Inifinite Click-Through for SQL Server 2005 Report Server
- How can I run SQL 9.0
- Database Master Key Error
- SQL Server 2005 Developer Edition and SQL Server 2005 Express Edition on same machine?
- Data source and data set confusion
- What is the best way for users to view cubes?
SQL Server
- Securing the MS SQL Server 2000 data files
- Can you use Report Builder with SQL Server 2000 and Visual Studio 2003?
- compatability level
- Newbie: sql 2005 encryption --- or where do I put that key?
- task manager
- Reporting Services Subscriptions - Help!
- rda localconnection
- Trying to install SQL Server 2005 Express Edition
- Is this a silly way to use SSIS?
- Permission problem
Excel Destination Error: Column"xx" cannot convert between unicode and non-unicode string data types
Jack Stone
Yep. Or a derived column transformation using a cast function...
(DT_WSTR,255)[column] will convert non-unicode to unicode.
Ram1979
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,
Karthikeya Pavan Kumar .B
Yanbiao Zhao - MSFT
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
Adi.Condor
seth j hersh
Firas Saltaji
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
Benjamin123
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.
Tejas_v
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 the
package and work on it to extend the Data Conversion Transformation.
Fridtjof
Richard_T
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.
MDB51
Youtham
-------------------------------
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!
SanjeevUpd
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.
Abyers
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:
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
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:
Ravi KP
I used the Data Conversion Transformation tool and the mapping as above as descrived also and the problem stopped.