Can't create an external data source from Azure Blob Storage Container to Azure Sql Database Hyperscale

Question

I have uploaded a single file (BlockBlob) to a container in the Azure BlobStorage

This is the path: https://myStorageName.blob.core.windows.net/myContainerName/myFolder.Name/myFileName.json

I want to load this file into a table within Azure Sql Database

If I create a SAS to the file, things works perfectly. However I am failing to generate a single SAS that can access multiple files inside a container.

Here is the code that works:

CREATE DATABASE SCOPED CREDENTIAL TemporaryBlobSCredential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'sp=......................'

CREATE EXTERNAL DATA SOURCE TemporaryBlobDataSource
 WITH ( TYPE = BLOB_STORAGE,
       LOCATION = 'https://<myStorageName>.blob.core.windows.net/<myContainerName>',
       CREDENTIAL= TemporaryBlobSCredential);

create table <tableName>
(JsonData varchar(max))
 
BULK INSERT <tableName>
FROM '<myFolder.Name>/<myFileName>.json'
WITH (DATA_SOURCE = 'TemporaryBlobDataSource');

If I generate a SAS that has ALL the permissions (SECRET = '?sv=......') it won't work:

Should I use something different? Does it even works?

GregGalloway · Accepted Answer · 2022-02-21 19:17:28Z

2

This document says:

Warning

The SAS key value might begin with a '?' (question mark). When you use the SAS key, you must remove the leading '?'. Otherwise your efforts might be blocked.

I notice in your working code there is no ‘?’ but there is in your SAS token which doesn’t work. Can you double check this small difference isn’t the problem?

edited Feb 21, 2022 at 19:17

answered Aug 27, 2020 at 10:23

GregGalloway

11.6k3 gold badges21 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Himanshu Kumar Sinha · Accepted Answer · 2020-08-27 00:35:45Z

2

I just tried to run the code which you shared and it is very similar to what is pasted here . if you scroll up on the page , the "Applies to" does not call out synapse / SQL DW . Are you sure that you are using Synapse ? BULK INERT is not supported in SQL pool .

Anyways if you are using Synapse generally we can use the COPY INTO command but in your case we have JSON format which is not support . I think you can use Azure datafactory

answered Aug 27, 2020 at 0:35

Himanshu Kumar Sinha

1,8182 gold badges9 silver badges11 bronze badges

1 Comment

Flavio Pegas Over a year ago

You are right. Multitasking doesn't always work. I have just edited the question, but Greg were already right.

Collectives™ on Stack Overflow

Can't create an external data source from Azure Blob Storage Container to Azure Sql Database Hyperscale

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related