Home » SQL Server

Problem using SSIS to move DB2 to oracle


I will need your advice on a SSIS package which moves IBM DB2 (for z/OS V7) data to Oracle (AIX 10g). Thank you in advance.

Basically, this is a very simple SSIS package (created by SSIS import / export wizard) and just maps column by column. I used IBM OLEDB provider for DB2 to connect the source and Oracle Provider for OLEDB to connect to the destination.

The problem I ran into was that if the DB2 source value contains special (or invisible) characters then this value will be converted to a SPACE (' ') value on Oracle. To be more detailed, take the following example:

On DB2, value  000002D0000002D7 (EBCDIC HEX codes). This is actually a value of a column defined on DB2 as CHAR(8). Please note HEX '00' in EBCDIC stands for NULL and HEX 'D0' stands for character 'K';

On Oracle, this value will be stored as a SPACE (or a string of SPACEs) after data move.

On both DB2 and Oracle this column was defined as CHAR(8).

I guessed this was related to a code page issue, so I tried a few different code page to set for the source property (by setting the DefaultCodePage of the DataFlow source property), I used code page 37, 500, etc... All resulted in the same Oracle value.

The character set on DB2 is EBCDIC and the one on Oracle is ALT32UTF8. I guess it was the source provider (DB2 OLEDB provider) that converted this value to a SPACE.

So my questions are:

1. Which code page should I use for the DB2 source? And how can I know that the DB2 source should use this codepage? I remember there was a page on MSDN indicating all possible code pages, I tried but cannot find it.

2. How do I preserve the original value on DB2? that means I will have some invisible characters on Oracle while having the visible 'K' (or D2, if represented by EBCDIC HEX code) in the value. This is my essential goal. However, for doing this, I do NOT want to change data type to CHAR(8) type on Oracle. Actually, I changed the oracle column type to BLOB and it was working (meaing I have the binary code on Oracle side after the data move)

Any input will be greatly appreciated.



2 Answers Found


Answer 1

And the SSIS version is 2008 Enterprise.


Answer 2

Hmm.  I'm not sure your issue  is with the IBM DB/2 driver.  I'm suspicious that SSIS may be artificially trimming that data  because .Net/C code  tends to assume that strings are terminated by 0x00 characters. 

In other words, the IBM provider  may be supplying the full set  of data, but SSIS may not be passing it on.  In order to debug this, you'll have to fire up BIDS to run the package  interactively, if you're not already doing so.

I'd say use a Data Viewer on your source  output to see if you can see those things - but I'm betting not.  I say not because even if SSIS is storing the full 8 characters, I doubt the Data Viewer would show them... but you can try.

My next suggestion would be to throw a Data Conversion component on there, converting one of those columns to a byte array (DT_BYTES) of length 8.  Then throw a Data Viewer on the flow and see what you see.

If you don't see what you want to see, then we may need to alter the SSIS source to read those columns in as byte arrays, not as strings.  If you have a conversion syntax for the DB/2 query, you could try that (to make SSIS automatically detect it's a byte array, not a string).  Or, I think you should be able to edit the "advanced" properties of the source (right-click, Advanced Editor, I/O page) to change the data type to DT_BYTES (length of 8).  Throw a Data Viewer up there now, and see what you get.

Oh, and to actually run these without having to hook up the destination, just drop a Union All on the flow to act as a "terminator".

That's just the first step to see if we get anything meaningful.  How to shove that into Oracle (via OLE DB) is step 2, assuming we get something with this.




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure