SQL Server Bulk Import error when running inside docker container on Windows

Question

I am running the SQL Server for Linux container (mcr.microsoft.com/mssql/server) on Docker for Windows. I keep getting an error when I try to do a Bulk Import or OpenRowSet of data stored in an Azure Blob using the Azure Data Studio for Windows.

Command:

BULK INSERT dbo.MY_TABLE
FROM 'data/aaa/bbb/xxxxxxx.CSV'
WITH ( 
    DATA_SOURCE = 'SxxxxxxxxxxxxSecured', 
    FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 500
);
GO

Error message:

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "data/aaa/bbb/xxxxxxx.CSV" could not be opened. Operating system error code 32(The process cannot access the file because it is being used by another process.).

I have done the following...

• Validated that I have internet connectivity from inside the docker container (-it ping google.com works after installing ping)
• Validated that I properly have setup my credentialed connection to the storage account. I get different error messages if I mistype the path to the CSV file (file not found). I am using a private container in Azure Blob with a SAS key. I would presume this means that it can access and validate that the file exists.
• Validated that there is nothing using port 10000 inside the container (using netstat running as root inside the container), this was based on several other SO questions with this error
• Validated that I can do the exact same thing on my Mac running Docker for Mac and it works fine. But with Docker for Windows and Windows 10 as my host OS, it seems to not work.

Answer 1

Error: Operating system error code 32(The process cannot access the file because it is being used by another process.)

Starting with error, it will occur when another application is accessing the backup file and preventing SQL backup pro from writing to it.

**

Consider the following steps for alternative

**

1. Create the credentials

Check the following code block for creating the credentials

CREATE DATABASE SCOPED CREDENTIAL "Your storage account name"
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'Your secret key';

2. Referring the blob location for dataset

CREATE EXTERNAL DATA SOURCE "your storage account name" WITH ( TYPE = BLOB_STORAGE, LOCATION = 'Mention your blob storage location', CREDENTIAL = Your storage account name );

The below code is for "Openrowset"

SELECT * FROM OPENROWSET(
   BULK 'week3/inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   FORMAT = 'CSV',
   FORMATFILE='invoices.fmt',
   FORMATFILE_DATA_SOURCE = 'storage account name'
   ) AS DataFile;

3. Use Bulk Insert

   BULK INSERT Colors2

   FROM 'week3/inv-2017-01-19.csv'

   WITH (DATA_SOURCE = 'Your storage account name', FORMAT = 'CSV');

4. Accessing data in CSV file using container

   CREATE EXTERNAL DATA SOURCE Containername

   WITH (
       TYPE = BLOB_STORAGE,
       LOCATION = https://newinvoices.blob.core.windows.net/datasetfoldername',
       CREDENTIAL = Your sorage account name
   );