2

I am creating an ASCII output file contain some information and two of my fields require the date and time to be in packed decimal fields (COMP-3). I am using SQL and SSIS to generate my file.

Field Name     Format       Length    Picture
Date           YYYYMMDD     5         S9(8) C-3
Time           HHMMSS       4         S9(6) C-3

I have searched multiple sites and I still can't figure out how to convert a date/time into a packed decimal field. Any help would be greatly appreciated.

The following site http://www.simotime.com/datapk01.htm#TablePackedDecimal was provided for information about Packed fields. I understand how packed fields are used, but don't really understand how to pack a field. Thanks!

Bill Woodger
  • 12,968
  • 4
  • 38
  • 47
buzzzzjay
  • 1,140
  • 6
  • 27
  • 54
  • General idea is that you will need to generate two integer fields containing the date and time values `Derived Column Transformation`. I've had nothing but pain with COMP-3 but I suspect that was more a factor of bad source data. Connect a `Script Transformation` to make those values into their binary equivalent and write to file. – billinkc Jan 23 '12 at 16:17
  • I am not sure what to use in a Script Transformation because I don't really understand what I am transforming to? Cause I see hex and binary as an option on the site I listed above. I don't see how a date YYYYMMDD could fit into 5 characters using the Packed method. – buzzzzjay Jan 23 '12 at 17:23
  • You'll need a routine in C#/VB.NET to operate on `Row.Date` and `Row.Time` Common question on SO and google [Convert unpacked deicmal](http://stackoverflow.com/questions/4337912/how-to-convert-unpacked-decimal-back-to-comp-3) – billinkc Jan 23 '12 at 17:51

1 Answers1

5

If you store an integer date (ie 20120123) as a character string the hex representation would be 0x3230313230313233 where 32 = 2, 30 = 0 etc, which is 8 bytes (ie 32 30 31 32 30 31 32 33) of storage.

In a packed decimal format the representation of the same string would be: 0x020120123F The F is a bit to show that this is an unsigned integer. The other numbers are stored as half of a byte for each digit. So you can see that a common date string would fit into a 5 byte (ie 02 01 20 12 3F ) field.

So, to use this in SSIS you would probably have to do as @billinkc stated above and use a Script Transformation to transform the field. The mechanics of this would be to count the digits in your number, pad with 0's on the left to get your characters up to 9 for comp-3 5 and 7 for comp-3 4 then construct a hexidecimal string with the digits from your date or time and add a F at the end (or a C if your destination expects a signed number).

William Salzman
  • 6,396
  • 2
  • 33
  • 49
  • I understand what your saying and how to do it, but I don't understand how (02 01 20 12 3F) would fit into 5 characters. I just might be looking at it wrong but I see 10 characters. – buzzzzjay Jan 23 '12 at 19:04
  • 1
    Each of those two number pairs is a single byte (or character) of information. A single byte can be represented by a hexadecimal number between 00 and FF (in decimal from 0 to 255 and in binary from 0000 0000 to 1111 1111) – William Salzman Jan 25 '12 at 02:50
  • I am writing this information to a text file and only have 5 characters to contain the packed date. So in the example above what would I use that only takes up one character position? Wouldn't 00 or FF take up two character positions? My file is going to a COBOL mainframe system. Thanks for the help! – buzzzzjay Jan 25 '12 at 15:16
  • 1
    It is a hexadecimal number you are sending, not a string. It will look wrong as a string. Look at an ascii chart. The last of the five characters in the example above is 3F (a hexadecimal number). In ASCII a 3F character is a "?" and a 20 (the third character) is a space. The two digit numbers I am referencing in my examples are simply internal computer representations of a character in computer memory. Did you ever take any classes on computer theory? – William Salzman Jan 26 '12 at 05:43
  • I believe I did at one point. I also understood that the two number you were referencing were representations of the character in computer memory, but I just wasn't sure what the Packed field method wanted the values returned. I wasn't sure if it wanted ASCII values or if there was something else I was missing, like another conversion process. I have been able to find very little on Packed Fields and I wanted to make sure I was sending the correct information. I really do appreciate your help! Thanks! – buzzzzjay Jan 26 '12 at 13:53