Reading the latest file from SFTP in OIC

I received a request from one of my connections who questioned how to read the latest files from SFTP in OIC. So, I thought to discover this and write an article on this so here we are.

By default, the FTP adapter List Files operation will list the files in ascending order according to the last modified time, But if you need to read the files in chronological order or read the latest file (which is modified recently) from SFTP then you need to use some XSLT functions to achieve it.

In this blog, we will see how to fetch the latest file from SFTP and write it into another SFTP location.

For Example: As per the below screenshot, 24000042.csv is the latest file that is modified recently.

To complete this article, you must have the below pre-requisites:

Oracle Integration Instance
FTP Server

You also need to create the FTP connection using the FTP adapter. Look at this blog to know how to create an FTP connection in Oracle Integration.

Let’s get started and see how to achieve this integration.

Creating an integration to fetch the latest file from SFTP:

Create a scheduled orchestration and provide the name as Read_latest_file.
Drag and drop the FTP adapter and use the List Files operation to list all the files. Look at this blog to know, how to list files.
Drag and drop the FTP adapter and use the Read a File operation. Look at this video to know, how to read the file.
Following is the XSLT code inside the Read File mapper to map the fields:

<xsl:template match="/" xml:id="id_11">
              <nstrgmpr:SyncReadFile xml:id="id_12">
                    <ns23:FileReadRequest>
                          <xsl:for-each select="$ListFiles/nsmpr2:ListFileResponse/ns18:ListResponse/ns18:FileList/ns18:File">
                                <xsl:sort select="ns18:lastModifiedTime" order="descending"/>
                                <xsl:if test="position()=1">
                                      <ns23:filename>
                                            <xsl:value-of select="ns18:filename"/>
                                      </ns23:filename>
                                      <ns23:directory>
                                            <xsl:value-of select="ns18:directory"/>
                                      </ns23:directory>
                                </xsl:if>
                          </xsl:for-each>
                    </ns23:FileReadRequest>
              </nstrgmpr:SyncReadFile>
        </xsl:template>

Code Explanation:

for-each XSLT function to iterate over all the files fetched by ListFiles Operation.
Use xsl:sort function to change the default ascending order nature of ListFiles to descending order using the order attribute.
Note: On using xsl:sort we cannot open mapper in design mode.
Now using the position() function fetch the first file which will be our latest File.

Drag and drop the FTP adapter and use the Write File operation with the proper schema to write this latest file at another location. Look at this blog to know, how to Write Files.
Map the fields between Read File response and Write file Request to complete the integration.

After you complete the integration, the designer will look like as per the below screenshot: