Configure incremental copy by using watermark from Oracle Netsuite through ODBC to Azure SQL DB on Azure Synapse

Question

Hi,

I'm trying to configure incremental copy activity on azure synapse pipeline. The data source is from Oracle Netsuite, which I managed to create the link service using ODBC and I am also using self-hosted integration runtime on the server. The main objective of this pipeline is to ensure that the table copy to Azure SQL DB by using incremental function. Below is the pipeline architecture:

First, I have created a watermarktable on Azure SQL DB. In the pipeline, I used lookup with the query below to create LookupOldWaterMarkActivity.

select * from watermarktable	

For the LookupNewWaterMarkActivity, I am getting the max date from the date column of the selected table:

select MAX(lastmodifieddate) as NewWatermarkvalue from table

In the IncrementalCopyActivity, the source dataset is from odbc and Azure SQL DB as sink . Then I use the query below to get the rows after LookupOldWaterMarkActivity and before LookupNewWaterMarkActivity.

select * from entity where lastmodifieddate > nlapiFormatDateTime('@{activity('LookupOldWaterMarkActivity').output.firstRow.WatermarkValue}','yyyy-MM-dd HH:mm:ss') and lastmodifieddate <= nlapiFormatDateTime('@{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}','yyyy-MM-dd HH:mm:ss')

When I tried to debug the pipeline , this is the result:

Below is the error code:

{
    "errorCode": "2200",
    "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [S1000] [NetSuite][ODBC 64bit driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# lie1j66fb9fahswsb706[400],Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [S1000] [NetSuite][ODBC 64bit driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# lie1j66fb9fahswsb706[400],Source=NQoa27.dll,'",
    "failureType": "UserError",
    "target": "IncrementalCopyActivity",
    "details": []
}

I have lookup online on the error, usually it was caused by date formatting. I have tried to format all the date but still have the same problem.Appreciate if someone can provide advise on this error.

Thank you.

Answer 1

Hi Wendy Chua ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding , you are trying to copy incremental records from Oracle DB to Azure SQL DB , however, it is throwing odbc related error at the source side.

From the error message it looks has ODBC driver issue. Could you please check is ODBC driver installed and has DSN setup in system where SHIR is installed? If not, kindly do the same and see if that helps.

Related documentations: ODBC Driver for Oracle

Installing the Software (ODBC)

Configuring the ODBC Driver for Oracle

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Answer 2

To troubleshoot this issue, you can consider the following steps:

Verify the ODBC Configuration:

• Double-check the ODBC configuration for the Oracle Netsuite connection. Ensure that the driver, connection string, and credentials are correctly specified.
• Test the ODBC connection independently to confirm if it can successfully connect to the Oracle Netsuite data source.

Validate Query Syntax:

• Review the SQL queries used in your pipeline activities (LookupOldWaterMarkActivity, LookupNewWaterMarkActivity, and IncrementalCopyActivity).
• Confirm that the table names, column names, and date formatting functions (nlapiFormatDateTime) are accurate and compatible with the Oracle Netsuite database schema.

Enable Diagnostic Logging:

• Enable diagnostic logging for your Azure Synapse pipeline and investigate the logs for any additional error details or warnings that might provide insights into the cause of the issue.
• Examine the logs related to the ODBC connection, query execution, and data retrieval steps.

Reach out to Support:

• If the issue persists, consider contacting Microsoft Azure Support for further assistance. Provide them with the error details, pipeline configuration, and any additional relevant information to help troubleshoot the problem effectively.

Additionally, you may want to consult the documentation and resources specific to the NetSuite ODBC driver for any known limitations, troubleshooting steps, or recommended configurations when working with Azure Synapse.