Geeks With Blogs
Jim Giercyk

One of the challenges recently thrown my way was to read an EBCDIC flat file, decode packed dates, and insert the dates into a SQL table.  For those unfamiliar with packed data, it is a way to store data at the nibble level (half a byte), and was often used by mainframe programmers to conserve storage space.  In the case of my input file, the dates were 2 bytes long and  represented the number of days that have past since 01/01/1950.  My first thought was, in the words of Scooby, Hmmmmph?  But, I love a good challenge, so I dove in.

Reading in the flat file was rather simple.  The only difference between reading an EBCDIC and an ASCII file is the Code Page option in the connection manager.  In my case, I needed to use Code Page 1140 for EBCDIC (I could have also used Code Page 37).    

cm

 

Once the code page is set correctly, SSIS can understand what it is reading and it will convert the output to the default code page, 1252.  However, packed data is either unreadable or produces non-alphabetic characters, as we can see in the preview window.

preview

 

Column 1 is actually the packed date, columns 0 and 2 are the values in the rest of the file.  We are only interested in Column 1, which is a 2 byte field representing a packed date.  We know that 2 bytes of packed data can be stored in 1 byte of character data, so we are working with 4 packed digits in 2 character bytes.  If you are confused, stay tuned….this will make sense in a minute.

OutputColumns

 

Right-click on your Flat File Source shape and select “Show Advanced Editor”. Here is where the magic begins. By changing the properties of the output columns, we can access the packed digits from each byte. By default, the Output Column data type is DT_STR. Since we want to look at the bytes individually and not the entire string, change the data type to DT_BYTES. Next, and most important, set UseBinaryFormat to TRUE. This will write the HEX VALUES of the output string instead of writing the character values.  Now we are getting somewhere!

Next, you will need to use a Data Conversion shape in your Data Flow to transform the 2 position byte stream to a 4 position Unicode string containing the packed data.  You need the string to be 4 bytes long because it will contain the 4 packed digits.  Here is what that should look like in the Data Conversion shape:

convert

Direct the output of your data flow to a test table or file to see the results.  In my case, I created a test table.  The results looked like this:

  queryResults

 

Hold on a second!  That doesn't look like a date at all.  No, of course not.  It is a hex number which represents the days which have passed between 01/01/1950 and the date.  We have to convert the Hex value to a decimal value, and use the DATEADD function to get a date value.  Luckily, I have created a function to convert Hex to Decimal:

 


-- =============================================
-- Author:        Jim Giercyk
-- Create date: March, 2012
-- Description:    Converts a Hex string to a decimal value
-- =============================================
CREATE FUNCTION [dbo].[ftn_HexToDec]
(
    @hexValue NVARCHAR(6)
)
RETURNS DECIMAL
AS
BEGIN
    -- Declare the return variable here
DECLARE @decValue DECIMAL
IF @hexValue LIKE '0x%' SET @hexValue = SUBSTRING(@hexValue,3,4)

DECLARE @decTab TABLE
(
decPos1 VARCHAR(2),
decPos2 VARCHAR(2),
decPos3 VARCHAR(2),
decPos4 VARCHAR(2)
)

DECLARE @pos1 VARCHAR(1) = SUBSTRING(@hexValue,1,1)
DECLARE @pos2 VARCHAR(1) = SUBSTRING(@hexValue,2,1)
DECLARE @pos3 VARCHAR(1) = SUBSTRING(@hexValue,3,1)
DECLARE @pos4 VARCHAR(1) = SUBSTRING(@hexValue,4,1)

INSERT @decTab
VALUES (CASE
              WHEN @pos1 = 'A' THEN '10'
                WHEN @pos1 = 'B' THEN '11'
              WHEN @pos1 = 'C' THEN '12'
              WHEN @pos1 = 'D' THEN '13'
              WHEN @pos1 = 'E' THEN '14'
              WHEN @pos1 = 'F' THEN '15'
              ELSE @pos1             
END,
CASE
              WHEN @pos2 = 'A' THEN '10'
                WHEN @pos2 = 'B' THEN '11'
              WHEN @pos2 = 'C' THEN '12'
              WHEN @pos2 = 'D' THEN '13'
              WHEN @pos2 = 'E' THEN '14'
              WHEN @pos2 = 'F' THEN '15'
              ELSE @pos2             
END,
CASE
              WHEN @pos3 = 'A' THEN '10'
                WHEN @pos3 = 'B' THEN '11'
              WHEN @pos3 = 'C' THEN '12'
              WHEN @pos3 = 'D' THEN '13'
              WHEN @pos3 = 'E' THEN '14'
              WHEN @pos3 = 'F' THEN '15'
              ELSE @pos3             
END,
CASE
              WHEN @pos4 = 'A' THEN '10'
                WHEN @pos4 = 'B' THEN '11'
              WHEN @pos4 = 'C' THEN '12'
              WHEN @pos4 = 'D' THEN '13'
              WHEN @pos4 = 'E' THEN '14'
              WHEN @pos4 = 'F' THEN '15'
              ELSE @pos4             
END)

