1

I'm receiving a file from a mainframe group that includes Comp-3 packed decimals and I'm having some problems converting it to a normal decimal.

For the SSIS Setup I have the following:

Flat File Source -> Advanced Editor -> Input and Out Properties
Choose the column -> UseBinaryFormat = True

I've checked to make sure I'm reading the correct sequence of bytes(according to the document sent). And I've done the correct mappings to the correct column.

In the Flat File Connections Manager Editor:

Advanced -> Choose Column -> byte stream [DT_BYTES], OutputColumnWidth:6

In the database/table I have a varbinary(6) column set up for the packed decimal, when the file gets read in I get the following in the column:

 0x00000018D72A

I spoke with the mainframe guys and they said for that particular record the decimal value should be:

187.05

I wrote a CLR Function to convert to a decimal(copied the C# from here:http://www.microsoft.com/en-us/download/details.aspx?id=20397 and wrapped it into a CLR function). The function seems to be working.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDecimal UnpackDecimal(SqlBytes value, SqlInt32 scale)
{
    byte[] inp = value.Buffer;
        long lo = 0;
        long mid = 0;
        long hi = 0;
        bool isNegative;

        // this nybble stores only the sign, not a digit.  
        // "C" hex is positive, "D" hex is negative, and "F" hex is unsigned. 
        switch (nibble(inp, 0))
        {
            case 0x0D:
                isNegative = true;
                break;
            case 0x0F:
            case 0x0C:
                isNegative = false;
                break;
            default:
                throw new Exception("Bad sign nibble");
        }
        long intermediate;
        long carry;
        long digit;
        for (int j = inp.Length * 2 - 1; j > 0; j--)
        {
            // multiply by 10
            intermediate = lo * 10;
            lo = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            intermediate = mid * 10 + carry;
            mid = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            intermediate = hi * 10 + carry;
            hi = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            // By limiting input length to 14, we ensure overflow will never occur

            digit = nibble(inp, j);
            if (digit > 9)
            {
                throw new Exception("Bad digit");
            }
            intermediate = lo + digit;
            lo = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            if (carry > 0)
            {
                intermediate = mid + carry;
                mid = intermediate & 0xffffffff;
                carry = intermediate >> 32;
                if (carry > 0)
                {
                    intermediate = hi + carry;
                    hi = intermediate & 0xffffffff;
                    carry = intermediate >> 32;
                    // carry should never be non-zero. Back up with validation
                }
            }
        }
        return new Decimal((int)lo, (int)mid, (int)hi, isNegative, (byte)scale);
    }

    private static int nibble(byte[] inp, int nibbleNo)
    {
        int b = inp[inp.Length - 1 - nibbleNo / 2];
        return (nibbleNo % 2 == 0) ? (b & 0x0000000F) : (b >> 4);
    }

The following unit test does actually work:

byte[] bytes = new byte[6];
        bytes[0] = 0;

        bytes[1] = 0;

        bytes[2] = 0;

        bytes[3] = 24;

        // decimal place holders
        bytes[4] = 112;
        // sign byte
        bytes[5] = 92;
        decimal result = TestObject.Unpack(bytes, 2);

It returns 187.05 (the expected result).

How do I go from:

0x00000018D72A

To

00 00 00 24 112 92

so my CLR function will work?

Or am I making a mistake elsewhere?

EDIT: I made a small mistake when I C/P the CLR function over parameter should've been SqlBytes not byte[]

EDIT2: When I run

SELECT dbo.UnpackDecimal(0x00000018D72A,2)

I get

Msg 6522, Level 16, State 2, Line 38
A .NET Framework error occurred during execution of user-defined routine or aggregate                         "UnpackDecimal": 
System.Exception: Character 10 is a bad sign nibble
System.Exception: 
at UserDefinedFunctions.UnpackDecimal(SqlBinary value, SqlInt32 scale).
  • 1
    Assuming you have deployed the CLR function to your SQL Server instance, what happens when you try SELECT dbo.UnpackDecimal(0x00000018D72A,2) – Max Vernon Sep 17 '14 at 15:42
1

Since I cannot reasonably add this as a comment, I am adding an answer. If this answer doesn't help, I will delete it.

The code below ignores the check to see if the first nibble is a valid sign-indicator. This will allow you to get past the exception you are seeing.

I haven't looked at the code in enough detail to understand what it actually does, this is simply a test to see if you some number out the other end of the code.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDecimal UnpackDecimal(SqlBytes value, SqlInt32 scale)
{
    byte[] inp = value.Buffer;
        long lo = 0;
        long mid = 0;
        long hi = 0;
        bool isNegative = false;

        long intermediate;
        long carry;
        long digit;
        for (int j = inp.Length * 2 - 1; j > 0; j--)
        {
            // multiply by 10
            intermediate = lo * 10;
            lo = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            intermediate = mid * 10 + carry;
            mid = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            intermediate = hi * 10 + carry;
            hi = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            // By limiting input length to 14, we ensure overflow will never occur

            digit = nibble(inp, j);
            if (digit > 9)
            {
                throw new Exception("Bad digit");
            }
            intermediate = lo + digit;
            lo = intermediate & 0xffffffff;
            carry = intermediate >> 32;
            if (carry > 0)
            {
                intermediate = mid + carry;
                mid = intermediate & 0xffffffff;
                carry = intermediate >> 32;
                if (carry > 0)
                {
                    intermediate = hi + carry;
                    hi = intermediate & 0xffffffff;
                    carry = intermediate >> 32;
                    // carry should never be non-zero. Back up with validation
                }
            }
        }
        return new Decimal((int)lo, (int)mid, (int)hi, isNegative, (byte)scale);
    }

    private static int nibble(byte[] inp, int nibbleNo)
    {
        int b = inp[inp.Length - 1 - nibbleNo / 2];
        return (nibbleNo % 2 == 0) ? (b & 0x0000000F) : (b >> 4);
    }
  • I did what you suggested, and I also commented out the second check if(digit > 9) throw new exception.... It was able to run, but gave me 193.72 instead of the expected 187.05(so we're closer at least ;) – Chris L Sep 17 '14 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.