Error with Unicode on Data source

Posted on 2011-10-18
Last Modified: 2013-11-10
I have a SSIS package that connects to an Oracle Database source and and SQL 2008 destination.  It basically does a SQL task to truncate the table in SQL server and then does a full dump of the table from Oracle.  (Refreshing the SQL table from Oracle source).

Anyway, I can do this manually, but decided to make a SSIS package since we want to allow users to trigger it.

The problem I have is that when I set up the Data Source (SOURCE!!!!) I get an error:
Column _____ cannot convert between unicode and non unicode string data types"

Note this error is BEFORE I even connect the source to any destination.

I understand that when that error is on the destination that I could use a data conversion to get it from one type to the other, but this does not work when the error is on the source - BEFORE connecting to any destination.  Any idea what is going on????
Question by:mrichmon
    LVL 6

    Expert Comment

    Please attach Oracle create table/view script
    LVL 35

    Author Comment

    Not sure what you are asking for....

    I have an OLE DB Source
    Connects using a  Connection Manager data source to the Oracle Database.
    Data Access is "Table or View"
    We select out the view in the drop down.

    Preview works fine.

    Columns show all columns selected.

    If you click "OK" it has an error on the data source as stated above....
    LVL 35

    Author Comment

    Okay so I went into SQL server and did the manual version of importing, but told it to save the SSIS package.  It does so - and it seems to not have this issue for the data sources.  So what is the difference?
    LVL 2

    Accepted Solution

    Data coming from the source select query is probabily unicode and the OLEDB source is by default non-Unicode so must set convert the columns to unicode.
    To do this, right click the OLEDB source component, click on "show advanced Editor..." and click on the "Input and Output Tab" click on each of the columns under the "Output Columns" under the "OLE DB Source Output" node and select "Unicode string [DT_WSTR]" from the DataType Property.
     Screen shot Screen shot
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Before loadin to destination use conversion task and convert it to DR_STR (Non unicode ) from DT_WSTR (unicode)
    LVL 35

    Author Comment

    I thought I closed this question last week (I guess the site maintenance interfered with the close request... since it was shortly before 3pm on the 18th))

    Anyway I played around and I ended up copying the data source created in the import to my package and that did not have the error.  Then after doing that for 2 more of the data tasks, it seemed to auto-clear up for the rest of the data tasks - I don't know why.

    I suspect the issue was what Frank_Banin describes, but can't be sure.

    Anyway it is a good response that may help others, so I will award the points there instead of deleting as I had originally tried to do.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now