Importing Mainframe Data with SSIS

By: Daniel Farina | Comments (9) | Related: More > Integration Services Data Flow Transformations

Problem

Your boss gives you a text file to import into a SQL Server database. It looks like a piece of cake, but you suddenly realize that the text file is in EBCDIC format. In this tip I will show you how to proceed to convert EBCDIC data on the fly in a Data Flow Task with a Script Transformation in SQL Server Integration Services.

Solution

Importing text files with SQL Server Integration Services can become complicated if you need to deal with mainframe sources. Even when SSIS allows us to select the code page for the file to import sometimes it is not that easy. For example, when you have binary fields on a text file, like COBOL's COMP fields. In short, the whole battery of tools Integration Services offers to us, sometimes it is not enough. But for those cases, SSIS offers us the possibility to extend its functionality with custom .NET code. From that point on, the limit is only your imagination.

Scripting in SQL Server Integration Services

There are two kinds of Scripting options on Integration Services; those are the Script Task and the Script Component. They may look the same at first, but the difference relies on its usage scenarios. The Script Task is used as a Control Flow task and the Script Component is used as a Data Flow Transformation. In this tip I will cover the last one and I will guide you to setup a data transformation with an example.

The Mainframe Numeric Data Types

There are different ways to handle decimal values on computers systems and each platform uses its own way based on its operating system and hardware. Mainframe uses the Binary Coded Decimal (BCD) encoding for decimal numbers. It consists of representing a decimal digit with a fixed number of bits, usually 8 for uncompressed numbers and 4 for compressed or packed ones. In this tip, I will refer to the uncompressed or zoned numbers.

If the field is defined as unsigned, like a COBOL PIC 9999, it is stored in plain EBCDIC format. For example, the number 1234 will be stored as 0xF1 0xF2 0xF3 0xF4, the hexadecimal codes for numbers 1, 2, 3 and 4 in the EBCDIC charset respectively. The problem arises if we are dealing with signed decimal numbers, like COBOL PIC S9999, in which the sign is encoded in the high order number of the first byte as a hexadecimal C for positive numbers and a hexadecimal D for negative. Some people say that back in the day of punched cards C was for credit and D for debit. So, according to the EBCDIC character map, the signed position of the field will display as a character value of "A-I" for positive numbers and "J-R" for negative numbers. Also the number zero will be represented as a "{" and "}", the character codes of 0xC0 and 0xD0 respectively.

The EBCDIC zoned number can be converted to an ASCII zoned number. In that case, the numbers are in the hex range of 0x30 to 0x39, the ASCII representation for numbers 0 to 9 and the sign digit is encoded as X'3n' for positive numbers and X'7n' for negatives. To avoid confusion let's call this variant as Modified Zoned. On the next image you will see an example of both a positive and negative number representation.

Zoned Number Representation on EBCDIC and ASCII Format.

But it doesn't end here. If the file is transmitted over FTP, it could be done in two different modes; binary or ASCII. Usually text files are transferred in ASCII mode. The main difference is that ASCII mode translates the data based on its character value, for instance, if we send the character "A" with EBCDIC code 0xC1, on the other side we will receive the character "A" with ASCII code 0x41. Notice that the binary values differ between the source and the destination. The next table will show us the possible values we will get for the signed digits.

