Reading the latest files from SFTP in OIC

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. Oracle Integration Cloud Instance
2. SFTP server
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.

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

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.

• 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.

• 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.

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

• 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).

• 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).

• 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).

• 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.

• 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.

• 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.

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

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

• 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)

• 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.

• 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.

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