cannot convert between unicode and non-unicode data types

Discussion in 'microsoft.public.sqlserver.dts' started by cindy, Sep 27, 2007.

  1. cindy

    cindy Guest

    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
     
    cindy, Sep 27, 2007
    #1
    1. Advertisements

  2. cindy

    Paul Shapiro Guest

    You can use a Data Conversion transformation. Add it between your data
    source and the destination.
     
    Paul Shapiro, Sep 27, 2007
    #2
    1. Advertisements

  3. cindy

    cindy Guest

    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, Sep 27, 2007
    #3
  4. cindy

    nick Guest

    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.
     
    nick, Oct 1, 2007
    #4
  5. cindy

    cindy Guest

    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, Oct 1, 2007
    #5
  6. cindy

    jhofmeyr Guest

    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
     
    jhofmeyr, Oct 2, 2007
    #6
  7. cindy

    cindy Guest

    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, Oct 2, 2007
    #7
  8. cindy

    jhofmeyr Guest

    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
     
    jhofmeyr, Oct 4, 2007
    #8
  9. cindy

    Angus Guest

    Angus, Oct 12, 2007
    #9
  10. cindy

    Carlos Cruz Guest

    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...between-unicode-and-non-unicode-data-types.ht

    Posted via DevelopmentNow.com Group
    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 dat
    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
    --
    cind

    :

    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 Microsof
    product can't actually produce a meaningful error message

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


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

    :

    On martes, 02 de octubre de 2007 06:48 a.m.
    jhofmey wrote:

    Re: cannot convert between unicode and non-unicode data types

    Hi Cindy and n..

    Cindy: It sounds from your description like you are trying to retriev
    a non-unicode (ASCII) column from Oracle (Char(9)) and insert it int
    a Unicode column in SQL Server 2005 (NChar(9)?). From you
    description of the steps you have taken so far, you mention selectin
    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


    :

    On jueves, 04 de octubre de 2007 02:31 a.m.
    jhofmey wrote:

    Re: cannot convert between unicode and non-unicode data types

    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...etween-unicode-and-non-unicode-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/tutorial...2-077a389706d1/send-screen-captures-into.aspx
     
    Carlos Cruz, Nov 13, 2009
    #10
  11. cindy

    Raga Guest

    Raga, Mar 12, 2010
    #11
  12. cindy

    Doug Ivison Guest

    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/SQLSe...ert-unicode-to-non-unicode?wa=wsignin1.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:

    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

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

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

    :

    Exactly the same problem.
    Exactly the same problem

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

    It doesn't really help that SSIS feels the need to produce a separat
    set of definition strings - I know that my output is Char(9) since
    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.



    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


    :

    Re: cannot convert between unicode and non-unicode data types

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


    :

    Re: cannot convert between unicode and non-unicode data types

    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.
    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...etween-unicode-and-non-unicode-data-types.htm

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

    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.

    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...etween-unicode-and-non-unicode-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/tutorial...6e-7d3fb7d38eca/join-lists-with-linq--sh.aspx
     
    Doug Ivison, Apr 1, 2010
    #12
  13. cindy

    Arno Mikli Guest

    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)
     
    Arno Mikli, Aug 25, 2011
    #13
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.