"errorCode": "2200", "message": "ErrorCode=UserErrorFailedToConnectOdbcSource

Question

I was hoping you could help me solve this error which is happening for a couple of days.
I'm taking data from Access Live Db using a synapse Pipeline when I checked in Monitor, a few of copy activities have ran successfully while a couple had this below error and failed. There were also a few which showed timeout. I Just recently created ODBC connection and pipeline worked fine for a couple days.
{ "errorCode": "2200", "message": "ErrorCode=UserErrorFailedToConnectOdbcSource,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=,Source=,'", "failureType": "UserError", "target": "Copy Data", "details": [] }

Answer 1

Apologies', I want to render my mistake that Source is not an Access DB but sage.
and secondly, I have already mentioned that the copy activity is working fine for a few tables inside For Each activity but then fails for a couple and time out for the remaining.
Timeout is already at 15 min.
Those copy activities that have been successfully executed took at most 30s.
So Authentication won't be a problem as if it was a connection won't be established for the selective few either.

Answer 2

The error you're encountering in Azure Synapse Analytics when attempting to connect to an Access Live DB using an ODBC connection suggests an intermittent failure in the connection between Synapse and the ODBC source. The error message ErrorCode=UserErrorFailedToConnectOdbcSource typically points to issues such as network connectivity, ODBC driver configuration, or timeout settings.

Here are a few steps you can take to troubleshoot this:

1. ODBC Driver Stability: Ensure that the ODBC driver you're using is stable and up-to-date. Sometimes, driver issues can cause intermittent connectivity problems. Check for any available updates and apply them.
2. ODBC Connection Timeout: Review the timeout settings in the ODBC connection and Synapse pipeline. Increase the timeout if necessary, as longer queries or network instability can cause timeouts.
3. Network Issues: Intermittent network issues can cause some copy activities to succeed and others to fail. Ensure there is consistent network connectivity between Synapse and the ODBC source.
4. Retry Logic: Implement retry logic in your Synapse pipeline to handle intermittent connection failures. This can help mitigate temporary disruptions.
5. Logging and Monitoring: Enable verbose logging to capture more detailed information about the failed connections, which can help pinpoint the issue.
6. Resource Limits: Check if your Access DB or network infrastructure is hitting any resource or connection limits, which might explain why some jobs run successfully while others fail.

By following these steps, you should be able to narrow down the cause of the connection failures and either resolve the issue or provide more diagnostic information for further troubleshooting.