I have been working this issue and the best solution is ask your DBA to drop the table and recreate it.. The destination data column.. I just resolved this issue... Or check and make sure you use Varchar only in your destn column

From http://www.developmentnow.com/g/103_...data-types.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

I don't know about you, but adding data conversion objects, and selecting each field...
.... or doing "CONVERT(NVARCHAR..." in the SQL...
.... is really adding up!!!

This simple, common part of ETL used to be almost instantaneous.... and now it takes time away EVERY time a layout change happens... which can be repeatedly throughout the day, in a common ad hoc project.

From the link below, it looks like Microsoft will be producing a fix for this in 2008... but they haven't (YET) offered one for SSIS in SQL Server 2005.

So, PLEASE go to the link below at their feedback site,
vote for the thread,
and comment, asking for a fix to automatically convert non-unicode to unicode in 2005 !!!

https://connect.microsoft.com/SQLSer...signin1.0#tabs

THANKS!




cmell wrote:

cannot convert between unicode and non-unicode data types
27-Sep-07

I am converting a simple (at least in the SQL Server 2000 DTS days) to this
new SSIS. I have a data source (which is Oracle 8 using .Net ODBC provider)
and am trying to pump the data into a SQL Server table. I get an error that
it cannot convert between unicode and non-unicode data types. Any user
wrote in that basically the same I am just far more a beginner.
I am in the data transformation task as suggested and I confused as to the
drop down to be selected, in fact I have used all the string values. In SSIS
I am new so is there a "rebuild" or do the changes I made in the
transformation stay operative when I run it if so I am confused because the
error message stays the same
cindy

Previous Posts In This Thread:

On Thursday, September 27, 2007 1:46 PM
cmell wrote:

cannot convert between unicode and non-unicode data types
I am converting a simple (at least in the SQL Server 2000 DTS days) to this
new SSIS. I have a data source (which is Oracle 8 using .Net ODBC provider)
and am trying to pump the data into a SQL Server table. I get an error that
it cannot convert between unicode and non-unicode data types. Any user
wrote in that basically the same I am just far more a beginner.
I am in the data transformation task as suggested and I confused as to the
drop down to be selected, in fact I have used all the string values. In SSIS
I am new so is there a "rebuild" or do the changes I made in the
transformation stay operative when I run it if so I am confused because the
error message stays the same
cindy

On Thursday, September 27, 2007 3:12 PM
Paul Shapiro wrote:

You can use a Data Conversion transformation.
You can use a Data Conversion transformation. Add it between your data
source and the destination.

On Thursday, September 27, 2007 6:56 PM
cmell wrote:

Yes, numerous times I have inserted the transformation task between the oracle
Yes, numerous times I have inserted the transformation task between the
oracle and the sql database. I have choosen from the drop down list dstr and
the code page goes to ascii latin and executed there is very no change in the
error message. I have changed the datatype on the sql column to nchar, to
nvarchar also I must be missing a step and I am going on 8 hrs today with it.
Tried dumping to a raw file but the output is still unicode when I try to
import from file to sql table. Specifically after oledb connection to oracle
and pull data with query I insert data transformation I have six columns that
come back with error message In the data conversion interface I select
string(dt_str) with code page 1252
then I execute and same error message. I have 4 new packages now all with
the same one control task only, to pull in the data with a conversion.
--
cindy


"Paul Shapiro" wrote:

On Monday, October 01, 2007 1:18 PM
nic wrote:

Exactly the same problem.
Exactly the same problem.

It's a bit rubbish that Microsoft product speaking to Microsoft
product can't actually produce a meaningful error message.

It doesn't really help that SSIS feels the need to produce a separate
set of definition strings - I know that my output is Char(9) since I
created it that way. Should that be DT_STR or not? It looks like it
should, but it's be nice if the helpfile was showing the
correlation.


On Sep 27, 11:56 pm, cindy <cme... (AT) nospam (DOT) nospam> wrote:

On Monday, October 01, 2007 2:11 PM
cmell wrote:

Mine is also Char 9 in Oracle, using the data transformation of dstr does not
Mine is also Char 9 in Oracle, using the data transformation of dstr does not
change the error message I am trying to go into a nvarchar on Sql and never
in the older model was it so difficult.
--
cindy


