Inconsistent COPY Command Behavior: Data Loading from Azure Blob Storage to Synapse Analytics

Question

Hello community,

I've been encountering a perplexing issue while using the COPY command to load data from Azure Blob Storage into Azure Synapse Analytics (dedicated SQL pool). The problem is inconsistent, with the COPY command working at times and failing at other times, exhibiting a seemingly random behaviour. I'm consistently using the same CSV file URI for the data load.

Methods I've Tried:

Synapse Studio:

• When executing the COPY command through Synapse Studio, it runs without any errors, but no data gets loaded.

Python Script:

• Using a Python script to execute the COPY command results in occasional errors for a correct file, and sometimes it runs without any issues.

COPY Command Used:

COPY INTO my_schema.my_table ([data], [bouncerate])
FROM 'https://<account_name>.blob.core.windows.net/<container>/00c0def4-289f-43cf-998b-f805cde51a55/tmp119dx833.csv'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Storage Account Key',
    SECRET='<SECRET>'),
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ENCODING = 'UTF8'
);

I would appreciate any insights or suggestions on how to troubleshoot and resolve this inconsistent behaviour in the data loading process. Thank you!

Answer 1

Hello DataChannel,

Thank you for posting your query here!

I understand that you are encountering an inconsistency while using the COPY command to load data from Azure Blob Storage into Azure Synapse Analytics (dedicated SQL pool). The issue occurs with the COPY command working intermittently and failing at other times.

Can you please try to load multiple files at once for parallel processing and maximizing bulk loading performance with SQL pools using the COPY statement. File-splitting guidance is outlined in the following documentation. Also, for additional data loading best practices, you may refer to the following documentation. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/data-loading-best-practices

Please let us know if you have any further queries. I’m happy to assist you further.

---

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.