Data conversion inserting to DB2 on AS400 with SSIS

I created a SSIS package moving data from a SQL 2005 table to an existing DB2 table on AS400 using Microsoft OLE DB Provider for DB2.

When the package was run, it showed that rows were successfully inserted to DB2. However, the data didn't seem to be converted correctly. Most of the string values were inserted as unusual characters. Also any string values of digits were not inserted.

For example, 1.) a character field (char(1) or nchar(1) as I have tried both types) in SQL 2005 table with a simple value of 'H' was inserted into the DB2 table field of type "A" (alphanumeric) of length 1 as '?' and others letters were inserted as other unusual characters. 2.) A string value of '00100' in SQL Server is not inserted to DB2 table at all.

Later we found that the fields inserted with usual characters are difined as CSSID =65535. A few fields with correct data inserted have CSSID=00037.

Does anyone know why this happened and how to solve this to get the data inserted correctly in the DB2 table?

Thanks in advance for any help!

Try to set the appropriate LocaleID and DefaultCodePage on your destination component.

Thanks.

|||

The LocaleID and DefaultCodePage were set correctly.

What I found was in the Data Link Properties of connection manager, the "Host CCSID" was set to "OEM - United States [437]". I changed it to "EBCDIC - US/Canada [37]" and it started working perfectly.

Thank you for your suggestion though.

Data Conversion Components & Code Page issue

I am using the SSIS wizard to pull data from DB2 z/os to sql server. The data flow task that is created converts the data to DT_STR Ansi 1252 before storing to sql server database. The package is blowing up on in the data conversion component...no match in found in target code page...for my city name field.

My old dts wizard didn't have this problem. The forums seem to indicate that SSIS is no longer doing some of the implicit conversions that DTS did and I may have to do more than one conversion.

What format type/code page do I use for the other conversion? The code page for my DB2 data source is 37.

I've tried several scenarios and none of them have worked. Any hints?

Quick and dirty work around was to change the destination column to nchar.|||

This one is a good workaround if you don't mind your strings being Unicode at the destination.

If you would rather keep your ANSI strings, then you should go to the package set the appropriate code page in the data conversion transform and set the appropriate collation in the CREATE TABLE statement.

Thanks.

|||

I tried that and couldn't get it to work.

My problem isn't a foreign language issue, but a special character issue. My old DTS packages accepted the special characters without a fuss but I'm told SSIS isn't doing the implicit conversion that the old DTS use to do.

Obviously, I can't seem to figure out which code page to use. And where do I set all of these code pages? My input data is coming from DB2 z/OS which is using code page 37. Do I set the code page of the source component to 37? The destination? The transform component? I tried setting the code page with multiple scenarios...none worked.

There is no where that I can find that explains how to do a code page transformation except in the most general terms.

PS. Wouldn't I only change the collation for a foreign language?

Data comparision of different data bases in crystal report 8.5

I have a requirement to compare the data from different data bases ( Oracle , Sybase, DB2) and hight the data if its different then Oracle data.
We have the same data in Oracle ,sybase and DB2. I now need to connect to all these DBs and get the data and then compare them and provide hight light if the data is different.

Can any expert provide me the best way of doing in crystal reports 8.5
I am trying to use the formula field .But don't know how to format the data and dispay with bold in crystal function.Link the databases. Then, if you want to show only the records with mismatched data fields, in your select formula pull only those records where the field(s) you are comparing are not equal. However, if you want to pull (and list) all the records but highlight only the mismatches, use highlighting.