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 and Host Integration Server


By:   |   Read Comments   |   Related Tips: More > Integration Services Development

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


Problem

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.

Solution

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]
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

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
      **----------------------------------------------
       01  CUSTOMER-RECORD.
           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
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 .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


Last Update:


next webcast 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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools