none
Host Integration Server and SQL Server (SSIS) RRS feed

  • Question

  • Hi,

    Is it possible with HIS2010/BizTalk to interogate off platform VSAM files and DB2 files?  

    Ideally what I want to be able to do is using SSIS, read from an EBCIDIC VSAM file containing Packed Decimals, multiple rows per record and repeating data within a section of the file and store this in SQL Server as relational data.  I would ideally like to import the COBOL copybook to define the schema and mapping.

    In addition, I will also need to be able to write packed decimal values to a file (that will also contain non packed data) convert it to EBCIDIC and then have it loaded onto a AS/400 mainframe (hosted by a 3rd party).

    Forgive me if this is the wrong forum for my question and please feel free to redirect or point me in the right direction

    Barry

     

    Wednesday, October 12, 2011 4:17 PM

Answers

  • Yes, it is possible.

    HIS 2010 includes the following that provide access to host files:

    - Microsoft OLE DB Provider for AS/400 and VSAM
    - Microsoft ADO.NET Data Provider for Host Files
    - Microsoft BizTalk Adapter for Host Files

    The Microsoft OLE DB Provider for AS/400 and VSAM (SNAOLEDB) is not fully compatible with (a) SQL Server Distributed Queries or (b) SQL Server Integration Services.

    The recommendation would be to take a look at the ADO.NET Data Provider for Host Files. You can use any of the folowing to call this provider:

    - custom C# application

    - SSIS ADO.NET Source/Destination (Using VS BIDS and SQL Server 2008 (R2), the Microsoft ADO.NET Data Provider for Host Files can work within an SSIS package using an ADO.NET Destination or ADO.NET Source. In this case, you can only work with flat and single record formats (no nested, no dynamic record formats). )

    - BizTalk Server

    The Microsoft BizTalk Adapter for Host Files can easily work with the BizTalk Adapter for SQL Server to read/write to VSAM, with full support for nested records. See http://technet.microsoft.com/en-us/library/bb726925(BTS.10).aspx

    HIS 2010 also includes the ability to read and write host files offline. See http://msdn.microsoft.com/en-us/library/gg167635(v=BTS.70).aspx:

    Host Files Offline Writer

    HIS developers use the ADO.NET Provider for Host Files to access and integrate information stored in existing host record-oriented files with new solutions that are based on the .NET Framework. When accessing information stored in z/OS host file systems, the data provider must connect across an SNA APPC session using high performance routing over internet protocol (HPR/IP) network. Some enterprises have standardized on TCP/IP and cannot upgrade their host systems to support HPR/IP. Previously, HIS offered an improved host file provider that supports off-line reading of binary files, which allowed for use of file transfer protocol (FTP) as a means to read host data without an SNA APPC session. However, there was no solution for writing data back to the host system. In HIS 2010, the enhanced host file provider supports off-line writing to binary files. This enables enterprise developers to read and write host data without a live connection to the host file system. When operating in off-line reader/writer mode, the ADO.NET Data Provider supports the same record access methods (sequential, keyed, and combined) and record formats based on local metadata produced by the host file designer for Visual Studio.

    THe following is the main link to the HIS 2010 information on MSDN:

    http://msdn.microsoft.com/en-us/library/gg162969(v=BTS.70).aspx

    Thanks...

     


    Stephen Jackson - MSFT
    • Marked as answer by Barry.Hostead Friday, October 14, 2011 8:05 PM
    Friday, October 14, 2011 4:20 PM

All replies

  • Yes, it is possible.

    HIS 2010 includes the following that provide access to host files:

    - Microsoft OLE DB Provider for AS/400 and VSAM
    - Microsoft ADO.NET Data Provider for Host Files
    - Microsoft BizTalk Adapter for Host Files

    The Microsoft OLE DB Provider for AS/400 and VSAM (SNAOLEDB) is not fully compatible with (a) SQL Server Distributed Queries or (b) SQL Server Integration Services.

    The recommendation would be to take a look at the ADO.NET Data Provider for Host Files. You can use any of the folowing to call this provider:

    - custom C# application

    - SSIS ADO.NET Source/Destination (Using VS BIDS and SQL Server 2008 (R2), the Microsoft ADO.NET Data Provider for Host Files can work within an SSIS package using an ADO.NET Destination or ADO.NET Source. In this case, you can only work with flat and single record formats (no nested, no dynamic record formats). )

    - BizTalk Server

    The Microsoft BizTalk Adapter for Host Files can easily work with the BizTalk Adapter for SQL Server to read/write to VSAM, with full support for nested records. See http://technet.microsoft.com/en-us/library/bb726925(BTS.10).aspx

    HIS 2010 also includes the ability to read and write host files offline. See http://msdn.microsoft.com/en-us/library/gg167635(v=BTS.70).aspx:

    Host Files Offline Writer

    HIS developers use the ADO.NET Provider for Host Files to access and integrate information stored in existing host record-oriented files with new solutions that are based on the .NET Framework. When accessing information stored in z/OS host file systems, the data provider must connect across an SNA APPC session using high performance routing over internet protocol (HPR/IP) network. Some enterprises have standardized on TCP/IP and cannot upgrade their host systems to support HPR/IP. Previously, HIS offered an improved host file provider that supports off-line reading of binary files, which allowed for use of file transfer protocol (FTP) as a means to read host data without an SNA APPC session. However, there was no solution for writing data back to the host system. In HIS 2010, the enhanced host file provider supports off-line writing to binary files. This enables enterprise developers to read and write host data without a live connection to the host file system. When operating in off-line reader/writer mode, the ADO.NET Data Provider supports the same record access methods (sequential, keyed, and combined) and record formats based on local metadata produced by the host file designer for Visual Studio.

    THe following is the main link to the HIS 2010 information on MSDN:

    http://msdn.microsoft.com/en-us/library/gg162969(v=BTS.70).aspx

    Thanks...

     


    Stephen Jackson - MSFT
    • Marked as answer by Barry.Hostead Friday, October 14, 2011 8:05 PM
    Friday, October 14, 2011 4:20 PM
  • Hi Stephen,

     

    Thank you very much for your response it is very detailed, I will take a look at the additional information you have directed me towards as well.

     

    Regards

    Barry

    Friday, October 14, 2011 8:05 PM