UserErrorSqlDWCopyCommandError

Ghatage, Ravindra (R) 0 Reputation points
2024-04-28T05:26:07.45+00:00

After 10 hrs of executing Copy Data activity in ADF, the following error occurred.

ADLS to staging is getting completed within hour but stg to Synapase load taking lot of time and After 10 hrs of executing copy data getting failed with following error. Please assist us in fixing the following issue

Operation on target 4-Gold_Synapse_CPY failed: ErrorCode=UserErrorSqlDWCopyCommandError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL DW Copy Command operation failed with error 'Cannot bulk load because the file "/4943c116-8837-4256-89c4-8e8bf805c72f/SynapseImportCommand/part-00000-tid-3352202748690261118-edea6e64-77a1-470d-a1d6-a4e0953159c3-235-1-c000.snappy.txt" is incomplete or could not be read. Operating system error code 5(Access is denied.).',Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot bulk load because the file "/4943c116-8837-4256-89c4-8e8bf805c72f/SynapseImportCommand/part-00000-tid-3352202748690261118-edea6e64-77a1-470d-a1d6-a4e0953159c3-235-1-c000.snappy.txt" is incomplete or could not be read. Operating system error code 5(Access is denied.).,Source=.Net SqlClient Data Provider,SqlErrorNumber=4862,Class=16,ErrorCode=-2146232060,State=5,Errors=[{Class=16,Number=4862,State=5,Message=Cannot bulk load because the file "/4943c116-8837-4256-89c4-8e8bf805c72f/SynapseImportCommand/part-00000-tid-3352202748690261118-edea6e64-77a1-470d-a1d6-a4e0953159c3-235-1-c000.snappy.txt" is incomplete or could not be read. Operating system error code 5(Access is denied.).,},],'

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,644 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 15,831 Reputation points
    2024-04-28T17:44:14.7633333+00:00

    This is a known problem so you may need to open a ticket https://azure.microsoft.com/en-us/support/create-ticket

    Or based on this old thread, two approaches to solve the problem:

    1. Use Snapshot data to avoid read-lock error on CSV(in-place update): Why do I see the error message - cannot bulk load because the file is incomplete or could not be read?
    2. When exporting data, use append-only mode with dirty read support: When do I use Append only mode for a historical view of changes?
    0 comments
  2. AnnuKumari-MSFT 31,151 Reputation points Microsoft Employee
    2024-05-06T05:43:39.1566667+00:00

    Hi Ghatage, Ravindra (R) ,

    Thanks for your query on Microsoft Q&A platform.

    In addition to the above details provided by community , I would like to share few more possibility why the above error might have occurred , however, I hope the target datastore for you is dedicated sql pool . Could you please share what is the DWU of dedicated SQL pool which you are using as the sink. Kindly check the below points:

    1. Check the firewall setting on the storage account (see below, but is essentially enabling access from all networks).
    2. Check in case you are using SAS token and the end date of the shared access signature has passed and SAS token is expired.

    Relevant resources that can help:

    https://techcommunity.microsoft.com/t5/azure-sql-blog/troubleshooting-bulk-insert-and-openrowset-issues-on-azure-sql/ba-p/664734

    https://learn.microsoft.com/en-us/answers/questions/124104/cannot-bulk-load-because-the-file-sqlfiles-csv-cou

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

    0 comments