Azure Data Factory - Unable to log error/incompatible rows

Question

Hi,

I have 1m records in flat file and i need to load them in table which is already created in dedicated SQL pool

In copy activity, I have enabled fault tolerance and enabled logging to a particular AZure storage account.

But I am getting below error:-

"https://__.blob.core.windows.net/*/copyactivity-logs/44a3a763-653c-45d6-b9f9-5b4982a2b8b9/synapse-copy-statement/_rejectedrows/20230222_111817/QID18131061_1_1.Row.Txt" could not be opened. Operating system error code 5(Access is denied.).,Source=.Net SqlClient Data Provider,SqlErrorNumber=4861,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4861,State=1,Message=Cannot bulk load because the file**

It seems there is permission error. Please help me to resolve that / any alternative way to get the incompatible row details

Answer 1

Hi @Anonymous ,

Thanks for using Microsoft Q&A forum and posting your query.

As per the error message, it sounds like a permission issue. Could you please confirm if your ADF managed Identity or the Service principal you are using in the Storage linked service is able to access the storage account folder? I would recommend to double Storage Blob Data Contributor role is assigned to ADF managed identity or the service principal that you are using to access the storage account.

Ref doc: ADF - Azure Storage Linked service configuration

In case if you have already granted the above permission but still seeing the same error message, then please let us know below:

1. What Integration runtime is being used in source connector, sink connector and logs Storage connector?
2. Also please check if the Integration Runtimes used has firewall access to the respective storage account used for logging.
3. [{Class=16,Number=4861,State=1,Message=Cannot bulk load because the file** - This part of the error message makes me think that since your source has 1M records and if there a large number of incompatible records that are being rerouted to the log folder, and your file being locked and not allowing the next set of incompatible records to be logged as the file is being accessed by process in logging previous set of incompatible records. Basically, I'm suspecting a file lock issue if the number of incompatible records is huge. If that is the case, then I would recommend filing a support ticket for deeper investigation from product team as I don't a way to overcome it. Before doing that, what I would recommend is to create a dummy dataset with less records (which has few incompatible records) in your source side and do a copy test pointing the incompatible records logging to the same storage account and folder that you have used initially which is throwing the error. This test will make sure that if the issue is related to Permissions or if the issue is related to number if incompatible records and hence file lock issue.
4. Along with above confirmation, could you please share Copy activity Settings tab image that show the Logging configuration?

Hope this helps. Please let us know how it goes.

Thank you