none
cannot convert between unicode and non-unicode string data types.

    Question

  • I'm trying to copy the data from 21 tables in a SQL 2005 database to a MS Access database using SSIS. Before converting the SQL database from 2000 to 2005 we had this process set up as a DTS package that ran every month for years with no problem.  The only way I can get it to work now is to delete all of the tables from the Access DB and have SSIS create new tables each time. But when I try to create an SSIS package using the SSIS Import and Export Wizard to copy the SQL 2005 data to the same tables that SSIS itself created in Access I get the "cannot convert between unicode and non-unicode string data types" error message. The first few columns I hit this problem on were created by SSIS as the Memo datatype in Access and when I changed them to Text in Access they started to work. The column I'm stuck on now is defined as Text in the SQL 2005 DB and in Access, but it still gives me the "cannot convert" error.
    Wednesday, June 11, 2008 6:42 PM

All replies

  • I get the same error with two Text fields in an MS Access database.  I am pulling 4 fields from a stored procedure call in a SQL 2005 DB.  The data source shows both fields as a non-unicode string under the Output Columns item, Advanced Editor, Input and Output Properties tab (BI Dev Studio).  But under the External Columns item (Advanced Editor also) for the Destination, the same columns show as unicode strings.  There are no transforms in between the data source and destination on the Data Flow Task tab.  Does anyone have any idea how or why a non-unicode string gets magically converted to a unicode string when doing so causes the task to break?  Thanks in advance.

    Friday, June 27, 2008 8:25 PM
  • I was getting same error while tranfering data from SQL 2005 to Excel , but using following method i was able to tranfer data. Hopefully it may also help you.

    1) Using Data Conversion transformation
       data types you need to select is DT_WSTR (unicode in terms of SQL: 2005)
    2) derived coloumn transformation
       expression you need to use is :
        (DT_WSTR, 20) (note : 20 can be replace by your character size)

    Note:
    Above teo method create replica of your esting coloumn (default name will be copy of <coloumn name>).
    while mapping data do not map actual coloumn to the destination but select the coloumn that were created by any of above data transformer (replicated coloumn).

    Sunday, June 29, 2008 8:20 AM
  • I found additional info online indicating that a Data Conversion task should be used (similar to what N.S. Shah posted).  My situation required me to go from non-unicode to unicode in order to get the SQL data to insert in MS Access.

    Monday, June 30, 2008 12:12 PM
  • Is there a way to tell SQL Server 2005 to stop promoting Excel and Access to unicode? This kills one shot uploads if you have to keep writing custom SSIS packages to import data.
    Monday, June 30, 2008 7:16 PM
  • SSIS isn't "promoting" Excel and Access to Unicode. Excel and Access both use Unicode for string data internally. The challenge is that SSIS will not implicitly convert from Ansi (varchar, DT_STR) strings to Unicode (nvarchar, DT_WSTR). In 2005, you have to add this conversion into the package yourself.

     

    In SQL Server 2008, the Import/Export Wizard will handle some of the data conversions itself, without you having to add it after the fact.

     

    Wednesday, July 02, 2008 5:57 PM
    Moderator
  • A patch for the existing server would be a lot better. My systems are not using unicode and never will. Why do I have to write a custom script for every one time import?

    I understand the need for strong typing. However, since the existing DTS package designer lets you do this, I can't imagine it is that difficult to turn the type checking off.
    Wednesday, July 02, 2008 6:22 PM
  • Actually, the data flow (and most of the control flow) was written from the ground up for SSIS. So DTS's ability to do it doesn't really impact the difficulty of implementing it in SSIS. The data flow is very closely tied to the types of the data it is passing through. The strong typing is one of the primary reasons that it is so fast. It would be a fairly major change to support implicit types.

     

    Even in 2008, it's not supporting implicit types, it's just adding a data conversion step.

     

    Wednesday, July 02, 2008 6:37 PM
    Moderator
  • Based on the link below, this may have been fixed in 2008... but no mention of a fix in 2005.

    PLEASE go to the link below,
    vote for the thread,
    and comment, suggesting a fix for 2005!

    https://connect.microsoft.com/SQLServer/feedback/details/337679/ssis-cannot-convert-unicode-to-non-unicode?wa=wsignin1.0#tabs

    THANKS!

    Thursday, April 01, 2010 8:29 PM
  • Even in 2008, it's not supporting implicit types, it's just adding a data conversion step.

    Thanks for the clarification. 

    ((I'm looking at the conversion to SQL Server 2008 just for this... would save so much time... unfortunately, it's not clear yet that the vendor of our 3-party application supports 2008.... yet.))

    If I'm understanding correctly, you're saying that 2008 generates the Data Conversion Tasks automatically, when an ASCII column is mapped to a Unicode column / vice versa.

    QUESTION:  the automation you're talking about, does it only happen when the Import/Export Wizard is used? 
    Or, is it also automated (or easy?) IN VISUAL LINK 2008, when an ASCII column is mapped to a Unicode column?
    (or Mgmt Studio 2008, etc.... SOMEWHERE that SSIS MODIFICATIONS can be done...),

    Thanks for your time...

    Friday, June 11, 2010 3:56 PM