Importing Mainframe Data with SQL Server Integration Services and Host Integration Server

By:   |   Comments (2)   |   Related: More > Integration Services Development


In the previous tips of this series (Tip 1 and Tip 2) we have learned how to deal with mainframe data using a Script Component Transformation of SQL Server Integration Services. But that method could be tedious if you have to deal with many files or files with too many columns. In this tip I will show you what could be a life saver.


In the previous tips of this series (Tip 1 and Tip 2) about importing mainframe data I showed you how to deal with mainframe files by using a Script Component Transformation, but there is another way to deal with mainframe sources if you can afford the license costs: Microsoft Host Integration Server. For those of you who didnít know, Mainframe computers are also referred as Hosts. This product allows direct access to mainframes using SNA or TCP/IP protocols. Amongst its features is a .NET data provider that can be used as a host file source, but its usage is a bit trickier. In this tip, I will explain how to take advantage of this feature.

Setting up the Test Environment

First we are going to create a sample database to load a host file.

USE [master]

( NAME = N'SampleDB_file1', 
   FILENAME = N'E:\MSSQL\SampleDB_1.mdf',
   SIZE = 128MB , 
( NAME = N'SampleDB_log_file1',
    FILENAME = N'E:\MSSQL\SampleDB_1.ldf',
    SIZE = 64MB,
    MAXSIZE = 2048GB,

The next COBOL Copybook defines a Customer Record. You should copy its content into a text file because we will need this in the next steps. Also you can download this file and the sample text file from this link.

      **--  CUSTOMERS DATA
           05 CUSTOMERNAME                      PIC X(50).
           05 CUSTOMERADDRESS                   PIC X(50).
           05 CUSTOMERCATEGORY                  PIC 9 COMP-3.
           05 CUSTOMERBALANCE                   PIC 9(8)V99 COMP-3.

This is a script that will create a table to load the file.

USE SampleDB

 DROP 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

The .Net Host File Project

In order to use the .NET Host File Provider we need to create a Host File Project. This will convert the COBOL Copybook into a Host Integration Definition XML file (*.hidx) that will serve as the metadata for the .NET Host File Provider. To do so, create a Visual Studio project like on the next image.

New Host File Project.

Next, on the Solution Explorer window right click on the solutionís file and select Add Host File Definition from the Contextual Menu.

Right Click to Add a Host File Definition.

When the Host File Definition Wizard opens, you have to select System Z as the Host Environment and COBOL for Language and hit the Create button.

Screen Capture of Host File Definition Wizard.

Finally we have the Host Integration Definition XML file (*.hidx) to import our COBOL Copybook. You will see an embedded window like the one on the next image showing a tree with three folders named Tables, Schemas and Unions respectively.

Host Project View.

The next step is to right click into the root node and select Import Host Definition from the contextual menu.

Import Host File Definition.

When the Import COBOL wizard opens itís just a matter of clicking the Browse button to load our COBOL Copybook. For the purpose of this tip we are going to select Simple Redefinition from the REDEFINE combo box and check the Use Importer Defaults checkbox. When dealing with a real Mainframe file you need to clear the redefinitions and sets in order to use the .NET Host File Provider.

View of Import CCOBOL Source.

After successfully importing the COBOL copybook you will see that the Schemas folder has been loaded with the record information contained in the copybook.

Host File Treeview.

Now we are one step away to complete this Host Project that will allow us to use the .NET Host File Adapter. The remaining step is to add a table to the project. To do so, right click on the Tables folder and select Add Table from the contextual menu. Then go to the Properties window and setup the properties. The Alias Property will be the name of the table we will see when we configure the .NET Host File Adapter. On the Host File Name Property we need to specify the name of the file we are going to import. This is something to note, since we wonít be able to change this file name on the Integration Services project.  For the Schema Property, it is only possible to select one of the available options on the dropdown list, in this case is CUSTOMER_RECORD.

Table Properties.

The Integration Services Project for working with Host File Adapter

This is the part that we are more comfortable with. Compared with the SSIS projects of my previous tips in this series in terms of complexity, this is the simplest SSIS package. The only components that we will use are a Data Flow Task with an ADO.NET Source and any destination you want, in my case I choose to use an OLEDB Destination.

A View of The SSIS Package.

In order to setup the ADO.NET connection, select MsHostFileClient Data Provider from the Provider combo box and then click on the Configure button. Donít try to assign a name to the connection now, if you do so you wonít be able to select a source platform on the Data Source Wizard other than an SNA connection.

MsHostFileClient Data Provider.

If you followed the previous steps you will see the Data Source Wizard that prompts us for a source platform: Mainframe File System, AS/400 File System and Local File. We are going to select the last option and click next.

Step 1 of Host File Client Data Source Wizzard.

The next wizard window asks us about a local folder that contains the text file to be imported. Also asks for a Metadata Definition File, here is when we have to specify the Host Integration Definition XML file (*.HIDX) we created on the Host File Project.

Step 2 of Host File Client Data Source Wizzard.

The last screen of the wizard contains two combo boxes to setup the host file encoding and the PC code page.

Step 3 of Host File Client Data Source Wizzard.

After those steps the wizard will show us the completion screen informing us that we have successfully configured the data source.

Host File Client Data Source Wizzard Finish Screen.

Now assign a name to this connection, for example "Customers" and hit the OK button.

Assing a Name to This Connection.

We are now ready to configure the ADO.NET source by selecting the connection manager we recently configured. As you can see on the next image we can access the file as a table, just like we do with any ADO.NET source.

ADO NET Source Connection.

Even more amazing is the fact that we can click the preview button and get a view of the fileís content.

Preview of File Data.

The last step is to map the source columns with the ones of the destination table.

Map the Source and Destination Columns.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

View all my tips

Comments For This Article

Thursday, October 17, 2019 - 7:06:12 AM - MOHAMMED SHAHEEN Back To Top (82806)

I am trying to import the mainframe data to SQL Server using SSIS as something similar mentioned in article

Please let me know if I want to import file directly from mainframe systems instead of local folder, what connection property need to set?

I am using HIS 2013 and SSMS 16.5.3 for SSIS.

Friday, May 24, 2019 - 3:05:23 AM - harsha Back To Top (80184)

Could you please let me know if i want to import file directly from mainframe systems instead of local folder, what connection propertied i need to set?

get free sql tips
agree to terms