Copy activity failed: Failure happened on 'Source' side

Question

Trying to get data from PGSQL to blob using copy activity give below error after query runs for ~30 min

Operation on target Copy Customer failed: Failure happened on 'Source' side. ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY008] [Microsoft][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: VERROR; canceling statement due to statement timeout(File postgres.c; Line 3094; Routine ProcessInterrupts; ),Source=mspsql27.,'

Answer 1

@PPThere are a couple of possibilities for the timeout error occurring even though you have a 1-hour limit set in Azure Data Factory (ADF). Here's how to investigate further:

Double-Check Timeout Settings:

• Ensure the timeout setting is applied correctly. Check both the linked service connection string for PostgreSQL and the specific copy activity settings within the pipeline. The timeout in the linked service takes precedence, but it's good practice to verify both.

Investigate Overnight Performance Degradation:

• Resource Contention: During overnight hours, there might be increased competition for Azure resources in your region. This can lead to slower processing, even if the query itself hasn't changed. Consider monitoring resource utilization during these timeframes.
• Database Maintenance: Check if your PostgreSQL database undergoes scheduled maintenance windows overnight. This can temporarily impact query performance.

Analyze Query Execution:

• Use PostgreSQL tools like EXPLAIN or pg_stat_activity to analyze the query's execution plan and identify potential bottlenecks. This can help identify if the query itself is taking longer than usual, even if ADF hasn't changed.