none
Importing from Mainframe with Host Integration Server RRS feed

  • Question

  • Hi team,

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

    https://www.mssqltips.com/sqlservertip/3497/importing-mainframe-data-with-sql-server-integration-services-and-host-integration-server/

    Please let me know if i want to import  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.

    Thank you

    Monday, October 21, 2019 4:51 PM

Answers

  • Hi All,

    Thanks Motax80 for your post. I tried with that event log. Noting much i could able to find in error log. But finally i find the problem and i fix it.

    The problem was my HIS and SSIS are in different versions and not supporting. I was using HIS 2013 in witch supported by VS2012 and SSIS package creation i was using VS2017 in witch HIS was not available. I remove HIS 2013 and install HIS 2016 and did all my configurations ans i use VS2013 for both HIS metadata creation and SSIS package creation. It works. 

    I believe this note will help others same like me so i share it here.

    Thanks everyone who reply my post and help me. so i could able to learn many things from these posts.


    Shaheen

    • Marked as answer by Shaheen24s Wednesday, October 30, 2019 1:12 PM
    Wednesday, October 30, 2019 1:11 PM

All replies

  • I would think that the connection properties would depend on the interfaces that the mainframe offers. So you will need to ask whoever installed/supports HIS, and your mainframe tech team, to verify direct access to the data. (A DB2 or IMS database? A plain old EBCDIC file? A VSAM file?  Etc.)    
    Monday, October 21, 2019 5:50 PM
  • Hi Shaheen24s,

    May I know if you see the following link?

    Importing Mainframe Data with SQL Server Integration Services

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 22, 2019 8:00 AM
  • Mainframe data on VSAM files and when i copy sample data in text files and tested same like shown in the link the connection working fine and i can see the data. But when i try the connection to mainframe direct access the connection string is not valid. i think i miss something in connection properties. I don't have any support from HIS team. only i can check the existing connection details through SNA. Do you have any sample connection details to help with.  

    

    Tuesday, October 22, 2019 1:09 PM
  • Hi Mona,

    The above link will not help me. My data is residing in VSAM files in mainframe. 

    Tuesday, October 22, 2019 1:11 PM
  •  Do you have any sample connection details to help with.  

    

    I'm sorry, I do not. I used to support MVS/CICS systems so I thought that maybe I could help you a bit. 

    I found this: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/82e174a9-9ed6-4176-ba09-15d171638af3/how-to-read-data-from-vsam-files-of-ibm-mainframe-with-ssis-and-store-it-as-relational-data-in-sql

    What provider are you using in your connection string? Maybe share the connection string (remove user+pswd if specified)? 

    Tuesday, October 22, 2019 3:09 PM
  • Hi, 

    i go through he above link and i am trying the connectivity using HIS 2013.

    I will share the connection details below.

    Qalifier:  Microsoft.HostIntegration.MsHostFileClient.HostFileConnection, Microsoft.HostIntegration.MsHostFileClient, Version=9.0.1000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

    Data Source=Mainframe;APPC Remote LU Alias=REMTLU1;APPC Local LU Alias=MSWLU100;APPC Mode Name=QPCSUPP;Network Transport Library=SNA;Host CCSID=20420;PC Code Page=1256;Network Port=446;Integrated Security=False;Default Library=PO;Metadata=D:\HIDX\Myservices.hidx; 

     

    Tuesday, October 22, 2019 3:43 PM
  • Are you connecting to MVS or an AS/400?

    https://docs.microsoft.com/en-us/host-integration-server/core/appc-mode-definition2

    QPCSUPP  - All sessions with an AS/400 computer


    • Edited by MotoX80 Tuesday, October 22, 2019 4:05 PM
    Tuesday, October 22, 2019 3:57 PM
  • i am trying to connect with MVS and data is in VSAM files
    Tuesday, October 22, 2019 6:35 PM
  • Why did you specify Mode Name=QPCSUPP?

    Did you try Mode Name=#BATCH? 

    Tuesday, October 22, 2019 6:42 PM
  • another info that when i use older version the connection is working fine.

    HIS 2006, SQL server 2008 and ssis 2008.

    Connection string: Data Source=Mainframe;APPC Remote LU Alias=REMTLU1;APPC Local LU
    Alias=MSWLU100;APPC Mode Name=QPCSUPP;Network Transport Library=SNA;Host
    CCSID=20420;PC Code Page=1256;Network Port=446;Integrated
    Security=False;Metadata=C:\HIS_DLLs\DLLNAME.DLL;

    This dll is not supported for HIS higher version and .net 4.0 instead i convert the dll into hidx. reference link is shown below. But with upgraded version of HIS i could find the right connection string.

    https://docs.microsoft.com/en-us/host-integration-server/core/upgrading-existing-installation-host-files 

    Tuesday, October 22, 2019 6:44 PM
  • Have you seen/used this wizard? https://docs.microsoft.com/en-us/host-integration-server/core/data-source-wizard-host-files-2

    I'm sorry, I'm not going to be of much help to you. Good luck. 

    Tuesday, October 22, 2019 6:57 PM
  • i gone through these and the connection i tired to define similar to this. But it is not connecting with mainframe. With local file its working fine.
    Tuesday, October 22, 2019 7:07 PM
  • Hi Shaheen24s,

    The following link will be helpful:

    Best Practices: Staging Mainframe/DB2 Data to SQL Server 2012

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, October 25, 2019 5:48 AM
  • Hi Mona,

    thanks but the above link is not related to issue.


    Shaheen

    Sunday, October 27, 2019 7:14 AM
  •  But it is not connecting with mainframe. 

    There has to be an error event in a log somewhere when that occurs. Try this: in this post I put a Powershell script that I called RecentEvents.ps1. It reads events from all eventlogs, sorts by time, and displays them in a gridview. Recreate the error and then run the script on the HIS server.

    https://social.technet.microsoft.com/Forums/en-US/87fad390-725c-46f1-88fe-a12446591bad/windows-services-terminates-unexpectedly?forum=ws2016

     
    Sunday, October 27, 2019 12:38 PM
  • Hi All,

    Thanks Motax80 for your post. I tried with that event log. Noting much i could able to find in error log. But finally i find the problem and i fix it.

    The problem was my HIS and SSIS are in different versions and not supporting. I was using HIS 2013 in witch supported by VS2012 and SSIS package creation i was using VS2017 in witch HIS was not available. I remove HIS 2013 and install HIS 2016 and did all my configurations ans i use VS2013 for both HIS metadata creation and SSIS package creation. It works. 

    I believe this note will help others same like me so i share it here.

    Thanks everyone who reply my post and help me. so i could able to learn many things from these posts.


    Shaheen

    • Marked as answer by Shaheen24s Wednesday, October 30, 2019 1:12 PM
    Wednesday, October 30, 2019 1:11 PM