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 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 .
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....

Best Regards

No comments: