Site icon TheWindowsUpdate.com

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

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

Our customer has the following scenario:

 

 

 

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!

 

Exit mobile version