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).
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');
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).
Related Azure SQL tutorials:
- How to create SQL authentication user in Azure SQL database
- Reset Password Azure SQL database
- Azure SQL database query history
- Read only replica Azure SQL
- Bulk loading data to Azure SQL
- Azure SQL Database Schema
- Cannot drop schema because it is being referenced by object
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.