Digit	EBCDIC Hex	EBCDIC Display	Strict Zoned ASCII Hex	Strict Zoned ASCII Display	Modified Zoned ASCII Hex	Modified Zoned ASCII Display
+0	0xC0	{	0x30	0	0x7B	{
+1	0xC1	A	0x31	1	0x41	A
+2	0xC2	B	0x32	2	0x42	B
+3	0xC3	C	0x33	3	0x43	C
+4	0xC4	D	0x34	4	0x44	D
+5	0xC5	E	0x35	5	0x45	E
+6	0xC6	F	0x36	6	0x46	F
+7	0xC7	G	0x37	7	0x47	G
+8	0xC8	H	0x38	8	0x48	H
+9	0xC9	I	0x39	9	0x49	I
-0	0xD0	}	0x70	p	0x7D	}
-1	0xD1	J	0x71	q	0x4A	J
-2	0xD2	K	0x72	r	0x4B	K
-3	0xD3	L	0x73	s	0x4C	L
-4	0xD4	M	0x74	t	0x4D	M
-5	0xD5	N	0x75	u	0x4E	N
-6	0xD6	O	0x76	v	0x4F	O
-7	0xD7	P	0x77	w	0x50	P
-8	0xD8	Q	0x78	x	0x51	Q
-9	0xD9	R	0x79	y	0x52	R

Sample Data Loading with SSIS

First we are going to create a sample database.

USE [master]
GO

CREATE DATABASE [SampleDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDB_file1', 
   FILENAME = N'E:\MSSQL\SampleDB_1.mdf',
   SIZE = 128MB , 
   MAXSIZE = UNLIMITED, 
   FILEGROWTH = 64MB) 
 LOG ON 
( NAME = N'SampleDB_log_file1',
    FILENAME = N'E:\MSSQL\SampleDB_1.ldf',
    SIZE = 64MB,
    MAXSIZE = 2048GB,
    FILEGROWTH = 32MB)
GO

We need some sample data in a text file to load into our previously created table.From this link you can download a sample text file.

Next is an example of what you will see in a COBOL Copy File (a file that contains the record definition) for the file of the previous step. I added a brief description about the data types just in case you are not familiar with Cobol syntax.

CustomerName  pic X(50)   -- Character field of 50 positions
CustomerAddress  pic X(50)   -- Character field of 50 positions
CustomerCategory pic 9    -- Number 0 - 9
CustomerBalance  pic S9(8)V99   -- Eight digits and two decimal positions

A suitable definition for a table to load this file is next.

USE SampleDB
GO

IF OBJECT_ID('Customers','U') IS NOT NULL
BEGIN
 DROP TABLE Customers
END
GO

CREATE TABLE Customers
(
   CustomerID   INT IDENTITY (1, 1) NOT NULL,
   CustomerName   VARCHAR (50) NOT NULL,
   CustomerAddress  VARCHAR (50) NOT NULL,
   CustomerCategory  TINYINT  NOT NULL,
   CustomerBalance  DECIMAL(10,2) NOT NULL
   PRIMARY KEY CLUSTERED (CustomerID)
)

The next step is to create a SSIS project and add a Data Flow task, a Flat File Connection to the file created in the previous step and an OLEDB connection to our sample database as shown in the next image.

Then set up the flat file connection with a fixed width format, an ANSI code page and select {CR}{LF} as the row delimiter as shown below.

In the Columns view we see our file as rubbish data because it's in EBCDIC format. Also notice that the numeric fields end with a letter.

Because of this and for simplicity, it's better for us to use the Advanced view to set up the layout of our text file. According to the file's record definition we will configure columns Name and Address with an input and output of 50 and a Data Type of String. We must also set the TextQualified option to False.

For the Category and Balance fields we need to select String as the Data Type; and 1 and 10 for the lengths respectively.

Now we need to drop into our package a Flat File Source, an OLEDB Destination and a Script Component. After dropping the Script Component a window will pop up asking us how we want to use this Script Component. Since we are going to perform a transformation we check the Transformation radio button and click the OK button.

On the Script Tab select Visual Basic as the Script Language.

The next step is to setup the Inputs and Outputs tab. We have to add four columns into the Output folder tree; those columns will be where we will return the transformed data to be inserted into our database.

Script Transformation Input and Output Setup.

After adding the columns we have to configure the output data types for each. According to our file definition, the data types for CustomerName and CustomerAddress are String with a length of 50. Also the data types for CustomerCategory and CustomerBalance are Single Byte Unsigned Integer and Numeric with Precision 10 and Scale 2 respectively. The next image may be used as a guide.

