[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Error with Unicode on Data source

Posted on 2011-10-18
Medium Priority
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

Expert Comment

ID: 36988348
Please attach Oracle create table/view script
LVL 35

Author Comment

ID: 36988404
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

ID: 36988437
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?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

Frank_Banin earned 2000 total points
ID: 36993653
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
ID: 36997798
Before loadin to destination use conversion task and convert it to DR_STR (Non unicode ) from DT_WSTR (unicode)
LVL 35

Author Comment

ID: 37019544
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

872 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