EBCDIC Packed (comp-3) data

I created a previous post regarding converting EBCDIC data (code page 37) using SSIS.  Another anamoly that can be found in EBCDIC data is a packed decimal field (also referred to as comp-3).  This post describes how to convert this data type.

To save space, a packed field stores two decimal digits in one byte.  A byte is comprised of 8 bits.  Those 8 bits are split into two parts (or nibbles). The decimal digits are stored in these nibbles (upper and lower).  The lower nibble of the last byte of the field will hold the sign (+,- or unsigned).

Below is an example:

01

To load these files drag and drop ‘Flat File Source’ onto your work area.  Click on the newly added ‘Flat File Source’ icon to set up your connection manager.

 Once the flat file connection manager editor is open, you can configure it to properly read your flat file.   

Code page:  37 IBM EBCDIC U.S./Canada Format: Fixed width Header row delimiter: {CR}{LF}

Click on the columns section in your editor to set up columns for your flat file.  For fixed files, this interface will look similar to SQL Server 2000’s DTS

As you would in DTS, measure out the proper row width and field lengths for each field.

NOTE:  Most of the data in the Source data columns section of your editor will look normal, but with packed data, you will see some odd characters.

02

 

Click on the columns section in your editor to set up columns for your flat file.  For fixed files, this interface will look similar to SQL Server 2000’s DTS

As you would in DTS, measure out the proper row width and field lengths for each field.

NOTE:  Most of the data in the Source data columns section of your editor will look normal, but with packed data, you will see some odd characters.

  03

 

Once you have properly configured the ‘Flat File Connection Manager’ and the ‘Flat File Source’, Right Click on the ‘Flat File Source’ and choose ‘Show Advanced Editor…’

From the ‘Advanced Editor’, choose the ‘Input and Output Properties’ tab.

 There, choose each column that contains packed data and in the Custom Properties section on the right side of the window you should see an option that reads “UseBinaryFormat”.  This option MUST be set to ‘True’ for any packed fields.

 NOTE:  The “UseBinaryFormat” is available for Service Pack 2 and above.

05

About Unpack Decimal 

Like I mentioned before, SSIS (out of the box) cannot “unpack” Packed data. Here, add an UnpackDecimal transform.

Documentation regarding UnpackDecimal and the downloadable msi can be found at:

http://www.microsoft.com/downloads/details.aspx?familyid=0e4bba52-cc52-4d89-8590-cda297ff7fbd&displaylang=en

Your SSIS installation should have the UnpackDecimal transform available in the data flow Transformation.  If not, you will have to compile it in Visual Studio.  Add the DLL to both

C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents

C:\WINDOWS\assembly

Then, right click on Data Flow Transformations in the Data Flow toolbox and select ‘Choose Items…’

Click on the SSIS Data Flow Items tab and click UnpackDecimal.

Click on the ‘UnpackDecimal’ icon and under the ‘Input Columns’ tab choose all columns that contain packed data that you wish to unpack.

07

Now, click on the ‘Input and Output Properties’ tab; under the Output Columns tree you can specify a scale (if any) for the packed data type.  If there is no scale, you can leave that value equal to zero.

08

Once you have properly configured your ‘UnpackDecimal’ transformation, create a ‘Derived Column’ transformation to convert the unpacked data back to a string so that you can have ‘CodePageConvert’ convert the new unpacked strings from 37 EBCDIC to 1252 ASCII.

 The ‘Derived Column’ transformation is located in the data flow toolbox.

Click on the ‘Derived Column’ transformation to open the editor.  From here, drag and drop each of the columns you unpacked onto your work area in the Expression column.  Once all of the packed fields are in the work area, set the data type equal to ‘string[DT_STR]’ and the Code Page equal to ‘37 EBCDIC’.  This will convert packed fields into EBCDIC strings.

11

Now you will have to convert the code page.  More on this topic can be found in a previous post Converting an EBCDIC file using SSIS.  Add a ‘CodePageConvert’ transform to your Data Flow work area.

12

Once you have added the ‘CodePageConvert’ transform, click on the icon to open the advanced editor. 

 This time, choose only columns that did not require unpacking, and all of the columns that came from the ‘Derived Column’ transform from the Input columns tab.

13

Once you have properly configured the CodePageConvert transform, create an OLE DB Destination in your data flow area.  Click the ‘New’ button next to the ‘Create Table or View” drop down. Here you will see a simple create table statement. 

First, remove the columns being read from the flat file and the decimal columns being read from UnpackDecimal (these columns have been unpacked but the code page is still EBCDIC). 

 Now, change the create table statement to reflect the correct name of the table you will be loading and also change the field names to the correct field names for that table.

14

In the ‘Mappings’ section of your OLE DB destination editor, link up your Input columns with Destination columns by dragging and dropping the input over to the desired output creating a link.

 Remember to link only the columns you transformed and not the original columns from the flat file or the columns from ‘Unpack Decimal’.

15

Once you have properly set up your mapping, return to your data flow work area and click Debug (or F5).

16

Advertisements

Tags: , , , , , , , ,

One Response to “EBCDIC Packed (comp-3) data”

  1. Douglas webb Says:

    do you plan on adding support for SQL2008 and newer anytime soon?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: