SFTP File Mediation With Transformations

Mediate SFTP files between source and destination SFTP server with a transformation

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 strucure 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,

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