If you’re working with Oracle Fusion ERP and want to pull data into your SQL Server or other destinations using SSIS, this guide will walk you through how to do that using ZappySys JSON Source and an HTTP Connection with Basic Authentication.
This solution is ideal for ETL developers looking to automate data extraction from Oracle Cloud (Fusion ERP).
Prerequisites
- SQL Server Data Tools with SSIS installed
- ZappySys SSIS PowerPack
Download from here - Oracle Fusion ERP credentials (Username, Password, and Service URL)
Step 1: Create a New HTTP Connection
- In your SSIS project, right-click on Connection Managers and choose New ZappySys HTTP Connection.
- In the URL box, enter your Oracle Fusion REST API base URL
Syntax:https://<your-domain>.fa.<region>.oraclecloud.com
Example:https://servername.fa.us2.oraclecloud.com - Go to the Authentication tab:
- Select Basic Authentication
- Enter your Username and Password
For more information on HTTP Connection, check this article
Step 2: Configure JSON Source to Fetch Fusion ERP Data
- Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
- Double click on the DataFlow task to see DataFlow designer surface.
- From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
- Double click to edit
- Specify your Oracle Fusion REST API endpoint.
Example:https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/invoices - Check Use Credentials option. From dropdown Select existing connection OR Click new ZS-HTTP connection
- Configure like this
- Select Array Filter (Click Button to use Browse UI). Example for above URL you can pick
$.items[*] - Click Preview to view sample data.
Step 3: Configure Pagination for Oracle Fusion ERP Data
- Go to the Pagination Tab
- Select Pagination using Next Link Attribute
- Enter following Expression in the Next Link/Cursor textbox:
$.links[?(@.rel=='next')].href
Additional Tips
- For large datasets, enable pagination (e.g., using nextLink or offset/limit settings).
- ZappySys JSON Source also supports OAuth, Bearer Tokens, and other authentication types if needed.
- Use ZappySys REST API Task to perform POST/PUT requests if your use case requires it.
- Refer to the full Oracle Fusion REST API documentation for additional endpoints:
Oracle Fusion ERP API Docs
Conclusion
With ZappySys SSIS PowerPack, integrating Oracle Fusion ERP into your ETL workflows is straightforward and efficient. Whether you’re migrating data, building reports, or automating business processes, ZappySys provides the flexibility and power you need.
Get Started Now
Download ZappySys SSIS PowerPack