Oracle Integration Cloud – Read file from SFTP Server

Use Case

Reading a File (csv or other) from a SFTP location or a local file server and performing any actions with the data such as passing it on to Oracle Fusion (SaaS) or transform the data and place it at any destination.

How do you go about it?

Define the logical flow with the end points first.

The flow is simple –

Access CSV file in the SFTP Server from OIC.

Download the File to local OIC Server Area .

Read/Access the file in the local OIC Server Area.

Loop to read file contents.

Insert Data to a cloud database table (say ADW Database)

1. CSV File in a SFTP Server is present

We will need a SFTP Adapter Connection first to be created in order to connect to SFTP Server.

CREATE CONNECTION

Connections are of three types:

TRIGGER – What starts the integration

INVOKE – What we invoke from the integration (target)

TRIGGER & INVOKE – Combination of Both

In this case, SFTP connection is TRIGGER (You can create TRIGGER & INVOKE as well – if you ultimately want to place a file in SFTP server again). So one connection would suffice for both the scenarios.

Go to Connections -> FTP Adapter -> Create Connection ->Provide the Following Values

·  Some Connection Name, say XXCUST_SFTP_OIC_CONN

·  Host Name

·  Port Number

·  User Name

·  Password

·  SFTP – Yes / No (it’s a checkbox)

·  TEST CONNECTION -100%

2. When we read the data from the file, we then insert data in a Cloud Database (say an ADW database).

We need another connection now logically.

To connect to a cloud based ADW database, Oracle provisions you a wallet.zip file – which contains a file with service names like TNSNAMES.ora.

You could also access ADW database from Oracle SQL Developer to run SQLs.

·  Connection Name,

·  User Name,

·  Password,

·  Database Type = Cloud PDB,

·  You are then asked to upload the DatabaseWallet.zip, provide the Wallet password &

·  Test Connection.

Coming back to OIC –

Go to Connections -> ATP Adapter -> Provide

·  Some Connection Name, say XXCUST_ADW_DB_CONN

·  Connection Type – Trigger and Invoke

·  User Name of Database

·  Password

·  Upload Wallet.zip

·  Provide Wallet Password

·  TEST CONNECTION – 100%

EXPERT TIP : You could connect to a Local File Server to read file & a local database to insert data as well. Since both are local on-premise end points, you would need to first create an AGENT GROUP, then AGENTS for these. Lots of material is available on-line to create AGENTS. As explained in first part, AGENTS are used when you need to connect to on-prem end points.

3.  At this point of time, you have created 2 connections –

·  One SFTP which will be used to access and read the file

·  Second ADW Database Connection which will be used to store the file data

4. Click on INTEGRATION -> Create Integration.

On this screen now, 6 kinds of patterns will be available to you to start with:

·  App Driven Orchestration

·  Scheduled Orchestration – This is the most commonly used design pattern which you are going to use frequently. This is the integration that you can create with end-points and can schedule it using the OIC Scheduler (pretty strong – lots of scheduling options available).

·  File Transfer

·  Basic Routing

·  Publish to OIC

·  Subscribe to OIC

5. Build the Integration

You would now reach a blank canvas screen and are ready to build the integration.

We need END POINTS first i.e. the connections which we built. You could click on the PLUS signs or click on the extreme right-hand side to see the available connections to you (the ones that you have created).

Note: For a connection to be able to be dropped at the start of the integration, it must be defined as TRIGGER or TRIGGER & INVOKE, else you won’t be able to drag that connection to the start of the integration.

Drop or Add the SFTP Connection to the Integration. As you add it, a new window opens:

·  Provide the name of Operation, say ‘DownloadFile’

·  This is the SFTP Connection, so you would get standard operations such as:

