{"id":3450,"date":"2018-05-08T21:01:37","date_gmt":"2018-05-08T21:01:37","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=3450"},"modified":"2019-10-03T18:38:34","modified_gmt":"2019-10-03T18:38:34","slug":"read-json-informatica-import-rest-api-json-file","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-json-informatica-import-rest-api-json-file\/","title":{"rendered":"Read JSON in Informatica – Import REST API \/ SOAP \/ JSON File"},"content":{"rendered":"

Introduction<\/h2>\n

\"Informatica<\/a>JSON \/ REST API is becoming more and more popular each day as everyone embrace cloud-centric\u00a0services. This article is primarily focused on Informatica users who want to do XML SOAP\/\u00a0JSON \/ REST API Integration in Informatica<\/strong>. However many tips and techniques described in this article will help you to understand how to integrate XML SOAP \/ JSON \/ REST API in other ETL \/ Reporting apps such as Tableau, Power BI, SSRS, Talend, Excel and many more.<\/p>\n

After going through this article you will learn\u00a0how to\u00a0Read JSON in Informatica <\/strong>and understand the concept of JSON \/ REST API<\/strong>. We will go through many screenshots and step-by-step examples to demonstrate\u00a0\u00a0JSON File or REST API integration in Informatica PowerCenter.<\/strong><\/p>\n

XML \/ JSON can come from a local file or REST API service (internal or public) so we will include both examples in this article (i.e. Read JSON files in Informatica,\u00a0\u00a0Import REST API in Informatica). So let’s get started. Next article will focus on how to write data to API in Informatica (POST \/ PUT data)<\/a><\/p>\n

Requirements<\/h2>\n

This article assumes that you have full filled following basic requirements.<\/p>\n

    \n
  1. Download Install ZappySys ODBC PowerPack<\/a> (Drivers for JSON and REST API)<\/li>\n
  2. Install Informatica PowerCenter Client Tools (e.g. Workflow and Mapping Designers)<\/li>\n
  3. Access to a Relational database such as SQL Server (or use any of your choice e.g. Oracle, MySQL, DB2 ). If nothing available then you can use flat file target.<\/li>\n<\/ol>\n

    How to Import JSON data using Informatica (Read JSON Files or REST API)<\/h2>\n

    Before we dive deep to learn how to load JSON data in Informatica (JSON to SQL Table), Here the summary of high-level steps you need to perform to import JSON Files or REST API in Informatica.<\/p>\n

      \n
    1. Download and Install ZappySys JSON ODBC Driver<\/a> (for JSON Files and REST API)<\/li>\n
    2. Create ODBC DSN using ZappySys JSON driver for your API Service (You can override most of the DSN settings via SQL Query at runtime)<\/li>\n
    3. Create Relational > ODBC<\/strong> Connection in Informatica Workflow designer<\/strong> (Point to JSON DSN we created in the\u00a0previous step)<\/li>\n
    4. Import JSON Source Definition in the Informatica Mapping Designer<\/strong> > Sources Tab<\/strong><\/li>\n
    5. Import Target Table\u00a0Definition\u00a0in the Informatica Mapping Designer<\/strong> > Targets Tab<\/strong><\/li>\n
    6. Create source to target mapping in Mappings tab<\/li>\n
    7. Save mapping (name m_API_to_SQL_Load<\/strong> )<\/li>\n
    8. Create Session using the mapping we created in the previous step<\/li>\n
    9. Save Workflow and execute to load JSON data into SQL Table. Verify your data and log.\n
      \"Loading<\/a>

      Loading JSON data to SQL Table in Informatica (Import REST API or JSON Files)<\/p><\/div><\/li>\n<\/ol>\n

      Video Tutorial – Read JSON in Informatica (Load JSON to SQL Table)<\/h2>\n

      By watching following ~5 min video can learn steps listed in this article to load JSON API data into SQL Server Table using ZappySys JSON Driver<\/strong>. You can go though full article to learn many useful details not covered in this video.<\/p>\n

      <\/div><\/div>
      <\/div>
      <\/div>
      <\/div><\/div><\/div>