Issue with ERRORFILE when BULK INSERTing from Azure Blob Storage

Question

I am trying to bulk insert a lot of CSV files from Azure Blob Storage into my Azure SQL database.

Here's how I am trying to achieve this:

IF EXISTS(SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('[sqldb1].[dbo].[TABLE_A_RAW]'))
    DROP TABLE [sqldb1].[dbo].[TABLE_A_RAW];
    
CREATE TABLE [sqldb1].[dbo].[TABLE_A_RAW]
(
        [COL1] varchar(60),
        [COL2] varchar(60),
        [COL3] varchar(60),
        [COL4] varchar(60),
        [COL5] varchar(60)
);
        
BULK INSERT [sqldb1].[dbo].[TABLE_A_RAW]
FROM 'TABLE_A.CSV'
WITH
    (
        DATA_SOURCE = 'myazureblobstoragecontainer',
        FORMAT = 'CSV',
        ERRORFILE = 'load_errors_TABLE_A',
        ERRORFILE_DATA_SOURCE = 'myazureblobstoragecontainer',
        FIRSTROW = 2,
        FIELDTERMINATOR = '0xE29691',
        ROWTERMINATOR = '0x0a'
    )
GO
    
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('[sqldb1].[dbo].[TABLE_B_RAW]'))
    DROP TABLE [sqldb1].[dbo].[TABLE_B_RAW];
    
CREATE TABLE [sqldb1].[dbo].[TABLE_B_RAW]
(
        [COL1] varchar(60),
        [COL2] varchar(60),
        [COL3] varchar(60),
        [COL4] varchar(60),
        [COL5] varchar(60),
        [COL6] varchar(60),
        [COL7] varchar(60),
        [COL8] varchar(60),
        [COL9] varchar(60)
);
        
BULK INSERT [sqldb1].[dbo].[TABLE_B_RAW]
FROM 'TABLE_B.CSV'
WITH
    (
        DATA_SOURCE = 'myazureblobstoragecontainer',
        FORMAT = 'CSV',
        ERRORFILE = 'load_errors_TABLE_B',
        ERRORFILE_DATA_SOURCE = 'myazureblobstoragecontainer',
        FIRSTROW = 2,
        FIELDTERMINATOR = '0xE29691',
        ROWTERMINATOR = '0x0a'
    )
GO
   

The code above was developed when I worked on an almost identical project (with identical deployment) and it worked without any issues. When I tried to run the code above for my current project, the error log files get created and so do the tables (as expected) but they are all empty and I get these errors:

Msg 4861, Level 16, State 1, Line 17
Cannot bulk load because the file "load_errors_TABLE_A" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Line 17
Cannot bulk load because the file "load_errors_TABLE_A.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Line 50
Cannot bulk load because the file "load_errors_TABLE_B" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Line 50
Cannot bulk load because the file "load_errors_TABLE_B.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

The error files are only created when I run the code above, meaning that they don't exist prior to running the code above as the error messages seem to indicate. When I comment-out the lines that say ERRORFILE and ERRORFILE_DATA_SOURCE (i.e.ERRORFILE = 'load_errors_TABLE_A',, ERRORFILE = 'load_errors_TABLE_B',, and ERRORFILE_DATA_SOURCE = 'myazureblobstoragecontainer',) and run the script again, then the bulk insert finishes without any errors (but the error files don't end up being created, obviously).

I want to BULK INSERT WITH ERRORFILEs so that I can track any truncations that occur during the operation, like I did in my previous project. I tried looking for similar posts but they all seem to mostly relate to local BULK INSERT operations where the error log files is also created/stored locally. The deployment for the previous project and this one are almost identical, as I mentioned above - They are both running SQL Server 2014 (12.0.2000.8) and I have read/write access to both the Azure DB and Blob Storage account + container.

Answer 1

1. Your SAS key has not expired, right? And please check the Allowed permissions.

2. Did you delete the question mark when you create the SECRET?

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12******2FspTCY%3D'

I've tried the following test, it works well. My csv file has no header.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '***';
go

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12&ss=bfqt&srt=sco&sp******%2FspTCY%3D'; -- dl


CREATE EXTERNAL DATA SOURCE MyAzureInvoices
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://***.blob.core.windows.net/<container_name>',
        CREDENTIAL = UploadInvoices
    );

BULK INSERT production.customer
FROM 'bs140513_032310-demo.csv'
WITH
    (
        DATA_SOURCE = 'MyAzureInvoices',
        FORMAT = 'CSV',
        ERRORFILE = 'load_errors_TABLE_B',
        ERRORFILE_DATA_SOURCE = 'MyAzureInvoices',
        FIRSTROW = 2
    )
GO

Answer 2

The culprit ended up being the permissions, as @joseph-xu suggested in his answer below.

Current Project:

Old Project:

The SAS key for the Blob Storage I was using for this project was missing DELETE and DELETE VERSION permissions, which is necessary if you want to include ERRORFILE and ERRORFILE_DATA_SOURCE in your BULK INSERT statement. As far as I am aware, this is not mentioned in Microsoft's documentation (and the error message doesn't hint at this being the issue either).

I simply created a new SAS key with ALL permissions, used that to create a new DATABASE SCOPED CREDNETIAL and EXTERNAL DATA SOURCE, and ran my code again and it worked.