January 16, 2008 at 8:59 am
Ok I give. I have a SqlServer database that uses data from a DB2 database. It grabs a dataset from the DB2 system massages it and then tries to write back a subset of that data to a table I created on the DB2 database. Here's the kicker. The DB2 table we'll call TABLE_A has just 3 columns all varchar (I've tried with plain char with same results) and a SqlServer table we'll call tblMainSet. When I try to grab a subset of data from tblMainSet (3 columns of varchar NOT nvarchar to match the DB2 side) and then write it to TABLE_A I get "can not convert non-unicode data to unicode" errors! When I go to the map columns tab on the OLEDB destination, I see the darndest thing.... SSIS has decided that the SqlServer table has varchar data AND the DB2 table is UNICODE! After fighting back with conversions, changing table defs, etc. I decided to just go with it an converted the output from the SS table to be unicode and the warnings went away! Yeah! Ran it and the damn thing blew up cause the DB2 table obviously isn't really hasn't got any unicode columns to write to.
So my question is, how can I get around this. I can't write unicode to it. The target keeps getting listed as unicode (which is wrong). I can't cast it cause it is already varchar and the problem is on the store side. Any ideas. I have to write the data back so that it can be bumped against a huge dataset on DB2 to get the final set I need, otherwise I'd just bring it all down and be done with it,
March 27, 2008 at 1:47 pm
I had a similar issue with DB2 datatype mismatches and found the issue to reside in my DB2Connect configuration settings.
I was using IBM's DB2Connect v8 database driver and there is a CLI Setting named OleDbReturnCharAsWChar. By default the configuration is set to return CHAR datatypes from DB2 as unicode (DT_WSTR) datatypes. Changing this client configuration setting in DB2Connect to return CHAR datatypes as non-unicode (DT_STR) datatypes resolved these problems within my SSIS packages.
Just a thought if you happen to be using DB2Connect to access the DB2 database.
I hope this helps.
March 28, 2008 at 5:04 am
Yes we are using DB2connect. I will pass this on to our DBAs it is the only option I had left (besides doing everything in DB2 then bringing results down).
You are awesome.
Thank you! 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply