Informatica Cloud - Ingest Data from Rest Api into Azure SQL

Today we will learn on how to fetch data from Rest Api and ingest into Azure SQL using Informatica Cloud data integration

Steps to Ingest data from REST API to Azure SQL DB via Informatica:

1.    Create a test SQL Server Database in Azure

·        Setup a new server for the database

·        For testing, use “Public endpoint” while setting up Azure server for database (this has to be properly setup when using for production)

·        Add your current computer’s IP address in the Azure server firewall

·        Add Informatica server’s IP address in the Azure server firewall

o   If you don’t add informatica server’s IP, Informatica will throw error “client with IP *** is not allowed to access the server”

·        Click Create the server.

·        Once the server is created, click OK to create the database

2.    Create Informatica connection to Azure SQL in the informatica cloud “administrator”

·        Get your Azure server name, database name and password from Azure (you can create a SQL account for testing in Azure SQL and supply the credentials appropriately)

·        In the connection, enter the credentials appropriately

·        Set Encryption Type = SSL

·        Click test the connection.

·        Once testing is completed, click create

3.    Create Informatica connection to REST API (we are using weather api for testing)

·        Enter base URL

·        Enter URL Request parameters (if any)

·        Media type = application/json

·        Response Folder Path = <enter a path where you want informatica to store results temporarily>

·        Enter URL Input Parameter Config File name = <enter the response path with a temporary file name>

·        Click create the config csv file = Yes

·        Test and save the connection

4.    In informatica, create a mapping task 

·        Source: 

               1. Choose the above created source connection for Rest api

               2. in the query, use the same parameters in the filter (headers/URL request parameters)
                   that you have set while creating the connection to Rest api

               3. Click preview data to the right of Object (to view sample source data from the api)

                        

                      

·        Target: 

               1. choose Azure SQL connection you create above 

               2. Object: Choose the SQL table name

               3. Operation: Insert 

               4. In field mapping, map the source fields to the target fields.

·        Save the mapping

                               

           

You are done. RUN the job and see the results in Azure SQL :)