I’m experiencing issues loading Netsuite transaction table data into ADLS Gen2 via an ODBC connection in Azure Synapse. What are the recommended troubleshooting steps or configuration changes to fix this?

To troubleshoot the issues you're experiencing with loading Netsuite transaction table data into ADLS Gen2 via an ODBC connection in Azure Synapse, consider the following steps:

1. Check Connection String: Ensure that your ODBC connection string is correctly configured. Verify that all parameters such as server address, database name, user ID, and password are accurate. Sample ODBC connection string:

      Driver={SQL Server Native Client 11.0};Server=tcp:{your_server}.database.windows.net,1433;Database={your_database};Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
   

2. Connection Timeout: Increase the connection timeout setting in your connection string to allow for longer connection attempts. Consider setting it to 300 seconds to accommodate any temporary network issues.
3. ODBC Driver Version: Ensure that you are using the latest version of the ODBC driver for Netsuite. An outdated driver may lead to compatibility issues.
4. Network Stability: Check your network stability and ensure that there are no intermittent connectivity issues between Azure Synapse and the Netsuite ODBC driver. You can use tools to monitor network performance.
5. Integration Runtime Configuration: Use the Diagnostics tab in the Integration Runtime Configuration Manager to test the connection. This can help identify if the connection is being reset or disabled during runtime.
   • Launch Integration Runtime Configuration Manager.
   • Switch to the Diagnostics tab.
   • Test the connection with the correct parameters.
6. Load Balancing: If you're experiencing heavy load on the server, consider scaling up your dedicated SQL pool to handle more requests and reduce the chance of timeouts.
7. Error Handling: Implement error handling in your pipeline to gracefully manage connection failures. This can include retries or logging errors for further analysis.

If these steps do not resolve your issue, you may want to create a support ticket for further assistance from the Azure engineering team.

References:

• Troubleshoot connectivity issues in dedicated SQL pool (formerly SQL DW)
• Resolve Azure Synapse Studio connectivity issues

Hi @Suraj Surendran

Thank you contacting to Microsoft QA, below are mitigation steps that may help you to resolve the issue -

Use Self-hosted Integration Runtime (SHIR)

• Install 64-bit SuiteAnalytics ODBC driver on SHIR VM (Azure IR won’t work).

Enable TCP KeepAlive

• Set KeepAliveTime to 180000 ms (3 min) in Windows registry to prevent idle disconnects.

Batch the Data Load

• Query in daily/weekly slices using trandate filters.
• Select only required columns (avoid SELECT *).

Pipeline Settings

• Retry: 3–5 times, 5-min interval.
• Moderate parallelism (start with 3 sessions).
• Increase connection/query timeouts in DSN.

Other Checks

• Update ODBC driver, use NetSuite2.com endpoint.
• Ensure SHIR VM has firewall/proxy clearance.
• Refresh tokens if using TBA.

Optional Alternatives

Use JDBC via Synapse Spark or SuiteQL REST API for chunked pulls.

Use Self-hosted Integration Runtime (SHIR)

• Install 64-bit SuiteAnalytics ODBC driver on SHIR VM (Azure IR won’t work).

Enable TCP KeepAlive

• Set KeepAliveTime to 180000 ms (3 min) in Windows registry to prevent idle disconnects.

Batch the Data Load

• Query in daily/weekly slices using trandate filters.
• Select only required columns (avoid SELECT *).

Pipeline Settings

• Retry: 3–5 times, 5-min interval.
• Moderate parallelism (start with 3 sessions).
• Increase connection/query timeouts in DSN.

Other Checks

• Update ODBC driver, use NetSuite2.com endpoint.
• Ensure SHIR VM has firewall/proxy clearance.
• Refresh tokens if using TBA.

Optional Alternatives

Use JDBC via Synapse Spark or SuiteQL REST API for chunked pulls.