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

sftp file mediation with transformations component 3

4. XSLT Transformer

sftp file mediation with transformations component 4

5. XPath String Extractor

sftp file mediation with transformations component 5

Namespace Map should be as below

sftp file mediation with transformations component 5 1

9. SFTP Egress Connector

sftp file mediation with transformations component 9

10. SFTP Egress Connector

sftp file mediation with transformations component 10

11. SFTP Egress Connector

sftp file mediation with transformations component 11

12. SFTP Egress Connector

sftp file mediation with transformations component 12

13. SFTP Egress Connector

sftp file mediation with transformations component 13
.

1. SFTP Ingress Connector

SFTP Host

centosvm

SFTP Remote Port

22

Username

asanka

Password

mypassword

Fetch Path

exchange-rates/outbox

File Name Pattern

2, 6, 7 ,8 Clone Message

Exchange Pattern

Keep Original

Clone Type

With full message

Flow Result Procedure

On result of both

3. CSV to XML Transformer

Root Element Name

Currency

Row Element Name

CurrencyInfo

Namespace Prefix

adrt

Namespace URI

Allow Single Column

false

4. XSLT Transformer

XSLT Path

global-currency-transformation.xsl

5. XPath String Extractor

Variable Name

Reference_No

XPath

/adrt_ns:ExchangeRates/adrt_ns:ReferenceNo

Namespace Map should be as below

adrt_ns

9. SFTP Egress Connector

Host Name

internal-original-sftp-host

Port

22

File Path

received/original

Append Mode

false

Username

internal-original-sftp-username

Password

mypassword

10. SFTP Egress Connector

Host Name

internal-transformed-sftp-host

Port

22

File Path

received/transformed

File Name

@{variable.Reference_No}_internal_GB_CURR_EXCG.xml

Append Mode

false

Username

internal-transformed-sftp-username

Password

internal-transformed-sftp-password

11. SFTP Egress Connector

Host Name

bank-a-sftp-host

Port

22

File Path

outbox-bank-a

File Name

@{variable.Reference_No}_bank-A_GB_CURR_EXCG.xml

Append Mode

false

Username

bank-a-sftp-username

Password

bank-a-sftp-password

12. SFTP Egress Connector

Host Name

bank-b-sftp-host

Port

22

File Path

outbox-bank-b

File Name

@{variable.Reference_No}_bank-B_GB_CURR_EXCG.xml

Append Mode

false

Username

bank-b-sftp-host

Password

bank-b-sftp-password

13. SFTP Egress Connector

Host Name

bank-c-sftp-host

Port

22

File Path

outbox-bank-c

File Name

@{variable.Reference_No}_bank-C_GB_CURR_EXCG.xml

Append Mode

false

Username

bank-c-sftp-host

Password

bank-c-sftp-password

.

Now you can run the Ultra Project and check the functionality of the integration flow. Create an UltraESB Server run configuration and start it.

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. The properties file is located at $ULTRA_HOME/conf/projects/sftp-file-mediation-with-transformation/default.properties

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

sftp-file-mediation-with-transformation-flow.sftp-ingress-connector.host

The host of the input SFTP server

sftp-file-mediation-with-transformation-flow.sftp-ingress-connector.port

SFTP Port

sftp-file-mediation-with-transformation-flow.sftp-ingress-connector.username

Username to be used to connect

sftp-file-mediation-with-transformation-flow.sftp-ingress-connector.password

Password to be used to connect

sftp-file-mediation-with-transformation-flow.sftp-ingress-connector.fetchPath

Path of the remote SFTP server for file fetching

sftp-file-mediation-with-transformation-flow.sftp-ingress-connector.fileNamePattern

File name pattern to select files from the SFTP URL location

sftp-file-mediation-with-transformation-flow.sftp-internal-original-file-sender.host

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

sftp-file-mediation-with-transformation-flow.sftp-internal-original-file-sender.port

Port of the internal SFTP endpoint. Default value is 22

sftp-file-mediation-with-transformation-flow.sftp-internal-original-file-sender.username

Username to be used to connect to the internal SFTP endpoint

sftp-file-mediation-with-transformation-flow.sftp-internal-original-file-sender.password

Password to be used to connect to the internal SFTP endpoint

sftp-file-mediation-with-transformation-flow.sftp-bank-a-file-sender.host

Host name of the Bank-A SFTP endpoint.

sftp-file-mediation-with-transformation-flow.sftp-bank-a-file-sender.port

Port of the Bank-A SFTP endpoint. Default value is 22

sftp-file-mediation-with-transformation-flow.sftp-bank-a-file-sender.username

Username to be used to connect to the Bank-A SFTP server

sftp-file-mediation-with-transformation-flow.sftp-bank-a-file-sender.password

Password to be used to connect to the Bank-A SFTP server

sftp-file-mediation-with-transformation-flow.sftp-bank-b-file-sender.host

Host name of the Bank-B SFTP endpoint.

sftp-file-mediation-with-transformation-flow.sftp-bank-b-file-sender.port

Port of the Bank-B SFTP endpoint. Default value is 22

sftp-file-mediation-with-transformation-flow.sftp-bank-b-file-sender.username

Username to be used to connect to the Bank-B SFTP server

sftp-file-mediation-with-transformation-flow.sftp-bank-b-file-sender.password

Password to be used to connect to the Bank-B SFTP server

sftp-file-mediation-with-transformation-flow.sftp-bank-c-file-sender.host

Host name of the Bank-C SFTP endpoint.

sftp-file-mediation-with-transformation-flow.sftp-bank-c-file-sender.port

Port of the Bank-C SFTP endpoint. Default value is 22

sftp-file-mediation-with-transformation-flow.sftp-bank-c-file-sender.username

Username to be used to connect to the Bank-C SFTP server

sftp-file-mediation-with-transformation-flow.sftp-bank-c-file-sender.password

Password to be used to connect to the Bank-C SFTP server

sftp-file-mediation-with-transformation-flow.sftp-internal-transformed-file-sender.host

Host name of internal SFTP endpoint. This will be used to store the transformed file internall

sftp-file-mediation-with-transformation-flow.sftp-internal-transformed-file-sender.port

Port of the internal SFTP endpoint. Default value is 22

sftp-file-mediation-with-transformation-flow.sftp-internal-transformed-file-sender.username

Username to be used to connect to the internal SFTP endpoint

sftp-file-mediation-with-transformation-flow.sftp-internal-transformed-file-sender.password

Password to be used to connect to the internal SFTP endpoint

After that navigate to $ULTRA_HOME/bin directory. Next you can run the UltraESB-X distribution with following command to start the engine with this sample project deployed.

./ultraesbx.sh -sample sftp-file-mediation-with-transformation

Testing the Integration Project

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.

In this topic
In this topic
Contact Us