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

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

"cindy" <cmello (AT) nospam (DOT) nospam> wrote

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:

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:

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

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

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

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.



Angus wrote:

I have run into this as well.
11-oct-07

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

Previous Posts In This Thread:

On jueves, 27 de septiembre de 2007 01:46 p.m.
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 jueves, 27 de septiembre de 2007 03:12 p.m.
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 jueves, 27 de septiembre de 2007 06:56 p.m.
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 lunes, 01 de octubre de 2007 01:18 p.m.
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 lunes, 01 de octubre de 2007 02:11 p.m.
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 martes, 02 de octubre de 2007 06:48 a.m.
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 martes, 02 de octubre de 2007 12:20 p.m.
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 jueves, 04 de octubre de 2007 02:31 a.m.
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 jueves, 11 de octubre de 2007 07:54 p.m.
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

EggHeadCafe - Software Developer Portal of Choice
Send Screen Captures Into A Database
http://www.eggheadcafe.com/tutorials...ures-into.aspx