How to load distinct values from parquet file to azure sql database using copy data activity in ADF,I don't want to use Dataflows

Alapati, Ravindranath Chowdary (UMKC-Student) 0 Reputation points
2024-03-22T23:39:27.3666667+00:00

I wrote the sql query in pre-copy script in SINK of copy data activity,I am getting error as below

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot bulk load because the file "http://<storageccount>.dfs.core.windows.net/gold/DAILY_LOAD/.parquet" could not be opened. Operating system error code 997(Overlapped I/O operation is in progress.).',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot bulk load because the file "http://<storageccount>.dfs.core.windows.net/gold/DAILY_LOAD/.parquet" could not be opened. Operating system error code 997(Overlapped I/O operation is in progress.).,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 "http://<storageccount>.dfs.core.windows.net/gold/DAILY_LOAD/*.parquet" could not be opened. Operating system error code 997(Overlapped I/O operation is in progress.).,},],'

my script

INSERT INTO SALES.DIM_COUNTRY

SELECT DISTINCT COUNTRY

FROM OPENROWSET(BULK 'http://<storageccount>.dfs.core.windows.net/gold/DAILY_LOAD/*.parquet',SINGLE_BLOB) AS [country_data]

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

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vahid Ghafarpour 17,875 Reputation points
    2024-03-23T05:24:16.95+00:00

    Thanks for posting your question in the Microsoft Q&A forum.

    The error message indicates that there is an issue with opening the specified file in the BULK operation due to an "Overlapped I/O operation is in progress" error. This could be caused by various reasons, such as network issues, file locking, or permissions.

    First you need to solve file lock

    ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **

    0 comments
  2. ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee
    2024-03-28T05:11:56.2666667+00:00

    Hi Alapati, Ravindranath Chowdary (UMKC-Student),

    Thank you for posting query in Microsoft Q&A Platform.

    Mentioned error, will come either of below situations.

    1. If file is getting processed by some other process. Make sure, you wait until that process overs and then try to access it.
    2. If file is not correct format and it's corrupted. Make sure file is correct format. You can try write query from synapse and test it.
    3. Check is file exists.

    Hope this helps. Please let me know if any further queries.

    Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.