Lesson Learned #280: Cannot bulk load. The file "xyz" does not exist or you don't have file access

[Jose_Manuel_Jurado]

Our customer has the following scenario:

• They need to import CSV file from a blob storage to Azure SQL Database. 
• To perform this operation our customer is using BULK insert option.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123!!!!'

CREATE DATABASE SCOPED CREDENTIAL [JM_Scope] 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2021-06-08&ss=b&srt=sco&sp=rwdlaciyx&se=2023-01-13T16:39:22Z&st=2023-01-13T08:39:22Z&spr=https&sig=k6XYHDjqsco7fyuGt...'

CREATE EXTERNAL DATA SOURCE [JM_EXT_DSource] 
WITH ( TYPE = BLOB_STORAGE, 
LOCATION = 'https://blogstorage.blob.core.windows.net/import/', CREDENTIAL = [JM_Scope] );

CREATE TABLE Table1 (ID INT,Name Varchar(50))

CREATE OR ALTER PROCEDURE [dbo].[LoadCSVintoAzureSQL]  
AS
BEGIN
TRUNCATE table [dbo].[Table1]
BULK INSERT [dbo].[Table1]
FROM 'names.txt'
WITH (DATA_SOURCE = 'JM_EXT_DSource'
     ,FORMAT      = 'CSV',
     FIELDTERMINATOR = ','
	 , ROWTERMINATOR = '\n')
END;

Our customer every time that they execute the store procedure EXEC LoadCSVIntoAzureSQL they got the following error message: Msg 4860, Level 16, State 1, Procedure LoadCSVIntoAzureSQL, Line 5 [Batch Start Line 14] - Cannot bulk load. The file "names.txt" does not exist or you don't have file access rights.

We checked the blob storage, container and the file exist, but, in the definition of the data Source [JM_EXT_DSource] we found that it finishes with / in the path of the location. So, removing this last character / in the name our customer was able to import the data. 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123!!!!'

CREATE DATABASE SCOPED CREDENTIAL [JM_Scope] 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2021-06-08&ss=b&srt=sco&sp=rwdlaciyx&se=2023-01-13T16:39:22Z&st=2023-01-13T08:39:22Z&spr=https&sig=k6XYHDjqsco7fyuGt...'

CREATE EXTERNAL DATA SOURCE [JM_EXT_DSource] 
WITH ( TYPE = BLOB_STORAGE, 
LOCATION = 'https://blogstorage.blob.core.windows.net/import', CREDENTIAL = [JM_Scope] );

CREATE TABLE Table1 (ID INT,Name Varchar(50))

CREATE OR ALTER PROCEDURE [dbo].[LoadCSVintoAzureSQL]  
AS
BEGIN
TRUNCATE table [dbo].[Table1]
BULK INSERT [dbo].[Table1]
FROM 'names.txt'
WITH (DATA_SOURCE = 'JM_EXT_DSource'
     ,FORMAT      = 'CSV',
     FIELDTERMINATOR = ','
	 , ROWTERMINATOR = '\n')
END;

If you face this problem, check the location and the definition of the external data source. 

Enjoy!