Hi please read my next comments:

Using OTM Web Services (Recommended) Oracle OTM provides a comprehensive set of web services (REST/SOAP) for data extraction. You can use these APIs to pull data into SQL Server via SSIS.

Steps: Configure OTM Web Services:

1.- Enable the required web services in OTM for the data you need.

2.- Obtain credentials and endpoint URLs from your OTM instance.

SSIS Integration:

1.- Use a Script Task or a third-party SSIS connector for REST/SOAP APIs.

2.- Make HTTP calls to OTM web services, fetching the required data.

Intermediate Staging:

1.- Save the fetched data into flat files or an intermediary database if needed.

Load into SQL Server:

1.- Use an SSIS Data Flow task to load the staged data into SQL Server.

Advantages:

1.- API-based access ensures you're not directly affecting the database performance.

2.- It aligns with Oracle’s best practices for integration.

Recommendations:

1.- If you need real-time integration, prefer the OTM Web Services approach.

2.- For batch processing, exporting data via FTI/OBIEE or direct database access can be more straightforward.

Ensure data security and compliance, especially when working with sensitive transportation data. Test your solution in a non-production environment to validate performance and accuracy.

Considerations:

Performance: Optimize queries on the Oracle side to fetch only required data. Use incremental data loading where possible.

Security: Ensure sensitive data is handled securely during transfer by using encrypted connections.

Testing: Thoroughly test the data flow for consistency and performance before production deployment.

I hope this can helps you if not, please contact me at [email protected] for more information without cost. Regards. Marco.