Scroll to top

Reading the latest files from SFTP in OIC


Karan Tulsani - May 1, 2023

Reading Time: 5 minutes

If you are having trouble reading the files from an SFTP server in OIC then you have come to the right platform. In this blog, we are going to create an integration with the help of which we are going to read files from SFTP and Insert Data into the Database and send a success email notification.

Requirements to perform the operation:

  1. 1.Oracle Integration Cloud Instance
  2. 2.SFTP server
  3. 3.Data base

Once you have all the above requirements you can follow the below steps:

Step 1 – Set up all the Connections.

To use SFTP Server and Database, you can setup their Connections in Oracle Integration Cloud first.

  • To setup a new Connection, Navigate to Integration > Connections in your OIC instance. In the top right corner, click on Create.

OIC

  • Search the FTP Adapter and select it to create an FTP connection. Next, assign a name to your Connection.

FTP

Configure your connection with all the mandatory details including connection properties and login credentials, then test your connection to ensure it works by clicking Test.

oracle integration

  • For DB Connection, search and select the connection just similar to the above steps except the FTP adapter, this time we will select Db adapter.

oracle integration cloud

  • Based on the security selected, configure your Db connection (In case of wallet upload the zip wallet file).

Step 2 – Create One Schedule based integration.

  • By clicking the top right corner in the Instance, create one Integration and select “Scheduled Orchestration” from the given options and give a name to your integration.

oracle cloud

  • Drag and drop the Plus sign by The FTP connection adapter which you have created

FTP connect

  • Configure the FTP adapter by giving it a name and select List File operation to list all the files which are present in the directory. You can also choose Max files that you want to list and name pattern of the file.

Note- In file name pattern * is used as a place holder.

  • Then you will be able to read the files and again use the FTP adapter to read this time. Next, you can select read operation in the adapter instead of list and all the empty fields will be handled in Mapping of this object and will select your file format as CSV( it also supports XLM and Json).

Recommended for you: Filter Data using Q Parameter in Oracle VBCS

oracle OIC

  • Then you will be able to read the files and again use the FTP adapter to read this time. Next, you can select read operation in the adapter instead of list and all the empty fields will be handled in Mapping of this object and will select your file format as CSV( it also supports XLM and Json).

  • After selecting the file format, you need to upload one sample file for read reference where you can select one field from the file as mandatory (will work as primary key).

oracle cloud

  • In The Mapper, you can pass Input directory, File Name and download directory. As shown in the below screenshot, you can Map directory and File name from the current elements of For Loop and hardcoded the “/OIC/” in download directory (This will act as a temporary directory at integration run time).

mapper code

  • Now, there can be more than one file in your SFTP location. To handle that, you can use XSLT to pick the latest file from the directory. It is possible to Implement it in the Mapper of your read file by going to code mode and put following XSLT code inside the Read File mapper to map the fields.

code

  • Next, you can insert the file which can be read into DB and for that you can drag and drop the Db connection created earlier from the right side and configure.
  • Here, you can assign a name to the object and operation that you want to perform.

oracle orchestration

  • Here, you must select the schema and table in which you want to insert the data with the help of the right arrow key selected from the Db object (Table)and import that table by clicking on import tables options.

oracle

log-file

  • In the Mapper, you can Map all the Fields of your read file response to the insert data request.

ftp-detail

  • In the end, you can add an email notification to get a success notification on the completion of the process.

oracle integration

  • After completion, the Integration will look like this in the design mode.

Step 3 –Testing the Integration.

  1. To test the Integration, put some sample test files in the SFTP location before running the integration.(Here the latest file is New 12)

temp-ftp

  • To test, go to your Integration -> Activate -> Submit Now to test it then click on the integration id which is available on the top right corner after submitting.

oracle

  • Go to Track Instance and click on the eye button to view integration logs. Look for the message received by read fil. It should contain the latest file name which you posted on SFTP.

OIC integration

  • Moreover, you will also receive an email from [email protected] on running the integration successfully.

 

Karan Tulsani

Karan Tulsani is the Founder and CEO of Conneqtion Group, an Oracle Cloud Infrastructure (OCI) & Oracle SaaS Implementation Partner that helps customers achieve digital transformation in ERP and Analytics. He has extensive experience working with clients across Banking and Financial Services, FMCG, Supply Chain Management, and the Public Sector. Karan has successfully led and contributed to numerous consulting engagements, leveraging his deep expertise to deliver impactful solutions. Previously, he was part of the consulting teams at Evosys and Oracle, where he worked with clients across the NA, EMEA, and APAC regions.

Author avatar

Karan Tulsani

Karan Tulsani is the Founder and CEO of Conneqtion Group, an Oracle Cloud Infrastructure (OCI) & Oracle SaaS Implementation Partner that helps customers achieve digital transformation in ERP and Analytics. He has extensive experience working with clients across Banking and Financial Services, FMCG, Supply Chain Management, and the Public Sector. Karan has successfully led and contributed to numerous consulting engagements, leveraging his deep expertise to deliver impactful solutions. Previously, he was part of the consulting teams at Evosys and Oracle, where he worked with clients across the NA, EMEA, and APAC regions.

Related posts

Post a Comment

Your email address will not be published. Required fields are marked *