Reading data from On-Prem Database and creating file into FTP server through Integration Call in 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 data from an on-prem database instance and generate the CSV file based on the on-premise database instance in an external FTP server
To achieve the use case we need the following things to need to be done
1. Create REST Integration in OIC to read data from the On-Premise Database instance, Let's name it Integration1 (CreateCsvFileFromDB)
2. Create another REST Integration in OIC to generate a CSV file based on JSON Payload Let's name it Integration2
3. Now will call Integration2 (CSV File Generation) with the name CreateCSVFileIntegration in Integration1(Select data from On-Prem DB ) to read data from the on-prem database instance and generate a CSV file in the external FTP server.
Now let's create Integration1 with the name CreateCsvFileFromDB to read data from the On-Premise Database instance
Create the integration, drop the REST connector, and fill in the mandatory details like REST endpoint name, Resource URI
Provide the sample JSON payload
{
"UserData":[
{
"id":"",
"firstname":"",
"lastname":"",
"department":""
},
{
"id":"",
"firstname":"",
"lastname":"",
"department":""
}
]
}
Provide the response payload as
{
"status":""
}
Click on Done.
Drop the OnPremDatabase connector to the OIC pallette
Select the mode as Run a SQL statement
provide the SQL query required to fetch data from the on-prem instance
Click on Done
The next step is to do mapping the activity
Use the mapper activity to map the fields between source response and target response and click on done to complete the mapping activity.
Enable identifier for tracking
Activate the integration and test it by hitting the URL
With this, we are done with Integration1.
Now let's create another integration with the purpose to generate a CSV file based on the JSON Payload, Create Integration with App Driven Orchestration
Fill in details to create a new integration
Drop the REST Trigger
Provide the endpoint name
select operation as POST, since we are going to generate CSV at the FTP server
{
"UserData":[
{
"id":"",
"firstname":"",
"lastname":"",
"department":""
},
{
"id":"",
"firstname":"",
"lastname":"",
"department":""
}
]
}
Configure response payload as JSON with below sample response
{
"status" :""
}
Click on Done
Once the ENDPOINT configured, now lets drop the FTP connector
Select the operation as Write File and Provide File Name Pattern as ora%SEQ%File.csv
The file will be created as ora1File.csv, ora2File.csv respectively
Specify the structure for the contents of the file
Create the CSV file with columns below and upload it as sample file
- ID
- FIRSTNAME
- LASTNAME
- DEPARTMENT
Click on done
Perform the mapping activity using mapper.
Create mapping for status as well by creating a target node
Map the message in target node as "Successfully created "
Click on Done button
Activate the integration and run through the JSON payload
File will be created in External FTP server
Now get through the integration1 which we have created with the name CreateCsvFileFromDB, deactivate to make changes to it by adding the newly created Integration2 (CreateCSVFileIntegration)
Open the Integration, Go to the action, expand the Call to drag Integration onto the pallette
Provide the name InvokeCsvAPI
Now browse the integration which you want to call , in our case it is CreateCSVFileIntegration
Select the Operation POST
Click on Done
Click on mapper to perform the mapping activity
Perform the mapping activity from the Database connector response to Call Integration response
Save and activate the integration
Hit the URL to generate CSV based on the On-Prem Database select query
Comments
Post a Comment