Moving Data From Oracle SQL to Azure SQL Dynamically

Question

Hi All,

@{activity('GetTableColumns').output.value[0].ColumnNames} - This line reading my columns from the lookup activity successfully when I try move data from MS SQL(Source) to Azure SQL Databases(Destination).

But it's reading the columns from the lookup activity successfully when I try move data from Oracle(Source to Azure SQL Databases(Destination).

Looking for a solution.

Answer 1

@Ramesh, Harish

Thanks for the question and using MS Q&A platform.

The issue seems to be with reading the columns from the lookup activity when the source is Oracle SQL.

Here are a few steps to troubleshoot and resolve this issue:

Check the Lookup Activity: Ensure that the query in your lookup activity is correctly fetching the column names from the Oracle database. You can test this query directly in Oracle SQL Developer or any other SQL client to verify it returns the expected results.

Data Type Compatibility: Verify that the data types of the columns in Oracle SQL are compatible with Azure SQL. Sometimes, data type mismatches can cause issues during data movement.

Mapping Data Flow: Use a mapping data flow in ADF to explicitly map the columns from Oracle SQL to Azure SQL. This can help in handling any discrepancies in column names or data types.

Dynamic Content: Ensure that the dynamic content expression @{activity('GetTableColumns').output.value[0].ColumnNames} is correctly referenced in your pipeline. You might need to debug the pipeline to check if the expression is evaluated correctly.

Integration Runtime: Make sure that the integration runtime used for connecting to Oracle SQL is properly configured and has the necessary permissions to access the Oracle database.

Error Logs: Check the error logs in ADF for any specific error messages that can give you more insights into what might be going wrong.

could you please provide more details about the error messages or the specific steps you're taking,

I hope these steps help you resolve the issue. Please Let me know if issue persists.

Answer 2

Hello Ramesh, Harish,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are in need to dynamically handling columns from Oracle SQL and transferring data to Azure SQL while leveraging ADF's dynamic content and integration capabilities.

Basically, you need to put into consideration the followings:

• Schema Compatibility: If column names or data types differ, use Mapping Data Flow to define transformation rules dynamically.
• Error Handling: Add error handling in case some columns or data types fail during movement.

Your pipeline flow will be similar to these steps: 1. Lookup Activity > 2. ForEach Activity > 3. Copy Data Activity > 4. Sink Configuration (Azure SQL).

These links below will provide step-by-step guidance and detailed insights to help you implement your solution effectively:

• https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle
• https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/database/oracle-to-sql-database-guide
• https://www.youtube.com/watch?v=h1LynU-T7OE

Similar answers to your question: https://learn.microsoft.com/en-us/answers/questions/1366948/data-migration-from-oracle-to-azure-sql-database-t and https://learn.microsoft.com/en-us/answers/questions/1341002/copy-on-prem-oracle-database-data-to-azure-sql-dat

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.