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

 

Export SQL Server Data with Integration Services to a Mainframe


By:   |   Read Comments   |   Related Tips: 1 | 2 | 3 | 4 | More > Integration Services Data Flow Transformations

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


Solution

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.

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.

This is how the SSIS package should look.

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

Select the CountryRegion table of the Person Schema.

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.

Convert Non-String types to Unicode String.

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.

Select Fixed width for the File Format.

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

Flat File Configuration With EBCDIC Code Page.

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

Flat File Advanced View.

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

View of the Exported Data in Notepad.

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.

Preview of Flat File Data.

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


Last Update:


signup button

next tip button



About the author





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     



Learn more about SQL Server tools