SET @decValue = (CONVERT(INT,(SELECT decPos4 FROM @decTab)))         +
                (CONVERT(INT,(SELECT decPos3 FROM @decTab))*16)      +
                (CONVERT(INT,(SELECT decPos2 FROM @decTab))*(16*16)) +
                (CONVERT(INT,(SELECT decPos1 FROM @decTab))*(16*16*16))

    RETURN @decValue

END


GO

 

 

Making use of the function, I found the decimal conversion, added that number of days to 01/01/1950 and FINALLY arrived at my “unpacked relative date”.  Here is the query I used to retrieve the formatted date, and the result set which was returned:

SELECT [packedDate] AS 'Hex Value',
       dbo.ftn_HexToDec([packedDate]) AS 'Decimal Value',
       CONVERT(DATE,DATEADD(day,dbo.ftn_HexToDec([packedDate]),'01/01/1950'),101) AS 'Relative String Date'
  FROM [dbo].[Output Table]

 

 

  queryResults  

This technique can be used any time you need to retrieve the hex value of a character string in SSIS.  The date example may be a bit difficult to understand at first, but with SSIS becoming the preferred tool for enterprise level integration for many companies, there is no doubt that developers will encounter these types of requirements with regularity in the future.

Please feel free to contact me if you have any questions. 

Posted on Tuesday, April 3, 2012 1:17 PM | Back to top


Comments on this post: Working with packed dates in SSIS

# re: Working with packed dates in SSIS
Requesting Gravatar...
Hello Jim,

I am facing a trouble in transforming the Data from main frame and load into sql tables using ssis.

I have a file which i can send you .It got some com data and also some hex fields... I wish you could help me.. If you can shoot me an email.. I can send you the file...
Left by Anji on Jun 26, 2013 4:46 PM

# re: Working with packed dates in SSIS
Requesting Gravatar...
I don't think you have any packed digits in this example. What you have is a 2-byte, unsigned integer stored in binary form. In your example you have converted the binary into Hexadecimal and then converted the Hexadecimal into decimal.

It might have been quicker to just convert Column1 straight into an unsigned integer and then DATEADD(day,[Column1_As_Int]),'01/01/1950').
Left by Tony Lung on Feb 26, 2014 2:04 PM

# re: Working with packed dates in SSIS
Requesting Gravatar...
Thanks for commenting Tony,

Perhaps packed was used too loosely since there really is no "packed decimal" data, like was used in the COBOL era. The example is showing how to get 4 bytes of output from 2 bytes of input. The 4 bytes of output represent a 4 digit hex number, which must be converted to decimal in order to find the relative date. I'm confused about the binary form you're referencing. Where are you seeing that in the example? Maybe I need to update it.

Can you attach an example of your idea? I'm always looking for an easier way to do things! Thanks again,

Jim
Left by Jim G on Feb 26, 2014 2:48 PM

# re: Working with packed dates in SSIS
Requesting Gravatar...
Thank you so much for the post!
I almost got it to work but getting the error 'An invalid character was found in text content'. Please advise.

Also should the SQL column be type of 'Int' for packed data?

My job depends on whether I complete the task or not.
Left by Julia on Mar 12, 2014 8:09 PM

# re: Working with packed dates in SSIS
Requesting Gravatar...
Thank you so much for this post!

Terry at http://www.taxiservicedurham.co.uk/
Left by Terry Shaw on Dec 01, 2017 3:03 PM

Your comment:
 (will show your gravatar)


Copyright © Jim Giercyk | Powered by: GeeksWithBlogs.net