"nick (AT) pheasnt (DOT) demon.co.uk" wrote:

On Tuesday, October 02, 2007 6:48 AM
jhofmey wrote:

Re: cannot convert between unicode and non-unicode data types
On Oct 1, 7:11 pm, cindy <cme... (AT) nospam (DOT) nospam> wrote:

Hi Cindy and n...

Cindy: It sounds from your description like you are trying to retrieve
a non-unicode (ASCII) column from Oracle (Char(9)) and insert it into
a Unicode column in SQL Server 2005 (NChar(9)?). From your
description of the steps you have taken so far, you mention selecting
DT_STRING with various code pages etc. If this is the case, the
problem is because you are still selecting ASCII string types. To
convert to Unicode, you need to select the DT_WSTR option for Unicode
String.

n...: I can understand your frustration! Microsoft states that the
reason for implementing such tight data typing in SSIS is to maximise
performance by allowing the package to grab exactly the right
resources required to perform the desired tasks. Compared to DTS2000
this does mean you need to put more thought and effort into your
package design/build/testing. Having said that, after over 2 years of
using SSIS, I must say that the functionality and performance
achievable put it far above DTS as a viable ETL tool.

Good luck and stick with it!
J

On Tuesday, October 02, 2007 12:20 PM
cmell wrote:

I am trying to go from NChar 9 in Oracle to a Sql database field datatype
I am trying to go from NChar 9 in Oracle to a Sql database field datatype
varchar
Why do I get the " error in trying to convert between unicode and
non-unicode datatypes" when I try to do this? and how do I move the data in
sql 2005?

--
cindy


"jhofmeyr (AT) googlemail (DOT) com" wrote:

On Thursday, October 04, 2007 2:31 AM
jhofmey wrote:

Re: cannot convert between unicode and non-unicode data types
On Oct 2, 5:20 pm, cindy <cme... (AT) nospam (DOT) nospam> wrote:

Hi Cindy,

As Paul said, this should work by simply putting a Data Conversion
task between the Oracle source and the SQL destination tasks. It
sounds as if that is what you have done, but it is still not working.

You also mentioned that you've tried changing the columns in the SQL
database to NChar as well, but are still getting the error. SSIS
sometimes doesn't reset itself correctly to match metadata that has
changed. I would suggest changing the SQL database to be NChar (I
always use nvarchar and nchar for text data to allow for
internationalization anyway) and then deleting and recreating the
source and destination tasks in your package. If you'd like to keep
the SQL columns as varchar and it still doesn't work with the
conversion task between the source and destination after recreating
the tasks, check the Advanced Editor option on each task (accessed
through the right-click menu). This will allow you to view and modify
the column properties of each task.

If all else fails, email me your package to lumpywater at googlemail
dot com and I'll take a look at it for you.
Good luck!
J

On Thursday, October 11, 2007 7:54 PM
Angus wrote:

I have run into this as well.
I have run into this as well. When you get this error, unmap the column in question, and then remap it.

From http://www.developmentnow.com/g/103_...data-types.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

On Friday, November 13, 2009 11:26 AM
Carlos Cruz wrote:

cannot convert between unicode and non-unicode string data types
Cindy, I have the same problem, there is a solution, In the OLE DB Destination component, in the Mappings option in the input column you must choose the Data Conversion.[COLUMN_NAME], and that's it.

Carlos.

On Friday, March 12, 2010 2:23 PM
Raga wrote:

I have been working this issue and the best solution is ask your DBA to drop
I have been working this issue and the best solution is ask your DBA to drop the table and recreate it.. The destination data column.. I just resolved this issue... Or check and make sure you use Varchar only in your destn column

From http://www.developmentnow.com/g/103_...data-types.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/


Submitted via EggHeadCafe - Software Developer Portal of Choice
Join Lists with LINQ - SharePoint 2010
http://www.eggheadcafe.com/tutorials...-linq--sh.aspx

This is a headache for me and my area, especially given that we have a complicated DTS procedure in 7.0 to worry about as well. (Import or recreate from scratch? the recreation bit seems more viable)