File Transfer Integration in OIC
File Transfer Integration in OIC: A Complete Guide with Examples
File-based integrations are still an important part of enterprise application integration. Many organizations exchange data through CSV, XML, JSON, TXT, ZIP, and other files between applications, databases, ERP systems, SFTP servers, and cloud platforms.
Oracle Integration Cloud (OIC) provides built-in capabilities for designing and managing file-based integrations. With OIC, you can read files from an FTP/SFTP server, process their contents, transform the data, and send the result to another application or file server.
In this guide, we will understand File Transfer Integration in OIC, how it works, the important activities and adapters involved, common use cases, and best practices.
What Is File Transfer Integration in OIC?
A File Transfer Integration in OIC is an integration process used to move, read, process, transform, or write files between systems.
A typical file integration can look like this:
SFTP Server
|
| CSV/XML File
↓
Oracle Integration Cloud
|
| Read File
↓
Validate / Transform
|
↓
Target Application
For example, an organization may receive an employee CSV file from an SFTP server every night.
OIC can:
- Pick up the file.
- Read the file contents.
- Parse the data.
- Validate the records.
- Transform the data.
- Send the information to Oracle Fusion HCM.
- Move or archive the processed file.
Why Use OIC for File-Based Integrations?
Without an integration platform, organizations often need custom scripts or programs to handle file transfers.
OIC provides a centralized integration platform where file processing, transformation, connectivity, monitoring, and error handling can be implemented.
Some common advantages include:
- Secure file transfer
- SFTP connectivity
- File processing
- Data transformation
- Scheduling
- Integration with Oracle applications
- Integration with third-party applications
- Error handling
- Monitoring
- Reusable connections
- Automated file processing
Common File Transfer Architecture
A typical enterprise file integration can be designed like this:
OIC
|
+------------+------------+
| |
Source Target
| |
SFTP Oracle Fusion
| |
CSV File ERP / HCM / SCM
Another common architecture is:
Source SFTP
|
↓
OIC Schedule Integration
|
↓
Read File
|
↓
Stage File
|
↓
Transform
|
↓
Target Application
Important OIC Components for File Integrations
Several OIC components are commonly used when building file-based integrations.
1. FTP Adapter
The FTP Adapter is commonly used to connect OIC with FTP and SFTP servers.
It can be used for operations such as:
- Reading files
- Writing files
- Listing files
- Moving files
- Deleting files
- Downloading files
- Uploading files
For secure enterprise environments, SFTP is generally preferred over plain FTP.
2. Stage File Action
The Stage File action is one of the most important activities for file processing in OIC.
It allows an integration to work with files during the integration flow.
Common operations include:
- Read Entire File
- Read File in Segments
- Write File
- Zip Files
- Unzip Files
It is particularly useful when the integration needs to temporarily process file data.
A simplified flow can be:
FTP Adapter
|
↓
Download File
|
↓
Stage File
|
↓
Read File
|
↓
Mapping
3. Schedule Integration
Many file integrations are batch-based.
For example, an organization may receive files every day at 11 PM.
A Schedule Integration can automatically start the process:
11:00 PM
|
↓
OIC Schedule Trigger
|
↓
Connect to SFTP
|
↓
Find File
|
↓
Process File
|
↓
Send Data to Target
This eliminates the need for manual file processing.
4. Data Mapper
File data often has a different structure from the target application’s data.
For example:
Source CSV
EMP_ID,NAME,DEPARTMENT
1001,John,Finance
1002,David,IT
The target application may require:
EmployeeId
EmployeeName
DepartmentName
OIC’s mapping capabilities can transform the source structure into the required target structure.
EMP_ID → EmployeeId
NAME → EmployeeName
DEPARTMENT → DepartmentName
5. Lookups
Lookups can be useful when source and target systems use different values.
For example:
Source Department Target Department
FIN Finance
IT Information Technology
HR Human Resources
Instead of hard-coding these values throughout the integration, an OIC lookup can be used to maintain the mapping.
File Transfer Integration: Step-by-Step Example
Let’s consider a practical example.
Business Requirement
A company receives an employee file from an SFTP server every night.
The file contains:
Employee ID
Employee Name
Department
Email
Salary
OIC needs to:
- Pick up the file.
- Read the records.
- Validate the data.
- Transform the records.
- Send the data to Oracle Fusion HCM.
- Archive the processed file.
Step 1: Create the Connection
Open OIC and navigate to the Connections section.
Create an FTP Adapter connection.
Configure the required details, such as:
- FTP/SFTP server
- Host
- Port
- Username
- Authentication
- Security credentials
- Connection properties
For SFTP, the appropriate authentication and security configuration should be used based on the server requirements.
Step 2: Create the Integration
Navigate to Integrations and create a new integration.
For a periodic file-processing scenario, select a Schedule integration.
Provide a meaningful name, such as:
Employee_File_Processing
Step 3: Configure the Schedule
Configure when the integration should run.
For example:
Every day at 11:00 PM
The schedule can be adjusted according to the business requirement.
Step 4: Add the FTP Adapter
Add the FTP Adapter to the integration flow.
Configure it to identify the required files.
For example:
/inbound/employee/
File pattern:
employee_*.csv
This ensures that the integration processes only files matching the required naming pattern.
Step 5: Download the File
Once the FTP Adapter identifies the file, OIC can download it for processing.
The flow becomes:
Schedule
↓
FTP Adapter
↓
Download Employee File
Step 6: Use Stage File
After obtaining the file, use the Stage File action to process the contents.
For example:
Stage File
|
↓
Read Entire File
If the file is large, processing it in segments may be more appropriate.
Step 7: Read the File Structure
Suppose the CSV file contains:
EMP_ID,EMP_NAME,DEPARTMENT,EMAIL
101,John Smith,Finance,[email protected]
102,David Brown,IT,[email protected]
OIC needs to understand the structure of the file before the data can be mapped.
The file schema can be defined based on the actual file format.
Step 8: Transform the Data
Use the mapper to transform the source data into the target format.
Example:
CSV File
|
| EMP_ID
↓
Employee ID
| EMP_NAME
↓
Employee Name
| DEPARTMENT
↓
Department
| EMAIL
↓
Email
The transformed data can then be sent to the target application.
Step 9: Invoke the Target Application
After transformation, invoke the target application.
For example:
SFTP
↓
CSV
↓
Stage File
↓
Mapping
↓
Oracle Fusion HCM
The target could also be:
- Oracle Fusion ERP
- Oracle Database
- REST API
- SOAP service
- Another SFTP server
- Third-party SaaS application
Step 10: Archive the File
After successful processing, it is good practice to move the processed file from the inbound directory to an archive directory.
For example:
/inbound/employee/
After successful processing:
/archive/employee/
This helps prevent the same file from being processed repeatedly.
Complete File Transfer Flow
A typical implementation could look like:
Schedule
|
↓
FTP/SFTP Adapter
|
↓
Get Employee File
|
↓
Stage File
|
↓
Read File
|
↓
Validate Records
|
↓
Data Mapping
|
↓
Oracle Fusion HCM
|
↓
Move to Archive
File Formats Supported in OIC
File-based integrations commonly work with formats such as:
CSV
Example:
ID,NAME,DEPARTMENT
101,John,Finance
102,David,IT
CSV is frequently used for bulk data exchange.
XML
Example:
<Employee>
<EmployeeId>101</EmployeeId>
<Name>John</Name>
<Department>Finance</Department>
</Employee>
XML is useful when structured hierarchical data needs to be exchanged.
JSON
Example:
{
"employeeId": "101",
"name": "John",
"department": "Finance"
}
JSON is commonly used with REST-based applications.
Fixed-Width Files
Some legacy applications produce fixed-width files where each field occupies a predefined number of characters.
For example:
000101John Smith Finance
OIC can process such structured file data when the appropriate schema is configured.
Handling Large Files in OIC
Large files require careful design.
Suppose an organization receives a file containing:
1,000,000 records
Trying to load and process the entire file in one operation may not be the best approach.
Instead, consider processing the file in segments when the integration and business requirements support it.
Conceptually:
Large File
|
↓
Stage File
|
+---- Segment 1
|
+---- Segment 2
|
+---- Segment 3
|
+---- Segment 4
Segment-based processing can help manage memory and processing requirements.
File Naming Conventions
A good file naming convention makes automated processing easier.
For example:
employee_20260824.csv
invoice_20260824.csv
customer_20260824.csv
For multiple files:
employee_YYYYMMDD_HH24MISS.csv
Example:
employee_20260824_230000.csv
Avoid ambiguous names such as:
data.csv
file1.csv
newfile.csv
Meaningful names make operational support much easier.
Error Handling in File Transfer Integrations
File processing can fail for several reasons.
Common failures include:
- SFTP connection failure
- Authentication failure
- File not found
- Invalid file format
- Invalid data
- Duplicate file
- Mapping error
- Target application failure
- Network timeout
- Permission issue
A good integration should handle these errors properly.
For example:
File Processing
|
↓
Validation
/ \
Valid Invalid
| |
↓ ↓
Process Error Handling
| |
↓ ↓
Archive Error Notification
Duplicate File Processing
Duplicate file processing is a common production issue.
For example, if the same file remains in the inbound directory after successful processing, the next scheduled execution might process it again.
A better approach is:
Inbound
↓
Process
↓
Success
↓
Archive
If processing fails:
Inbound
↓
Process
↓
Failure
↓
Error/Rejected
This helps maintain a clean file lifecycle.
Retry Strategy
Temporary failures can happen because of:
- Network problems
- Temporary application downtime
- SFTP availability issues
- API timeout
- Database connectivity issues
A retry strategy can help recover from transient failures.
However, retries should be designed carefully. Repeating a transaction without considering idempotency can result in duplicate business transactions.
Security Best Practices
File integrations often contain sensitive business data.
Follow appropriate security practices.
Use SFTP Instead of FTP Where Appropriate
SFTP provides encrypted communication and is generally preferred when transferring sensitive files.
Protect Credentials
Do not hard-code usernames, passwords, or private keys inside integration logic.
Use OIC connections and appropriate security mechanisms.
Restrict File Permissions
Ensure only authorized users and applications can access inbound and outbound directories.
Use Secure Network Connectivity
For integrations involving on-premises systems, use the appropriate OIC connectivity architecture, such as the connectivity agent where required.
File Transfer Integration with On-Premises Systems
Many organizations have on-premises applications and file servers.
OIC can integrate cloud applications with on-premises environments using the appropriate connectivity mechanisms.
A typical architecture can look like:
Oracle Cloud
|
OIC
|
Connectivity Agent
|
↓
On-Premises Network
|
SFTP / Application
This allows cloud-based integrations to communicate with systems that are not directly exposed to the public internet.
Monitoring File Integrations
Monitoring is an important part of production support.
From the OIC monitoring capabilities, administrators can investigate integration executions and failures.
When a file integration fails, check:
- Did the scheduled integration start?
- Was the SFTP connection successful?
- Was the file found?
- Was the file downloaded?
- Did Stage File process the file?
- Did mapping succeed?
- Did the target application respond?
- Was the file archived?
- Was an error generated?
A structured troubleshooting approach makes production support much easier.
Common File Transfer Problems and Solutions
| Problem | Possible Cause | Solution |
|---|---|---|
| File not found | Incorrect directory/pattern | Verify SFTP path and file pattern |
| Authentication failed | Incorrect credentials/key | Validate connection configuration |
| Mapping failure | Incorrect file structure | Verify schema and mappings |
| Duplicate processing | File not archived | Move successful files to archive |
| Large file failure | File too large for processing approach | Use appropriate staged/segmented processing |
| Target failure | Application/API unavailable | Check target and retry strategy |
| Permission denied | Incorrect SFTP permissions | Verify directory permissions |
| Invalid CSV | Incorrect delimiter/format | Verify file structure |
Best Practices for OIC File Transfer Integration
1. Use Meaningful Integration Names
For example:
Process_Employee_File
Import_Customer_Data
Process_Daily_Invoices
2. Use Clear Folder Structures
For example:
/inbound
/processing
/archive
/error
This makes file management easier.
3. Archive Successfully Processed Files
Do not leave processed files in the inbound directory.
4. Separate Error Files
Files that cannot be processed should be moved or copied to an appropriate error/rejected location when the design requires it.
5. Validate Files Before Processing
Check:
- File name
- File extension
- Required columns
- Record structure
- Mandatory fields
6. Design for Reprocessing
Production failures are inevitable.
Design the integration so that failed files can be corrected and reprocessed safely.
7. Avoid Hard-Coding
Use appropriate configuration mechanisms for environment-specific values.
8. Monitor Regularly
Set up operational monitoring and alerts according to the organization’s support requirements.
Real-World Example: Customer Data Integration
Consider a company that receives customer information from a third-party system.
Every morning:
Customer System
|
↓
SFTP Server
|
↓
customer_20260824.csv
|
↓
OIC Schedule Integration
|
↓
Stage File
|
↓
Validate
|
↓
Transform
|
↓
Oracle Fusion
|
↓
Archive File
This approach provides an automated and repeatable process for transferring customer data.
File Transfer Integration vs REST Integration
Both file-based and API-based integrations have their place.
| Feature | File Transfer | REST API |
|---|---|---|
| Data exchange | Files | API payload |
| Typical processing | Batch | Real-time |
| Large data sets | Often suitable | Depends on API |
| Scheduling | Common | Usually request-driven |
| Typical format | CSV/XML/JSON | JSON/XML |
| Example | Daily employee file | Create employee API |
A business requirement should determine which approach is appropriate.
Interview Questions on File Transfer Integration in OIC
1. What is File Transfer Integration in OIC?
It is an integration pattern used to transfer, read, process, transform, and write files between systems using OIC capabilities such as the FTP Adapter and Stage File action.
2. What is the FTP Adapter used for?
It is used to connect OIC to FTP/SFTP servers and perform file-related operations.
3. What is Stage File in OIC?
Stage File is an integration action used to temporarily stage and process files within an OIC integration.
4. How do you process a CSV file in OIC?
A typical approach is:
FTP/SFTP
↓
Get File
↓
Stage File
↓
Read File
↓
Map Data
↓
Invoke Target
5. How can you avoid duplicate file processing?
Move successfully processed files from the inbound directory to an archive directory and implement appropriate file-tracking/idempotency logic where required.
6. How do you process large files?
Use an appropriate file-processing strategy, including staged or segmented processing where supported and suitable for the file size and business requirement.
7. What happens if the target application fails?
Implement fault handling and an appropriate retry/reprocessing strategy so that failures can be investigated and recovered without creating duplicate transactions.
8. Why is SFTP preferred over FTP?
SFTP provides secure, encrypted file transfer and is generally more appropriate for sensitive enterprise data.
Conclusion
File Transfer Integration in OIC provides a powerful way to automate file-based communication between enterprise systems.
A typical OIC file integration can combine:
Schedule
↓
FTP/SFTP Adapter
↓
Stage File
↓
Read File
↓
Validate
↓
Transform
↓
Target Application
↓
Archive
Whether you are processing employee files, invoices, customer data, financial transactions, or legacy application files, OIC provides the connectivity and integration capabilities needed to automate the process.
The key to a reliable production implementation is not simply transferring a file. You should also consider validation, transformation, error handling, security, duplicate prevention, archiving, monitoring, and reprocessing.
With a well-designed architecture, OIC can turn manual file exchanges into secure, automated, and maintainable enterprise integrations.
