SFTP File Mediation With Transformations

Version: 17.01

Supported Since: 17.01

Use case description

Global Banking Ltd is a company maintains a banking gateway which mediates end of day transaction information between multiple banks. Global Banking recently introduced a new business requirement to sync the currency information between the government finance department and all it’s registered banks. Global Banking should fetch this currency information from the government department as a CSV file and transform this to a XML file which has a pre-defined schema.

There is a primary requirement to preserve this XSD schema while sending the currency information to the registered banks. For auditing purposes, Global Banking has decided to keep a copy of all the received and transformed file contents while mediation this currency information between the government and it’s clients. For this purpose, it’s required to send a copy of received CSV file as well as the transformed XML file to Global Banking’s internal SFTP server. File name should be modified to "<currency_update_reference_no><bank_id>_GB_CURR_EXCG.xml" format while uploading the files to the registered banks. "<currency_update_reference_no>" should be extracted out from the received CSV file from the column "Ref No". This "Ref No" value will be unique for all the entries for a given CSV input file.

The structure of the input CSV file which will be fetched from the finance department, will be like below,

EFFECTIVE_DATE,REFERENCE_NO,CRNCY_CODE,EXCH_RATE
10/24/16,CR_9898123,AUD,1.06
10/24/16,CR_9898123,CNY,6.07
10/24/16,CR_9898123,EUR,0.73
10/24/16,CR_9898123,GBP,0.59
10/24/16,CR_9898123,HKD,7.75
10/24/16,CR_9898123,JPY,103
10/24/16,CR_9898123,SGD,1.25
10/24/16,CR_9898123,USD,1
10/25/16,CR_9898123,AUD,1.10
10/25/16,CR_9898123,CNY,6.17
10/25/16,CR_9898123,EUR,0.83
10/25/16,CR_9898123,GBP,0.63
10/25/16,CR_9898123,HKD,7.79
10/25/16,CR_9898123,JPY,101
10/25/16,CR_9898123,SGD,1.05

The structure of the expected output XML file which will be sent to the registered banks will be like this,

<?xml version="1.0" encoding="UTF-8"?>
<ExchangeRates xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://adrt.com/studio/testing">
   <OriginatedFrom>Global Currency Ltd</OriginatedFrom>
   <CreationTimestamp>2016-12-23T15:07:25.069Z</CreationTimestamp>
   <ReferenceNo>CR_9898123</ReferenceNo>
   <Rates>
      <Rate>
         <Currency>AUD</Currency>
         <Rate>1.06</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>CNY</Currency>
         <Rate>6.07</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>EUR</Currency>
         <Rate>0.73</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>GBP</Currency>
         <Rate>0.59</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>HKD</Currency>
         <Rate>7.75</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>JPY</Currency>
         <Rate>103</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>SGD</Currency>
         <Rate>1.25</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>USD</Currency>
         <Rate>1</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>AUD</Currency>
         <Rate>1.10</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>CNY</Currency>
         <Rate>6.17</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>EUR</Currency>
         <Rate>0.83</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>GBP</Currency>
         <Rate>0.63</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>HKD</Currency>
         <Rate>7.79</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>JPY</Currency>
         <Rate>101</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>SGD</Currency>
         <Rate>1.05</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
      <Rate>
         <Currency>USD</Currency>
         <Rate>1</Rate>
         <RelativeTo>USD</RelativeTo>
      </Rate>
   </Rates>
</ExchangeRates>

Proposed Solution

As the solution for this, Global Banking has decided to mediate this currency information via an ESB since it has to do multiple SFTP fetch/uploads for a single message flow. Government finance department and client banks will be connected via ESB as shown in the following diagram.

sftp file mediation with transformations diagram

ESB will fetch the currency information in CSV format from the finance department and first it will upload the received file content to internal SFTP location to auditing purposes. Then it will do required transformations to make sure that the final content of the file is in XML format according to the pre-defined schema. This transformed content will be send to both internal SFTP server and all the SFTP server’s of the registered banks. While uploading these files to the clients, file name should be modified as described in the use case requirement.

Implementation of the Solution

Prerequisite

In-order to develop the above mentioned flow, first you need to add SFTP Connector from the connector list and Message Transformer and Flow Control from processors list.

If you have already created a project, you can add above dependencies via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the Connectors list and Processors list, select above dependencies
Implementation

To implement above use case, first let’s create our integration flow named “sftp-file-mediation-with-transformation”. Then let’s add required components by going through following steps in order.

  1. Add an SFTP Ingress Connector from the connectors list, to fetch the files from the source SFTP server. SFTP Ingress Connector basic parameter should be filled as below to fetch the JSON files from the source system.

sftp file mediation with transformations 1

  1. Now received message should be cloned to send to the the internal SFTP server. For that add a Clone Message processing element

  2. Then add a CSV to XML Transformer processing element to transform the received CSV file to XML file and configure it’s basic parameters as shown below.

