In SQL, bulk insert from blob is giving error

Question

I have CSV file in Azure Blob storage and I want to insert file content to my MS SQL table. My CSV file and table which I'm using in my code are having same number of columns.

I have used Bulk insert command as specified here: Importing data from a file in Azure blob storage.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxxx';

CREATE DATABASE SCOPED CREDENTIAL AzureBlobStorageCredential 
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '<I kept my SAS token here without leading question(?) mark>';


CREATE EXTERNAL DATA SOURCE GSCSVFileAzureBlobStorage
WITH (  TYPE = BLOB_STORAGE, 
        LOCATION = 'https://myaccount.blob.core.windows.net/csvfileshare', 
        CREDENTIAL= AzureBlobStorageCredential    );

BULK INSERT RawItemData
FROM 'itemdata_csv_test.csv'
WITH (DATA_SOURCE = 'GSCSVFileAzureBlobStorage',FORMAT = 'CSV',FIELDTERMINATOR = ',',FIRSTROW=2);

If I use my local file path bulk insert is working as expected, but if I read it from blob I get this error:

Cannot bulk load because the file "itemdata_csv_test.csv" could not be opened. Operating system error code 32 (The process cannot access the file because it is being used by another process.).

How can I find where exactly the issue is?

From the error message, seams that the .csv file is being used, can you check if it's being used? — Ivan Yang
@IvanYang, I created new container and uploaded file to it. Still getting same error. No one is using the file. — Saibaba B
Did you created a public container or a private container ? if it is private, you will need to generate sas token to access the file — Thomas
@Thomas, I actually tried both private and public. In both the cases I'm getting same error. — Saibaba B

Leon Yue Leon Yue · Accepted Answer · 2019-01-17T08:08:45

I find some informations about Azure Blob Storage error :"The process cannot access the file because it is being used by another process".

In this article says that the error message in the title is generally the result of a port already being in use. Check which of the storage services (blobs, tables, or queues) is failing to start, and see if anything on your computer is consuming that port.The most common case is blob storage not starting because BitTorrent is using port 10000.

Use the use netstat to determine which process has the port

netstat -p tcp -ano | findstr :10000

End this process and try again.

Hope this can helps you.

In SQL, bulk insert from blob is giving error

1 Answers