• Read File – Directly reads the file from SFTP location
• Download File – Downloads the file from SFTP location to local OIC. Download limit in OIC is 1GB.
• Move File – Move file from one location in SFTP server to another (Linux mv operation)
• Delete File – Delete the file in the SFTP server
• Read File in Segments – There is a limitation in OIC Product that it cannot read files from any location which is greater than 10MB. The way around is – you use this instead to read the file serially, it reads in chunks of 200 & by-passes the 10MB limit.
• List File – Lists the file in the SFTP server location. This is the only operation available that allows WILD CARD SEARCH on the name of the file to list the files. I will explain the usage of this – very handy for daily batch processing files, the names of which are appended by say sydatetimestamp (e.g – 20191107095311_FILENAME_20191107095311.csv) – so the file name is not constant each day.

· We select ‘DOWNLOAD FILE’ Operation. For this you need to provide:

• File Name
• Download Location – this is the SFTP server location, example – /home/oracle/batch/receivearea
• Target Location – this is the OIC staging area – you can provide any name as during the integration – OIC creates these TEMP folders to download file and the temp area is then cleared at the end of the integration.

Example – /oic/tmp/batch

• You would then need a schema definition to be created in OIC, that matches the file row so that there is a 1-1 mapping between the file row and the OIC schema where we will put the file.

Example, the file is a (StudentList.csv) CSV File for Student Names which we are reading:

StudentId,StudentName,StudentDepartment,StudentCity

101,John,Chemistry,Oxford

102,Brian,Physics,Warwick

103,Peter,English,Edinburgh

104,Bipul,Mathematics,New Delhi

Select CSV Schema Definition & load the same file as input with only 1-2 rows (no need to load the whole file) so that OIC schema is same to the CSV file schema.

You could also write a JSON or XSD to fit the same bill – but its easier to load a CSV schema while reading CSV files, as you can load the same file to get the same schema.

• At this point of time – we have only added DOWNLOAD FILE SFTP Connection End Point in the Integration. As soon as you add any END POINT in the Integration, a MAP is automatically added just before the end point. MAP is super-useful as they provide the values for the end-point, Without MAPS end points will have no use if values are needed to invoke the end-point. For SFTP end-point, the mapping screen will show you the same values that you provide in the DOWNLOAD FILE operation, which are – File Name, Download Location, Target Location Etc.

EXPERT TIPS

• The values provided in MAPPING will over-ride the values mentioned in the End-Point. Tip is to leave the end-point non-mandatory fields blank in the previous step and provide values via mapping.

Reasoning for the above – say you are reading from location:

– /home/oradev/batch/receivearea in DEV Environment and

–  /home/oraprod/batch/receivearea in PROD Environment

•  In the above scenario – the value of input is changing.
•  Define a Lookup in DEFINE LOOKUPS as:

–  DOWNLOAD_DIR – /home/oradev/batch/receivearea

–  ARCHIVE_DIR – /home/oradev/batch/receivearea/archive

• Once lookup is imported in TEST and PROD, we make a one time change in lookups to:

–  DOWNLOAD_DIR – /home/oratest/batch/receivearea (test)

–  DOWNLOAD_DIR – /home/oraprod/batch/receivearea (prod)

• Best solution is to DEFINE GLOBAL VARIABLES using ASSIGN operation and create variables:

–  FILE_DOWNLOAD_AREA – Value = get the value from Lookup by passing the key (DOWNLOAD_DIR).

–  FILE_NAME – Write the static file name – StudentList.csv

–  FILE_ARCHIVEAL_AREA – Value = get the value from Lookup by passing the key (ARCHIVE_DIR).

• ASSIGN operation name can be SetGlobals and should be used to assign any variables that you are going to use in the integration.
• Wherever you have to write a static string for assigning or comparison – best is to create a variable right at the start and use it seamlessly across the integration.

This article contained – 

1. OIC Design Patterns, which is the most commonly used
2. Create Connections – we created 2 – SFTP & ATP (for ADW)
3. Usage of Lookups
4. Usage of ASSIGN to create Global Variables and how they are better and make our integration easier to write & Maintain.
5. MAP – is assigned each time you add an end-point. Another expert tip I am providing here – When a map is added to OIC Integration – a hollow circle is present in between map and the end point – it means no mapping values are provided. Once you open the MAP and provide values in the mapping, the hollow circle is filled up (You see a large DOT) – see it for yourself.

Now the file has only been downloaded to OIC staging area.