Output Columns Configuration Properties.

Now, on the Script Tab, press the Edit Script button. Another instance of Visual Studio will arise in which we will paste the next script code.

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
#End Region

' This is the class to which to add your code.  Do not change the name, attributes, or parent
' of this class.
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent

    Private ASCIItoEBCDIC As String = Chr(&H0) + Chr(&H1) + Chr(&H2) + Chr(&H3) + Chr(&H37) + Chr(&H2D) + Chr(&H2E) + Chr(&H2F) + Chr(&H16) + Chr(&H5) + Chr(&H25) + Chr(&HB) + _
                            Chr(&HC) + Chr(&HD) + Chr(&HE) + Chr(&HF) + Chr(&H10) + Chr(&H11) + Chr(&H12) + Chr(&H13) + Chr(&H3C) + Chr(&H3D) + Chr(&H32) + Chr(&H26) + _
                            Chr(&H18) + Chr(&H19) + Chr(&H3F) + Chr(&H27) + Chr(&H1C) + Chr(&H1D) + Chr(&H1E) + Chr(&H1F) + Chr(&H40) + Chr(&H5A) + Chr(&H7F) + Chr(&H7B) + _
                            Chr(&H5B) + Chr(&H6C) + Chr(&H50) + Chr(&H7D) + Chr(&H4D) + Chr(&H5D) + Chr(&H5C) + Chr(&H4E) + Chr(&H6B) + Chr(&H60) + Chr(&H4B) + Chr(&H61) + _
                            Chr(&HF0) + Chr(&HF1) + Chr(&HF2) + Chr(&HF3) + Chr(&HF4) + Chr(&HF5) + Chr(&HF6) + Chr(&HF7) + Chr(&HF8) + Chr(&HF9) + Chr(&H7A) + Chr(&H5E) + _
                            Chr(&H4C) + Chr(&H7E) + Chr(&H6E) + Chr(&H6F) + Chr(&H7C) + Chr(&HC1) + Chr(&HC2) + Chr(&HC3) + Chr(&HC4) + Chr(&HC5) + Chr(&HC6) + Chr(&HC7) + _
                            Chr(&HC8) + Chr(&HC9) + Chr(&HD1) + Chr(&HD2) + Chr(&HD3) + Chr(&HD4) + Chr(&HD5) + Chr(&HD6) + Chr(&HD7) + Chr(&HD8) + Chr(&HD9) + Chr(&HE2) + _
                            Chr(&HE3) + Chr(&HE4) + Chr(&HE5) + Chr(&HE6) + Chr(&HE7) + Chr(&HE8) + Chr(&HE9) + Chr(&HAD) + Chr(&HE0) + Chr(&HBD) + Chr(&H5F) + Chr(&H6D) + _
                            Chr(&H79) + Chr(&H81) + Chr(&H82) + Chr(&H83) + Chr(&H84) + Chr(&H85) + Chr(&H86) + Chr(&H87) + Chr(&H88) + Chr(&H89) + Chr(&H91) + Chr(&H92) + _
                            Chr(&H93) + Chr(&H94) + Chr(&H95) + Chr(&H96) + Chr(&H97) + Chr(&H98) + Chr(&H99) + Chr(&HA2) + Chr(&HA3) + Chr(&HA4) + Chr(&HA5) + Chr(&HA6) + _
                            Chr(&HA7) + Chr(&HA8) + Chr(&HA9) + Chr(&HC0) + Chr(&H4F) + Chr(&HD0) + Chr(&HA1) + Chr(&H7) + Chr(&H20) + Chr(&H21) + Chr(&H22) + Chr(&H23) + _
                            Chr(&H24) + Chr(&H15) + Chr(&H6) + Chr(&H17) + Chr(&H28) + Chr(&H29) + Chr(&H2A) + Chr(&H2B) + Chr(&H2C) + Chr(&H9) + Chr(&HA) + Chr(&H1B) + _
                            Chr(&H30) + Chr(&H31) + Chr(&H1A) + Chr(&H33) + Chr(&H34) + Chr(&H35) + Chr(&H36) + Chr(&H8) + Chr(&H38) + Chr(&H39) + Chr(&H3A) + Chr(&H3B) + _
                            Chr(&H4) + Chr(&H14) + Chr(&H3E) + Chr(&HE1) + Chr(&H41) + Chr(&H42) + Chr(&H43) + Chr(&H44) + Chr(&H45) + Chr(&H46) + Chr(&H47) + Chr(&H48) + _
                            Chr(&H49) + Chr(&H51) + Chr(&H52) + Chr(&H53) + Chr(&H54) + Chr(&H55) + Chr(&H56) + Chr(&H57) + Chr(&H58) + Chr(&H59) + Chr(&H62) + Chr(&H63) + _
                            Chr(&H64) + Chr(&H65) + Chr(&H66) + Chr(&H67) + Chr(&H68) + Chr(&H69) + Chr(&H70) + Chr(&H71) + Chr(&H72) + Chr(&H73) + Chr(&H74) + Chr(&H75) + _
                            Chr(&H76) + Chr(&H77) + Chr(&H78) + Chr(&H80) + Chr(&H8A) + Chr(&H8B) + Chr(&H8C) + Chr(&H8D) + Chr(&H8E) + Chr(&H8F) + Chr(&H90) + Chr(&H9A) + _
                            Chr(&H9B) + Chr(&H9C) + Chr(&H9D) + Chr(&H9E) + Chr(&H9F) + Chr(&HA0) + Chr(&HAA) + Chr(&HAB) + Chr(&HAC) + Chr(&H4A) + Chr(&HAE) + Chr(&HAF) + _
                            Chr(&HB0) + Chr(&HB1) + Chr(&HB2) + Chr(&HB3) + Chr(&HB4) + Chr(&HB5) + Chr(&HB6) + Chr(&HB7) + Chr(&HB8) + Chr(&HB9) + Chr(&HBA) + Chr(&HBB) + _
                            Chr(&HBC) + Chr(&H6A) + Chr(&HBE) + Chr(&HBF) + Chr(&HCA) + Chr(&HCB) + Chr(&HCC) + Chr(&HCD) + Chr(&HCE) + Chr(&HCF) + Chr(&HDA) + Chr(&HDB) + _
                            Chr(&HDC) + Chr(&HDD) + Chr(&HDE) + Chr(&HDF) + Chr(&HEA) + Chr(&HEB) + Chr(&HEC) + Chr(&HED) + Chr(&HEE) + Chr(&HEF) + Chr(&HFA) + Chr(&HFB) + _
                            Chr(&HFC) + Chr(&HFD) + Chr(&HFE) + Chr(&HFF)

    Private EBCDICtoASCII As String = Chr(&H0&) + Chr(&H1&) + Chr(&H2&) + Chr(&H3&) + Chr(&H9C&) + Chr(&H9&) + Chr(&H86&) + Chr(&H7F&) + Chr(&H97&) + Chr(&H8D&) + Chr(&H8E&) + _
                                    Chr(&HB&) + Chr(&HC&) + Chr(&HD&) + Chr(&HE&) + Chr(&HF&) + Chr(&H10&) + Chr(&H11&) + Chr(&H12&) + Chr(&H13&) + Chr(&H9D&) + Chr(&H85&) + _
                                    Chr(&H8&) + Chr(&H87&) + Chr(&H18&) + Chr(&H19&) + Chr(&H92&) + Chr(&H8F&) + Chr(&H1C&) + Chr(&H1D&) + Chr(&H1E&) + Chr(&H1F&) + Chr(&H80&) + _
                                    Chr(&H81&) + Chr(&H82&) + Chr(&H83&) + Chr(&H84&) + Chr(&HA&) + Chr(&H17&) + Chr(&H1B&) + Chr(&H88&) + Chr(&H89&) + Chr(&H8A&) + Chr(&H8B&) + _
                                    Chr(&H8C&) + Chr(&H5&) + Chr(&H6&) + Chr(&H7&) + Chr(&H90&) + Chr(&H91&) + Chr(&H16&) + Chr(&H93&) + Chr(&H94&) + Chr(&H95&) + Chr(&H96&) + _
                                    Chr(&H4&) + Chr(&H98&) + Chr(&H99&) + Chr(&H9A&) + Chr(&H9B&) + Chr(&H14&) + Chr(&H15&) + Chr(&H9E&) + Chr(&H1A&) + Chr(&H20&) + Chr(&HA0&) + _
                                    Chr(&HA1&) + Chr(&HA2&) + Chr(&HA3&) + Chr(&HA4&) + Chr(&HA5&) + Chr(&HA6&) + Chr(&HA7&) + Chr(&HA8&) + Chr(&HD5&) + Chr(&H2E&) + Chr(&H3C&) + _
                                    Chr(&H28&) + Chr(&H2B&) + Chr(&H7C&) + Chr(&H26&) + Chr(&HA9&) + Chr(&HAA&) + Chr(&HAB&) + Chr(&HAC&) + Chr(&HAD&) + Chr(&HAE&) + Chr(&HAF&) + _
                                    Chr(&HB0&) + Chr(&HB1&) + Chr(&H21&) + Chr(&H24&) + Chr(&H2A&) + Chr(&H29&) + Chr(&H3B&) + Chr(&H5E&) + Chr(&H2D&) + Chr(&H2F&) + Chr(&HB2&) + _
                                    Chr(&HB3&) + Chr(&HB4&) + Chr(&HB5&) + Chr(&HB6&) + Chr(&HB7&) + Chr(&HB8&) + Chr(&HB9&) + Chr(&HE5&) + Chr(&H2C&) + Chr(&H25&) + Chr(&H5F&) + _
                                    Chr(&H3E&) + Chr(&H3F&) + Chr(&HBA&) + Chr(&HBB&) + Chr(&HBC&) + Chr(&HBD&) + Chr(&HBE&) + Chr(&HBF&) + Chr(&HC0&) + Chr(&HC1&) + Chr(&HC2&) + _
                                    Chr(&H60&) + Chr(&H3A&) + Chr(&H23&) + Chr(&H40&) + Chr(&H27&) + Chr(&H3D&) + Chr(&H22&) + Chr(&HC3&) + Chr(&H61&) + Chr(&H62&) + Chr(&H63&) + _
                                    Chr(&H64&) + Chr(&H65&) + Chr(&H66&) + Chr(&H67&) + Chr(&H68&) + Chr(&H69&) + Chr(&HC4&) + Chr(&HC5&) + Chr(&HC6&) + Chr(&HC7&) + Chr(&HC8&) + _
                                    Chr(&HC9&) + Chr(&HCA&) + Chr(&H6A&) + Chr(&H6B&) + Chr(&H6C&) + Chr(&H6D&) + Chr(&H6E&) + Chr(&H6F&) + Chr(&H70&) + Chr(&H71&) + Chr(&H72&) + _
                                    Chr(&HCB&) + Chr(&HCC&) + Chr(&HCD&) + Chr(&HCE&) + Chr(&HCF&) + Chr(&HD0&) + Chr(&HD1&) + Chr(&H7E&) + Chr(&H73&) + Chr(&H74&) + Chr(&H75&) + _
                                    Chr(&H76&) + Chr(&H77&) + Chr(&H78&) + Chr(&H79&) + Chr(&H7A&) + Chr(&HD2&) + Chr(&HD3&) + Chr(&HD4&) + Chr(&H5B&) + Chr(&HD6&) + Chr(&HD7&) + _
                                    Chr(&HD8&) + Chr(&HD9&) + Chr(&HDA&) + Chr(&HDB&) + Chr(&HDC&) + Chr(&HDD&) + Chr(&HDE&) + Chr(&HDF&) + Chr(&HE0&) + Chr(&HE1&) + Chr(&HE2&) + _
                                    Chr(&HE3&) + Chr(&HE4&) + Chr(&H5D&) + Chr(&HE6&) + Chr(&HE7&) + Chr(&H7B&) + Chr(&H41&) + Chr(&H42&) + Chr(&H43&) + Chr(&H44&) + Chr(&H45&) + _
                                    Chr(&H46&) + Chr(&H47&) + Chr(&H48&) + Chr(&H49&) + Chr(&HE8&) + Chr(&HE9&) + Chr(&HEA&) + Chr(&HEB&) + Chr(&HEC&) + Chr(&HED&) + Chr(&H7D&) + _
                                    Chr(&H4A&) + Chr(&H4B&) + Chr(&H4C&) + Chr(&H4D&) + Chr(&H4E&) + Chr(&H4F&) + Chr(&H50&) + Chr(&H51&) + Chr(&H52&) + Chr(&HEE&) + Chr(&HEF&) + _
                                    Chr(&HF0&) + Chr(&HF1&) + Chr(&HF2&) + Chr(&HF3&) + Chr(&H5C&) + Chr(&H9F&) + Chr(&H53&) + Chr(&H54&) + Chr(&H55&) + Chr(&H56&) + Chr(&H57&) + _
                                    Chr(&H58&) + Chr(&H59&) + Chr(&H5A&) + Chr(&HF4&) + Chr(&HF5&) + Chr(&HF6&) + Chr(&HF7&) + Chr(&HF8&) + Chr(&HF9&) + Chr(&H30&) + Chr(&H31&) + _
                                    Chr(&H32&) + Chr(&H33&) + Chr(&H34&) + Chr(&H35&) + Chr(&H36&) + Chr(&H37&) + Chr(&H38&) + Chr(&H39&) + Chr(&HFA&) + Chr(&HFB&) + Chr(&HFC&) + _
                                    Chr(&HFD&) + Chr(&HFE&) + Chr(&HFF&)


    'This method is called once for every row that passes through the component from Input0.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Row.CustomerName = Translate(Row.Name, EBCDICtoASCII)
        Row.CustomerAddress = Translate(Row.Address, EBCDICtoASCII)
        Row.CustomerCategory = Convert.ToByte(COBOLZonedPicToDecimal(Row.Category, 0))
        Row.CustomerBalance = COBOLZonedPicToDecimal(Row.Balance, 2)

    End Sub
    Public Function Translate(ByVal str As String, sConversionTable As String) As String

        Dim Temp As String, I As Int32
        Temp = Space(Len(Str))
        For I = 1 To InStr(Str, vbCrLf) - 1
            Mid(Temp, I, 1) = Mid(sConversionTable, Asc(Mid(Str, I, 1)) + 1, 1)
        Next
        Translate = Temp
    End Function


    Public Function DecimalToCOBOLZonedPic(ByVal dNumber As Decimal, ByVal iPadLength As Integer, ByVal iDecimalPlaces As Integer, ByVal bModifiedZoned As Boolean) As String
        Dim isNegative As Boolean = False
        Dim strNumber As String
        Dim sPositive As String = "{ABCDEFGHI"
        Dim sNegativeMod As String = "}JKLMNOPQR"
        Dim sNegativeStrict As String = "pqrstuvwxy"

        ' Determine the sign
        If dNumber < 0 Then
            isNegative = True
            dNumber = -dNumber
        End If

        dNumber = Convert.ToDecimal(dNumber * (10 ^ iDecimalPlaces))

        strNumber = dNumber.ToString

        If strNumber.IndexOf(".") > -1 Then
            ' Truncate remaining decimal places
            strNumber = strNumber.Substring(0, strNumber.IndexOf("."))
        End If

        ' Pad with leading zeros
        If strNumber.Length < iPadLength Then
            strNumber = strNumber.PadLeft(iPadLength, "0"c)
        End If

        Dim lastDigit As Integer = Convert.ToInt32(strNumber.Substring(strNumber.Length - 1, 1))

        If bModifiedZoned Then
            If isNegative Then
                strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeMod.Substring(lastDigit, 1).ToString
            Else
                strNumber = strNumber.Substring(0, strNumber.Length - 1) & sPositive.Substring(lastDigit, 1).ToString
            End If
        Else
            If isNegative Then
                strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeStrict.Substring(lastDigit, 1).ToString
            End If
        End If

        Return strNumber
    End Function

    Public Function COBOLZonedPicToDecimal(ByVal strNumber As String, ByVal decimalPlaces As Integer) As Decimal
        Dim sZoneChar As String
        Dim convertedNumber As Decimal
        Dim sPositive As String = "{ABCDEFGHI"
        Dim sNegativeMod As String = "}JKLMNOPQR"
        Dim sNegativeStrict As String = "pqrstuvwxy"

        strNumber = strNumber.Trim

        If strNumber = "" Then
            Return 0
        End If

        sZoneChar = strNumber.Substring(strNumber.Length - 1)

        Select Case True
            Case sPositive.IndexOf(sZoneChar) > -1
                strNumber = strNumber.Substring(0, strNumber.Length - 1) & sPositive.IndexOf(sZoneChar)
                convertedNumber = Convert.ToDecimal(strNumber)

            Case sNegativeMod.IndexOf(sZoneChar) > -1
                strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeMod.IndexOf(sZoneChar)
                convertedNumber = -Convert.ToDecimal(strNumber)

            Case sNegativeStrict.IndexOf(sZoneChar) > -1
                strNumber = strNumber.Substring(0, strNumber.Length - 1) & sNegativeStrict.IndexOf(sZoneChar)
                convertedNumber = -Convert.ToDecimal(strNumber)
            Case Else
                convertedNumber = Convert.ToDecimal(strNumber)
        End Select

        Return Convert.ToDecimal(convertedNumber / (10 ^ decimalPlaces))
    End Function

