Azure Blob Cannot Bulk Load

4
votes
CREATE PROCEDURE LoadData
AS
BEGIN

DELETE FROM [dbo].[File];

BULK INSERT [dbo].[File]
FROM 'File.csv'
WITH ( 
    DATA_SOURCE = 'AzureBlob',
    FORMAT      = 'CSV',
    FIRSTROW    = 2
);

END
---------------------
CREATE EXTERNAL DATA SOURCE AzureBlob
WITH ( 
    TYPE       = BLOB_STORAGE,
    LOCATION   = 'https://marczakiocsvstorage.blob.core.windows.net/input',
CREDENTIAL = BlobCredential
);
-----------------------------
CREATE DATABASE SCOPED CREDENTIAL BlobCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=SAS_TOKEN_HERE';

Following this guide (https://marczak.io/posts/azure-loading-csv-to-sql/), I am attempting to load data from an Azure Blob into an Azure SQL table.

After creating the external data source and running the stored procedure I am getting the following error:

"Cannot bulk load because the file "File.csv" could not be opened. Operating system error code 5(Access is denied.)."

I made sure to double check my SAS Token and exclude the question mark when creating the credential. Also double checked the Contrainer URL. All seems okay. What could I be missing here preventing the blob from being read?

2
sqlazureazure-sql-databaseazure-storageazure-storage-blobs
Could you try update your location as / at last like https://marczakiocsvstorage.blob.core.windows.net/input/ – Jayendran
What was you solution? For me it can run once and after multiple re-queries of the SCOPED CREDENTIAL BlobCredentials it will run... – Simon GIS
Have you found the fix ? I have this same issue. I have to drop and recreate the External Data Source to make it work. – Pரதீப்

2 Answers

3
votes

Please make the "input" container exist inside the BLOB account.

Please verify the Shared Access Signature start and expiry date and time, verify its time zone and verify "Allowed IP addresses" is blank.

enter image description here

Try using OPENROWSET instead.

SELECT * INTO TempFile FROM OPENROWSET( BULK 'input/File.csv', DATA_SOURCE = 'AzureBlob', SINGLE_CLOB)
0
votes

It appears that when you are using SQL Authentication and Azure SQL is not allow to access the bulk load blob storage.

If you Azure Blob storage account is not public, you need to generate a shared access signatures(SAS) key for the account by using the Azure portal. Put the SAS key in CREDENTIAL, and create an EXTERNAL DATA SOURCE with CREDENTIAL, as shown in the following example:

CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2015-12-11&ss=b&srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z&spr=https&sig=copyFromAzurePortal';

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://myazureblobstorage.blob.core.windows.net',
       CREDENTIAL= MyAzureBlobStorageCredential);

For more details, you could refer to this article.