Sunday, 14 October 2018

Oracle Autonomous Integration Cloud (AIC): Read ZIP files using FTP adapter and load data into on-premise application

We all know the capabilities of Oracle Integration Cloud FTP adapter of reading and writing files. There are situations where the files are in the zip format and required to read the zip files, parse each and every file under the zip file and send the data into the downstream applications.

In the article, we will explain how to read ZIP file from FTP location and push the content of each file under the zip file into the Oracle database.

Download the zip file from here. This zip file contains three CSV file in the below format

FirstName,LastName,Designation,Qualification
Ankur,Jain,Tech Lead,MCA
Dev,Ghelot,Sr. Manager,Btech
Priya,Kapoor,SVP,MBA

Below are some pre-requisites which are required to execute this use case:
  • Oracle Authonous Integration Cloud instance subscription. If  you don't have please subscribe for the free trial from here
  • FTP adapter which should be accessible publicly
  • Oracle Database
  • AIC agent should be up and running
  • Download the SQL script from here. Run these script which will create a table USER_MASTER table and a package UserPKG along with one procedure AddUser
Let's start and achieve the use case
  • Login into the Oracle AIC instance and move to the Integrations
  • Move to the Integrations and click on the Create button
  • Select Scheduled Orchestration pattern
  • Enter Integration name as ReadZipFiles
  • Select FTP_Conn which will be used to read the ZIP file
  • Provide endpoint name as DownloadToICS
  • We'll select Download File Operation which will allow unzipping the file at Integration Cloud Staging location. Enter below information and click the Next button
    • Select Operation: Select Download File
    • Select a Transfer Mode: ASCII
    • Input Directory: Enter the directory which contains the zip file at FTP location
    • FileName: ZIP file name. In our case the zip file name is User.zip
    • Download Directory: Enter the directory where the file will be unzipped at Oracle Staging location: For the time being we have entered /unzippedFiles
    • Select Unzip the file checkbox: This option will unzip the file
  • Click the Done button
  • After this step, the integration will look like below

  • Now add for each loop to loop over the unzipped files. Add For Each activity just after the FTP adapter

  • Enter below information and click the Create button
    • Name: Any meaningful name. In this case, we have entered LoopOverThefiles
    • Repeating Element: Drop the repeating element ICSFile which is under DownloadToICS object
    • Current Element Name: Enter CurrentFile for instance
  • Since all the ZIP file is unzipped on the Oracle Cloud Staging location. So to read the file from staging location, add the Stage activity under For Each loop
  • Enter Endpoint name as ReadFile
  • Select Read Entire File Operation
  • Select Edit button Parallel to Specify the File Name and drop the fileName element under CurrentFile object. Save and Exit the Expression builder
  • Select Edit button Parallel to Specify the Directory to read from and drop the directory element under CurrentFile object. Save and Exit the Expression builder
  • Select Create a new Schema from a CSV file and click Next button
  • Create a sample CSV file and paste the content as below and upload using the Choose File button. Enter User and Users in the subsequent text boxes and click the Next button
FirstName,LastName,Designation,Qualification
Ankur,Jain,Tech Lead,MCA
Dev,Ghelot,Sr. Manager,Btech
Priya,Kapoor,SVP,MBA

  • Click the Done button
  • Add one For Each loop just after the Stage activity. This loop will be used to loop over the employee's data from CSV file
  • Configure the For Each with below information and click the Create button
    • Name: Enter the name as LoopOverTheEmployees
    • Repeating Element: Drop User element under the ReadFile object
    • Current Element Name: Enter as CurrentEmployee
  • Add DB adapter under the nested For Each loop
  • Enter below information and click the Next button
    • What do you want to call your endpoint: Enter the name of your choice. For instance, we have entered InsertUser
    • What operation do you want to perform: Select "Invoke a Stored Procedure"
  • Enter below information and click the Next button
    • Select Schema: Since the package which we wanted to call resides under the HR schema, so we have selected the  HR schema
    • Select Package: Select the USERPKG
    • Select Procedure: Select the ADDUSER
  • Click the Done button
  • Edit the InsertUser mapper and map element from the left panel under CurrentEmployee Object
  • After this step, the integration will look like below
  • Integration is completed now. Save the integration and Close. Activate the integration and Submit using Submit Now option

  • Check the database table and see all data in the CSV files should be inserted into the USER_MASTER table

No comments:

Post a Comment