Load data from Azure Blob storage into Azure SQL | New Data Exposed episode

Copper Contributor

Apr 28, 2021

Hello Anna,

Thanks for this amazing post.

I have followed your vedio from youtube and microsoft documentation, while i try to do perform similar task i am blocked with error during Bulk insert step.

Below are my steps performed and error, please help.

----

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test_123';

---

CREATE DATABASE SCOPED CREDENTIAL MyCredentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=r&st=2021-04-28T05:36:45Z&se=2021-04-30T13:36:45Z&spr=https&sv=2020-02-10&sr=b&sig=NWmp5KNs6u6BBop8TbTD1IdeoVrsj5xhSvT0NRIxumg%3D'

---

CREATE EXTERNAL DATA SOURCE dataset
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://test.blob.core.windows.net/data/Data1/',
-- CREDENTIAL= MyCredentials
);

---

As table is already created by name Test with schema, i am doing bulk insert.

BULK INSERT Test
FROM '/data/Data1/test.csv'
WITH ( DATA_SOURCE = 'dataset',
FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
FIRSTROW=2,
ROWTERMINATOR = '0x0a',
TABLOCK);

ERROR:

Msg 4861, Level 16, State 1, Line 36
Cannot bulk load because the file "/data/Data1/test.csv" could not be opened. Operating system error code 5(Access is denied.).

Please advise how to overcome this error?

Thanks in advance. 🙂

Blog Post

Load data from Azure Blob storage into Azure SQL | New Data Exposed episode