Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Importing Mainframe Data with SQL Server Integration Services


By:   |   Read Comments (9)   |   Related Tips: More > Integration Services Data Flow Transformations

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.

Sample Package View.

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.

Flat File Connection Main Page.

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.

Flat File Connection Columns View.

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.

Flat File Advanced View.

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.

Script Component Type Select Pop-Up.

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

Script Transformation Properties View.

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.

Script Code Screen Capture.

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.

Script Code Screen Capture.
Next Steps


Last Update:


signup button

next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, December 15, 2016 - 8:21:16 PM - Simon Carter Back To Top

 

This process is very messy. We use TextPipe Pro - you paste the copybook in, and it generates CSV, Tab or XML output for you. It can also handle variable record formats easily,


Wednesday, May 11, 2016 - 4:03:51 PM - Ken Back To Top

 The Article Importing Mainframe Data with SQL Server Integration Services with the vb script looks terrific. I need to use this process and going thru the excersize the character columns CustomerName and CustomeAddress end up being empty. I was wondering if anyone else is seeing this behavior. Still digging in but thought I would ask. Thanks All! 


Friday, September 04, 2015 - 5:29:02 PM - Azhar Back To Top

Great Post!  Sample file is still missing the data for the first three columns though.  I tested it out.

Azhar


Thursday, March 05, 2015 - 9:47:12 AM - George Lewycky Back To Top

Very useful material and explanation for the programmers unfamiliar with the IBM Mainframe & EBCDIC world!!!

I went through this on a major scale of files and found a useful alternative to converting EBCDIC to ASCII on the mainframe end to FTP to the Oracle/SQL Server end...

DFSORT has this useful feature not requiring you to write a program for each file. I have something I wrote in 2006 below  

http://georgenet.net/oracle/files/publications/DFSORT_feature_updated.pdf

George


Tuesday, December 23, 2014 - 9:15:26 PM - Daniel Farina Back To Top

Hi Eric!

The link pointing at the zip file has been updated. You can download it anytime.

Best Regards!


Saturday, December 20, 2014 - 12:37:49 AM - Daniel Farina Back To Top

Hi Eric!
I am sorry to say that I send the wrong sample text file to the editor. He will be upgrading the link soon.
Please accept my apologies


Friday, December 19, 2014 - 12:19:38 PM - eric81 Back To Top

That's what I thought , but when I attempt to run it getting some errors still...  I'll spend some time figuring it out .. thanks for your replies.

 

at System.Decimal.ToByte(Decimal value)

 

at SC_901b32f257b347b6b75477715eb13691.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

 

at SC_901b32f257b347b6b75477715eb13691.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

 

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer)

 

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

 


Thursday, December 18, 2014 - 8:24:17 PM - Daniel Farina Back To Top

Hi Eric!
Thank you very much for reading and commenting!
Yes! just replace the existing vb code.

Thanks!


Thursday, December 18, 2014 - 2:16:22 PM - eric81 Back To Top

Great article only issue is when I open the Script Editor where do I put the script you provided?   Do I overwrite the existing vb code with your example?


Learn more about SQL Server tools