Converting an EBCDIC file using SSIS

EBCIDC data is an 8-bit character encoded (code page) file used on IBM mainfram operating systems such as z/OS, OS/390, VM and VSE as well as IBM midrange computer operating systems such as OS/400. Unfortunately, SSIS out of the box can handle EBCDIC conversion but cannot handle a straight conversion from EBCDIC codepage (37) to ASCII codepage (1252).

Code Project has a fairly good article on using VB.NET to handle the conversion but here is how to handle and load the files using the SSIS interface.

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.  In this example, there is a simple EBCDIC file.

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

001

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: If the data in your Source data columns section is not readable, either you did not properly configure your connection manager, or you have Packed/Binary data in your file.  Packed data will be explained in a later chapter.

002

About Code Page Convert

Like I mentioned previously, SSIS (out of the box) can read EBCDIC data and write to an EBCDIC destination; but it cannot convert between code pages IE. 37 EBCDIC to 1252 ASCII. 

Here you will need to add a CodePageConvert transform.

Documentation regarding CodePageConvert and the downloadable msi can be found at: http://www.microsoft.com/downloads/details.aspx?familyid=9e56417e-23d1-4fd3-8d6d-61314faa2de3&displaylang=en

Your SSIS installation should have the CodePageConvert transform available in the data flow transformation toolbox.  If not, you will have to compile it in Visual Studio.  Once compiled, add the resultant 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…’

003

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

004

Now you should be able to add the ‘CodePageConvert’ transform to your Data Flow work area.

005

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

 Choose all columns from the Input Columns tab.

006

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 your flat file.  These should be the first columns in your create table statement.  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.

007

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.

008

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

009

Advertisements

Tags: , , , , , , , ,

2 Responses to “Converting an EBCDIC file using SSIS”

  1. EBCDIC Packed (comp-3) data SSIS « data integrity Says:

    […] Packed (comp-3) data SSIS By dataintegrity 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 […]

  2. Ben Says:

    If you ever need to handle other column types such as Redefine, Occurs, Occurs depending feel free to check out our component, Lysine. I would be happy to walk you through an example sometime.

    -Ben

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: