Re: cannot convert between unicode and non-unicode data types
- From: jhofmeyr@xxxxxxxxxxxxxx
- Date: Thu, 04 Oct 2007 06:31:05 -0000
On Oct 2, 5:20 pm, cindy <cme...@xxxxxxxxxxxxx> wrote:
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
"jhofm...@xxxxxxxxxxxxxx" wrote:
On Oct 1, 7:11 pm, cindy <cme...@xxxxxxxxxxxxx> 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
"n...@xxxxxxxxxxxxxxxxxxx" 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...@xxxxxxxxxxxxx> 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:
You can use a Data Conversion transformation. Add it between your data
source and the destination.
"cindy" <cme...@xxxxxxxxxxxxx> wrote in message
news:3369E769-5A87-41B8-AB6E-D014203EC27A@xxxxxxxxxxxxxxxx
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
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- Hide quoted text -
- Show quoted text -
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
.
- References:
- Prev by Date: Re: Available Input columns on Lookup not available
- Next by Date: RE: SSIS use loop to set dynamic global variables
- Previous by thread: Re: cannot convert between unicode and non-unicode data types
- Next by thread: RE: cannot convert between unicode and non-unicode data types
- Index(es):
Relevant Pages
|