sftp file mediation with transformations 2

  1. Then add an XSLT Transformer processing element to transform the above XML message to the expected format as described in the use case requirement. Configure the XSLT Path parameter by selecting the XSLT to transform the message to the final format. The XSLT structure would be like below to do the required transformation.

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"   xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://adrt.com/studio/testing">

    <xsl:output indent="yes" omit-xml-declaration="no"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="Currency" xpath-default-namespace="http://adrt.com/studio/testing">
        <ExchangeRates>
            <OriginatedFrom>Global Currency Ltd</OriginatedFrom>
            <ReferenceNo><xsl:value-of select="CurrencyInfo[1]/REFERENCE_NO"/></ReferenceNo>
            <CreationTimestamp><xsl:value-of select="adjust-dateTime-to-timezone(current-dateTime(), xs:dayTimeDuration('PT0H'))"/></CreationTimestamp>
            <Rates>
                <xsl:apply-templates/>
            </Rates>
        </ExchangeRates>
    </xsl:template>

    <xsl:template match="CurrencyInfo" xpath-default-namespace="http://adrt.com/studio/testing">
        <Rate>
            <Currency><xsl:value-of select="CRNCY_CODE"/></Currency>
            <Rate><xsl:value-of select="EXCH_RATE"/></Rate>
            <RelativeTo>USD</RelativeTo>
        </Rate>
    </xsl:template>

</xsl:stylesheet>

  1. Now reference ID should be extracted out to modify the file name as described in the requirement. For that, add a XPath String Extractor processing element and configure it’s basic parameters as shown below.

sftp file mediation with transformations 3

namespaces-map should be defined as below in the project.xml.

sftp file mediation with transformations 10

  1. Then add SFTP Egress Connectors as described below to send out files to both internal SFTP server locations and three registered banks - Bank A, Bank B and Bank C.

    1. Add an SFTP Egress Connector and configure as shown below to send the received file to internal SFTP server location - <SFTP_USER_HOME>/received/original

sftp file mediation with transformations 4

  1. Add an SFTP Egress Connector and configure as shown below to send the final structure of the file to internal SFTP server location - <SFTP_USER_HOME>/received/transformed

sftp file mediation with transformations 5

  1. Add an SFTP Egress Connector and configure as shown below to send files to Bank A SFTP server location - <SFTP_USER_HOME>/inbox/exchange-rates

sftp file mediation with transformations 6

  1. Add an SFTP Egress Connector and configure as shown below to send files to Bank B SFTP server location - <SFTP_USER_HOME>/inbox/exchange-rates

sftp file mediation with transformations 7

  1. Add an SFTP Egress Connector and configure as shown below to send files to Bank C SFTP server location - <SFTP_USER_HOME>/inbox/exchange-rates

sftp file mediation with transformations 8

  1. Add three more Clone Message processing element to connect all the SFTP Egress Connectors as shown below

sftp file mediation with transformations 9

Now you can run the integration flow and check the SFTP file mediation and transformation is working as expected and input files will be delivered from finance department SFTP server to the registered banks. At the same time a copy of the received input file as well as the final transformed file should be sent to the internal SFTP server location which will be helpful for auditing purposes as described in the use case requirement.

Property Configuration

When running the sample in the UltraESB-X distribution, you need to override the following properties in-order for the sample to work.

Refer to Managing Project Properties documentation on how to override properties.

SFTPIngressConnector.sftpListner.host

The host of the SFTP server

SFTPIngressConnector.sftpListner.port

SFTP Port

SFTPIngressConnector.sftpListner.username

Username to be used to connect

SFTPIngressConnector.sftpListner.password

Password to be used to connect

SFTPIngressConnector.sftpListner.fetchPath

Path of the remote SFTP server for file fetching

SFTPIngressConnector.sftpListner.fileNamePattern

File name pattern to select files from the SFTP URL location

SFTPEgressConnector.internalSftpEgressConnector.host

Host name of SFTP endpoint.This will be used to store the original file

SFTPEgressConnector.internalSftpEgressConnector.port

Port of the SFTP endpoint. Default value is 22

SFTPEgressConnector.internalSftpEgressConnector.username

Username to be used to connect

SFTPEgressConnector.internalSftpEgressConnector.password

Password to be used to connect

SFTPEgressConnector.bankASftpEgressConnector.host

Host name of SFTP endpoint.

SFTPEgressConnector.bankASftpEgressConnector.port

Port of the SFTP endpoint. Default value is 22

SFTPEgressConnector.bankASftpEgressConnector.username

Username to be used to connect

SFTPEgressConnector.bankASftpEgressConnector.password

Password to be used to connect

SFTPEgressConnector.bankBSftpEgressConnector.host

Host name of SFTP endpoint.

SFTPEgressConnector.bankBSftpEgressConnector.port

Port of the SFTP endpoint. Default value is 22

SFTPEgressConnector.bankBSftpEgressConnector.username

Username to be used to connect

SFTPEgressConnector.bankBSftpEgressConnector.password

Password to be used to connect

SFTPEgressConnector.bankCSftpEgressConnector.host

Host name of SFTP endpoint.

SFTPEgressConnector.bankCSftpEgressConnector.port

Port of the SFTP endpoint. Default value is 22

SFTPEgressConnector.bankCSftpEgressConnector.username

Username to be used to connect

SFTPEgressConnector.bankCSftpEgressConnector.password

Password to be used to connect

SFTPEgressConnector.bankDSftpEgressConnector.host

Host name of SFTP endpoint.

SFTPEgressConnector.bankDSftpEgressConnector.port

Port of the SFTP endpoint. Default value is 22

SFTPEgressConnector.bankDSftpEgressConnector.username

Username to be used to connect

SFTPEgressConnector.bankDSftpEgressConnector.password

Password to be used to connect
In this topic
In this topic
(c) Copyright 2016-2017 AdroitLogic. All rights reserved.
Terms of Use
Contact Us