Cannot convert between unicode and non-unicode string data type

HI.

I'm having this problem.

I use Visual Studio's, integration project to load XML file into SQL Server. In the XML file, i have defined collumns as string. When i try to load XML file with parts defined in scheme as string, i get an error "cannot convert between unicode and non-unicode string data type.

Destinated collumns in SQL are defined as varchar and char.

Thanks for help

I had the same problem and I put a Data Conversation object to convert the fields from XML to string [ST_STR] and everything works fine.

Hope this helps
Paulo Aboim Pinto
Odivelas - Portugal

|||Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.
|||

pkv wrote:

Thank you. You saved me lot of work today. O have bypassed this problem with querry in SQL Server, but this solution is better. Thanks again.

Please mark your post as answered if you've solved your own issue. It helps us to filter out which posts still require attention.|||Post from another topic that might help...
-

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!
|||

Tim/Katrina, that all sounds great. But when I change the External Columns to DT_STR, it doesn't save my changes!!!! I'll go right back into my OLE DB Destination, and whamo, it changes it back to DT_WSTR.

Any ideas?

I'm running:

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

|||

Hi..

Set ValidateExternalMetata option of the Destination False from True, and change types of external columns in Advnaced Editor.

Related Article and sample packages (written by Korean..Sorry!!)

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005SSIS&intSeq=807

test user_id and password : test / test

HTH

|||

OH MY GOD THANKS FOR POINTING THIS VALIDATEEXTERNALMETADATA OUT I HAVE SPENT HOURS FIDDLING WITH SETTINGS AND FINALLY IT WORKS!!!!!

But it totally sucks beyond belief that SSIS is so full of gotchas like this.