OIC - Automating File-Based Integrations in OIC: A Scheduler-to-SFTP Flow Using Object Storage and Child Integrations

Use Case

In many enterprise scenarios, large volumes of files are generated daily and need to be processed, transformed, and delivered to target systems on a schedule. This blog outlines an OIC-based solution where a scheduled integration orchestrates the entire process: from validating input parameters, listing files from Oracle Object Storage, and invoking a child integration to download, transform, and upload the files to an SFTP server.

Solution Architecture

The solution consists of three integrations:

1. Scheduler Integration – Initiated with a fileProcessingDate parameter (format: yyyyMMdd) and validates its format. This is empty by default that means current date will be used to fetch the file. but if someone wants to reprocess the file, have to put the file processing date.
2. Main Integration – Lists files from a configured Object Storage path based on prefix and and date filter in the file name and loops over each file and invokes the child integration.
3. Child Integration – Downloads each file, performs necessary transformation, and uploads it to a specified target SFTP location.

Solution Steps

1. Scheduler Integration

• Trigger Type: Scheduled
• Input Parameter: fileProcessingDate (string, format: yyyyMMdd)
• Step 1: Validate fileProcessingDate using an inline expression or JavaScript function.
  • Throw a fault if the format is invalid.
• Step 2: Call the Main Integration, passing fileProcessingDate as a parameter.

2. Main Integration

• Trigger Type: App Driven (HTTP or SOAP)
• Input: fileProcessingDate
• Step 1: Use List Objects action (Object Storage adapter) to list files in the bucket/prefix corresponding to fileProcessingDate.
• Step 2: Loop over each file name from the response.
• Step 3: For each file, call the Child Integration and pass:
  • File name
  • File path (if required)
  • fileProcessingDate (if used in downstream processing)

3. Child Integration

• Trigger Type: App Driven
• Inputs:
  • File name
  • File path (optional)
• Step 1: Use Get Object action from Object Storage to download the file.
• Step 2: Apply transformation (e.g., map fields, convert formats).
• Step 3: Use FTP Adapter to upload the file to the target SFTP folder.