Export SQL Server Data with Integration Services to a Mainframe

By: Daniel Farina   |   Updated: 2015-03-05   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Integration Services Data Flow Transformations

Problem

In my previous tips (Introduction to Mainframe Data Storage for SSIS Developers and Importing Mainframe Data with SQL Server Integration Services) I introduced you to the mainframe world by explaining how to deal with zoned and packed decimal numbers and EBCDIC data when you need to import files from a Mainframes into a SQL Server database.

In this tip we are going the opposite way, we are going to send data from SQL Server to a Mainframe. Unless your mainframe has a DB2 database the only way to send data is with flat files. If you have read my previous tips in this series then you are aware of the different numeric formats on the mainframe environment like packed and zoned. When exporting data we don't need to care about those numeric formats. The reason is simple, as we do on SQL Server when we import a file; the mainframe host has to process the file in order to store and index it.

Solution

Create an EBCDIC File with Integration Services

Usually when we create a Flat File Connection to export data we leave the Locale and Code page fields with the default options. If you take a look at the Code page dropdown list you will see that Integration Services offers us the possibility to export data in EBCDIC format with different code pages to allow language specific characters. Now I will show you an example of an Integration Services package that exports data into an EBCDIC encoded text file.

For the purpose of this sample I will use the AdventureWorks database which you can download for free from CodePlex at this link.

The first step is to create an Integration Services package and add a Data Flow Task component in which you should add an OLEDB Source, a Flat File Destination and a Data Conversion components like on the next image.

On the OLEDB Source select the Person.CountryRegion table. This table has two string columns and one date column.

We need to convert the date column to a Unicode String data type. If we omit this step the resulting file will have the date column as an ASCII string, even when the flat file is configured to use an EBCDIC code page. This will render the file unusable because it will contain both ASCII and EBCDIC encoded fields.

Something to note is that this step should be done with every non string column the source has.

On the Flat File Destination we are going to create a new connection by clicking the New button. Then select the Fixed Width Format for the destination file.

When the Flat File Connection Manager Editor pops up enter a proper file name and select IBM EBCDIC as the Code Page.

On the Advanced View leave CountryRegionCode, Name and Text_ModifiedDate columns.

After executing the package if you open the output file on Notepad you will see gibberish text like on the next image.

In order to be sure that the data is in EBCDIC format, you can open the file with a text editor that supports the EBCDIC encoding, or you can use the Preview tab on the Flat File Connection Manager to view the contents of the file.

Sending File trough FTP

This is by far the easiest way to send a text file to a mainframe in EBCDIC format because the conversion between ASCII and EBCDIC is transparent to you if you configure the FTP client to send the file in text mode, usually referred in some FTP clients, including the windows ftp console command as ASCII mode. This may be confusing, but let me explain. When an FTP client sends a file in ASCII mode it means that the source file is treated as a text stream, every byte on the file is considered an ASCII character, not a Byte and the remote server also interprets the byte received as a character and performs the conversion.

A big advantage of this method is that you don't need to care about the code page of the host system. For those of you who didn't know, the EBCDIC format has different code pages to allow the use of special characters from different languages.

Next Steps

• You can download the SSIS package used for this tip here. I have used SQL Server Data Tools for Visual Studio 2013 which you can download for free from this link: www.microsoft.com/download/details.aspx?id=42313.
• Read my previous tip about Importing Mainframe Data with SSIS.
• If your mainframe system is running DB2 you can export data by creating a linked server following this tip: How to create a SQL Server link server to IBM DB2.
• 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