Thursday, August 13, 2009
Cannot convert between unicode and non-unicode string data types
I was creating a Integration service which needs to fetch data from a POS (Point of Sale) application and migrate to JDE ERP system. The Source is MS Access Database & the destination tables are in JD-Edwards DB2. In between there are vital manipulation logic to be done, which is taken care in MS SQL Server with the help of SSIS. When i was creating OleDB data destination for DB2 there were lot of issues. I am really thankful to this thread in MSDN http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/4128e62f-92d1-4125-baa3-fdd9fed59d48 which solved the connectivety issues. The final data flow there supposed to migrate data from SQL server to DB2. The problem i faced & invested lot of hours was on this error..
"Cannot convert between unicode and non-unicode string data types"
To solve lot of people gave lot of advices in forum http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/21646e0b-d742-467a-8880-255ebf198c46 .
Some people told Data conversion component is helpful ,In my case I am transferring data into Z-files. These are tables F4001Z & F4011Z which have 149 columns & 249 columns each. And doing mapping for each fields is a boring task...
Solution: From the error it is clear that the issue is converting unicode to non-unicode string. So What i did was, I created a Local table in SQL Server with same table structure of F4001Z & F4011Z tables (like F4001Z_local & F4011Z_local). And in Table creation SQL script, where ever there was nchar datatype, I replaced with varchar . Now in the source Data Flow section, the manipulated data will we inserted into these local tables F4001Z_local & F4011Z_local and then a Select query is run through a stored procedure. Doing so in the string is non-unicode string or DT_STR format we can say... The Error got resolved :) The screen shots says the rest, The data conversion component was placed before. I believe if we remove the component also the application would work fine....