SSIS Talk

Over on the forums today a user, Pra Rav, has asked how do we work with mainframe zoned decimals. I have the answer, at least on how *I* deal with them. Take it or leave it as there may be other ways to do this, but it works slick enough for me to blog about it and to share it with Pra. Read on….

Let’s say we have the following data file with two rows in it. This data file contains three columns:

06 000960{ 00000{
07 000471C 00074}

What do we have here? The first column represents a key, perhaps. The second and third columns could be dollar amounts, perhaps. In normal terms the fields above are really representing:

06 +9600 +00
07 +4713 -740

The problem with zoned decimals, and it’s not really a big deal, is that the last byte is overlaid with a sign representation. The last byte indicates the sign AND the numerical value. One other thing to note is that the data does not contain decimal point placement information. That is handled outside of the storage and is really a presentation layer feature.

So, how do we work with this in SSIS? Actually, it’s pretty easy with some simple tricks. (I’m going to move away from the file processing and just pretend that I only have one field containing a zoned decimal called “CurrencyValue.”)

Basically we only need three transformations in the data flow to convert these values: two derived columns and a lookup transformation.

In the first derived column transformation, we do two things. The first is to grab the right-most character of our field. This character contains the sign for the entire number and the numerical value of the last digit. We also check to ensure that we don’t have nulls or blanks coming in, if we do, assign a default value of “0{” just to passify the later transformations.

As you can see above (click on the image to expand it), we added a column “SignChar” and replaced the CurrencyValue column with a default value if nothing existed there. Pretty simple and straightforward stuff.

Next, we add a simple lookup table. This “table” is really just an SQL statement to perform our conversion. We’ll output the sign and the numerical value from this lookup. Use the SQL below to build the lookup table:

select 1 as sign, '{' as lookupvalue, 0 as returnvalue
union all
select 1 as sign, 'A' as lookupvalue, 1 as returnvalue
union all
select 1 as sign, 'B' as lookupvalue, 2 as returnvalue
union all
select 1 as sign, 'C' as lookupvalue, 3 as returnvalue
union all
select 1 as sign, 'D' as lookupvalue, 4 as returnvalue
union all
select 1 as sign, 'E' as lookupvalue, 5 as returnvalue
union all
select 1 as sign, 'F' as lookupvalue, 6 as returnvalue
union all
select 1 as sign, 'G' as lookupvalue, 7 as returnvalue
union all
select 1 as sign, 'H' as lookupvalue, 8 as returnvalue
union all
select 1 as sign, 'I' as lookupvalue, 9 as returnvalue
union all
select -1 as sign, '}' as lookupvalue, 0 as returnvalue
union all
select -1 as sign, 'J' as lookupvalue, 1 as returnvalue
union all
select -1 as sign, 'K' as lookupvalue, 2 as returnvalue
union all
select -1 as sign, 'L' as lookupvalue, 3 as returnvalue
union all
select -1 as sign, 'M' as lookupvalue, 4 as returnvalue
union all
select -1 as sign, 'N' as lookupvalue, 5 as returnvalue
union all
select -1 as sign, 'O' as lookupvalue, 6 as returnvalue
union all
select -1 as sign, 'P' as lookupvalue, 7 as returnvalue
union all
select -1 as sign, 'Q' as lookupvalue, 8 as returnvalue
union all
select -1 as sign, 'R' as lookupvalue, 9 as returnvalue

Next, set up the mappings:

We map our input, SignChar to “lookupvalue” and return “returnvalue” and “sign.”

The last step is to calculate the actual value of the zoned decimal. We use the second derived column to do this and basically we replace the last character of the zoned decimal with “returnvalue” and then multiply that value by “sign.”

(In my work I chose to have the output of this be character data, but you could cast it to a decimal, integer, whatever if you desire.)

Here’s the expression:

(DT_STR,17,1252)(sign * (DT_I8)REPLACE(CurrencyValue, RIGHT(CurrencyValue,1), (DT_WSTR,1)returnvalue))

And that’s pretty much it. NewCurrencyValue will contain the converted number from Zoned Decimal to a “normal” representation to be able to be used in calculations and such.

NOTE: There is one gotcha with this. The example I provided above works well for ONE column. If you have many, you’d better look into unpivoting the records first before going into the first derived column transformation, and then pivot them back when done with the last derived column. That way, you only have to build this once, versus one for each column.