Cannot bulk load because the file could not be opened. operating system error code (null). azure

In this Azure SQL tutorial, I will share how I resolved the error Cannot bulk load because the file could not be opened. Operating system error code (null).

Table of Contents

Cannot bulk load because the file could not be opened. operating system error code (null)

Recently, when I was working with the BULK INSERT statement in the Azure SQL database, I faced an error. I was trying to bulk insert data from my local storage into the Azure SQL database.

It was a CSV file that I was trying to upload. Below is the code that I was trying to execute:

BULK INSERT [dbo].[Customers]
FROM 'C:\Users\Blades\Documents\test.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW=2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Then I faced the error Failed to execute query. Error: Cannot bulk load because the file “C:\Users\Blades\Documents\test.csv” could not be opened. Operating system error code (null).

I was using SQL Server management studio so that I could upload a file from my local storage.

I understood from the error that my file was not being read. I checked the location of my CSV file again, but everything was fine (If the file is not available in the specified path, you may face this error).

cannot bulk load because the file could not be opened. operating system error code (null)

I did some research on this error and found that the Azure SQL database does not support reading files from the local file system.

I again searched for an alternative. Luckily, I found an alternative way to do it. But for this, I needed to create a storage account in Microsoft Azure.

If you do not know how to create a storage container in Azure, you can read our article on Backup and restore SQL server to Azure blob storage.

The idea is to create a storage account and create a storage container. Then upload the CSV file to the storage container.

After that, we need to create an external data source in our Azure SQL database. We specify the storage container as the external data source. I created the external data source with the below T-SQL query:

CREATE EXTERNAL DATA SOURCE MyExternalSource
WITH ( TYPE = BLOB_STORAGE,
          LOCATION = 'https://groupazurestorage.blob.core.windows.net/sqlserverbackupdb'

);

In the above code, MyExternalSource is the external data source name, in the LOCATION parameter, we will specify the link to our Azure blob container, where sqlserverbackupdb is the name of my Azure blob container.

After following the above steps, I was able to use the BULK INSERT statement along with a CSV file. My new T-SQL query looked like this:

BULK INSERT [dbo].[Customers]
FROM 'MyExternalSource'
WITH (FORMAT = 'CSV'
      , FIRSTROW=2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

Cannot bulk load because the file could not be opened. operating system error code null azure — Successfully bulk loaded data

Note that I had configured my Azure blob container access to be Public. If you want to configure your Azure blob container with Private access, you need to create a database scoped credential.

I have explained this process in the article on Bulk loading data to Azure SQL.

Thus, this was my experience with the error Cannot bulk load because the file could not be opened. Operating system error code (null).