SFTP File Mediation With Transformations

Version: 17.07

Supported Since: 17.01

Use case description

Global Banking Ltd. is a company which 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 its 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 mediating this currency information between the government and its 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 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 for 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.

UltraStudio Configuration

UltraESB-X Configuration

Implementation of the Solution

Prerequisite

In order to implement above use case you must first select following dependencies when you are creating a new Ultra project.

  • SFTP Connector from the connector list

  • Message Transformation and Flow Control from the processor 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 shown below to fetch the JSON files from the source system.

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

  3. Then add a CSV to XML Transformer processing element to transform the received CSV file to XML file and configure its basic parameters as shown below.

  4. 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 its basic parameters as shown below.

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

  3. Add three more Clone Message processing elements with same configuration as in step 2, to connect all the SFTP Egress Connectors as shown below

The completed integration flow should look like below.

sftp file mediation with transformations complete flow

Configuration for each element is as below. The numbering corresponds to the numbers shown in above diagram.

Design View

Text View

.

1. SFTP Ingress Connector

sftp file mediation with transformations component 1

2, 6, 7 ,8 Clone Message

sftp file mediation with transformations component 2

3. CSV to XML Transformer