Alright... I've beat my head against a wall on this issue at different times....
Let me just start with Yes you do have to have a data conversion task before loading into SQL Server. My specific situation is the source is an Oracle 11g server and the data is stored as varchar2. I have setup my sql server 2008 R2 table where the columns are nvarchar2 (unicode).
I have a data conversion task to convert the oracle columns to Unicode (WSTR_STR or something like that... I can't access my server to verify exactly how the converstion task references that).
The Oracle source is using the Oracle OLDB connector and the SQL Server Destination is using the SQL Server OLEDB connector.
I'm using the Oracle OLEDB connector because I have to build a select statement variable (to substitute in dynamic criteria).
All of this has been working fine until I promoted to a production project file. (ie a manual effort to of starting a new project file and loading it with the packages, etc of the currently working project and then updating the Data Sources to point to the production database (found on the same server)) .... getting the message unable to convert between unicode and non-unicode.
.... So in the new "production" project file, I changed the Oracle OLEDB connector to an Oracle.Net for OLEDB connector which required changes is how a built the dynamic query to be passed to Oracle..... This fixed the unable to convert unicode and non-unicode errors.
.... I came across this thread because of searching on the "unable to convert unicode and non-unicode" error. A couple of posts earlier than this one, someone mentioned that this maybe a 32-bit vs 64-bit issue.... I'm unable to test and confirm at this time; but, I bet that there is merit to that state has my Dev project is set to 32-bit and when creating the Prod project file, I forgot to set it to 32-bit (Solution Explorer->RightClick on Project->Properties->Debugging->Run64BitRuntime = False)
So... In short, if you're having problems with Unicode, Non-Unicode conversion and you are already using a Data Conversion task, the fix is either....
1. Use the Oracle.Net connector for your source
2. Set your project to run the 32bit Runtime.