End Class

How the Script Works

When the Visual Studio Editor opens, it gives us a Class Template to add our code. This template defines a method called Input0_ProcessInputRow that is executed once for every row. The method receives an object variable row of type Input0Buffer that includes our input and output columns as methods. From now on, it's only matter of assigning a method to the corresponding converted value, like on the next image.

To convert from EBCDIC to ASCII, I created a function that receives as parameters a translation table and the value to be translated. It works by replacing a character with its corresponding position in the table, much like a hash function does to find a match. In fact it is a collision free hash function.

Also to convert numeric values I included two functions:

COBOLZonedPicToDecimal: Receives a string representing the zoned number to be converted and an integer to specify the decimal places. It converts both EBCDIC and ASCII encoded zoned numbers.
DecimalToCOBOLZonedPic: Receives a decimal number to be converted, an integer with the padding length, an integer with the decimal places and a Boolean value used to specify if the given number should be converted to the modified or strict zoned definition.

After executing the package you can perform a SELECT on the destination table and you will see that 1000 rows have been loaded into our sample table.

Next Steps

You can download the SSIS package and the sample text file used for this tip here.
For more information about conversion between data types check out this tip: SQL Server Integration Services Data Type Conversion Testing.
Browse Integration Services Data Flow Transformations Tips Category for more tips about Data Flow Transformations.
If you want to enrich your SSIS packages and make them look more professional, take a look at Integration Services Best Practices Tips Category.
Also check out SQL Server Business Intelligence Tips and Tricks.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Importing Mainframe Data with SQL Server Integration Services