Read CSV File from FTP server and insert data into On Premise Database using OIC

Read CSV File from FTP server and insert data into On-Premise Database using OIC

 In this article, we will learn how to reuse or call already built integration into another integration. 

Use case:  REST integration needs to be developed with the capability to read CSV data from an external FTP server and insert the CSV data into Oracle On-Prem Database

1. Create another  REST Integration in OIC to read a CSV file based on JSON Payload Let's name it Integration1 (ReadCsvandDatabaseInsert)

2.  Create REST Integration in OIC to insert data into the On-Premise Database instance, Let's name it Integration2 (OnPremDataInsert)

3. Now will call Integration2 (OnPremDataInsert)  with the name OnPremDataInsert in Integration1(ReadCsvandDatabaseInsert) to read CSV data from an external FTP server and insert the CSV data into Oracle On-Prem Database

Now let's create Integration1 (ReadCsvandDatabaseInsert)  to read a CSV file based on JSON Payload

Create the integration, drop the REST connector, and fill in the mandatory details like REST endpoint name, Resource URI

Go to the Integrations and click on Create

Select App Driven Orchestration 

Provide the integration Name 

Drop the REST Trigger Connector 

Fill the endpoint name 

Select the Resource URI and Operation for the endpoint 

Enable the query parameter as shown in the snippet 

Copy the below sample JSON payload 

{

  "UserData" : [ {

    "id" : "",

    "FirstName" : "",

    "LastName" : "",

    "Department" : ""

  }, {

    "id" : "",

    "FirstName" : "",

    "LastName" : "",

    "Department" : ""

  } ]

}

Click on Done

 Drop the FTP Connector as shown in the snippet 

provide the endpoint name 

Provide operation as "Read a File"  and provide Input directory of the file residing in external FTP server 

Provide the structure of the file as shown in the snippet 

Create an empty CSV with the column names and click on Selected File Name

• ID 
• FIRSTNAME
• LASTNAME
• DEPARTMENT

Initiate the mapper activity 

Define the mapping for the query param at request side 

Mapping activity at FTP connector 

Map the column between source and target 

Enable the identifier 

Activate the integration and hit the URL 

Here is the JSON payload which shows the data from the CSV file

Now create another Integration to insert data into On-Prem Database instance 

Click on Create 

Click on App Driven Orchestration 

Fill in the mandatory details 

Drop REST connector 

Fill in the endpoint name 

Select the operation as POST and provide sample JSON payload for both request and response 

Click on Next 

Provide the JSON Sample Payload 

{

  "UserData" : [ {

    "id" : "",

    "FirstName" : "",

    "LastName" : "",

    "Designation" : ""

  }, {

    "id" : "",

    "FirstName" : "",

    "LastName" : "",

    "Designation" : ""

  } ]

}

Click on Next 

Click on Response and provide sample  JSON payload 

{

"status" :""

}

Click on Next 

Click on the Done button 

Now drop the For loop 

Drop UserData Array element as Repeating Element 

Once FOR LOOP is configured then drop On-Prem Database connector onto the pallette 

Fill in the Endpoint name and provide operation as Run a SQL statement 

provide the below insert statement and click on Next 

INSERT INTO XX_EMP_JOB_DESCRIPTION( ID,FIRST_NAME,LAST_NAME,DESGINATION) VALUES (#ID,#FIRSTNAME,#LASTNAME,#DESGINATION)  

Click on Validate SQL Query 

Click on Done button 

Now click on mapper to start the mapping activity 

Map the column between source and target 

Click on Validate 

Create mapping for target response 

Click on Create target node and provide the response message as shown in the snippet 

Enable the tracking identifier 

Let's create a lookup to transform data from source to target instance 

Creating data mapping between source and destination 

Now next step is to assign lookup in mapping stage 

Click onDesgination Column and click on  Functions 

Click on LookupValue and drop on onto the pallet 

choose the lookup name 

Click on Next 

Click on Default Value as NA 

Click on Done 

Edit the expression by dropping column from source to the lookup expression as shown in the snippet 

click on save and close it 

Activate the integration and call the REST URL from OIC to verify whether data getting inserted or not 

Data has been successfully validated and transformed  in the On-Prem Database instance 

Now let's call the newly created integration OnPremDataInsert in the integration ReadCsvandDatabaseInsert

Deactivate the integration to make any changes to it .

Drop the action to call Integration 

Provide the integration Call name 

Select the integration name OnPremDataInsert

Clik on Operation POST 

Click on Done 

Click on Mapper Activity 

Map the columns 

Run the integration 

Here is the data load from CSV file to On prem data base instance