Azure Blob to Azure SQL tables creation

Question

I am trying to convert BLOB Files into SQL DB Tables in Azure using BULK INSERT.

Here is the reference from the Microsoft: https://azure.microsoft.com/en-us/updates/preview-loading-files-from-azure-blob-storage-into-sql-database/

My DATA in CSV looks like this 100,"37415B4EAF943043E1111111A05370E","ONT","000","S","ABCDEF","AB","001","000002","001","04","20110902","11111111","20110830152048.1837780","",""

My BLOB Container is in Public Access Level.

Step 1: Created Storage Credential. I had generated a shared Access key (SAS token).

CREATE DATABASE SCOPED CREDENTIAL Abablobv1BlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2017-07-29&ss=bfqt&srt=sco&sp=rwdlacup&se=2018-04-10T18:05:55Z&st=2018-04-09T10:05:55Z&sip=141.6.1.0-141.6.1.255&spr=https&sig=XIFs1TWafAakQT3Ig%3D';
GO

Step 2: Created EXTERNAL DATA SOURCE in reference to Storage Credential

CREATE EXTERNAL DATA SOURCE Abablobv1BlobStorage
WITH ( TYPE = BLOB_STORAGE, LOCATION = 'https://abcd.blob.core.windows.net/', CREDENTIAL = Abablobv1BlobStorageCredential );
GO

Step 3 BULK INSERT STATEMENT using the External Data Source and DB TABLE

BULK INSERT dbo.TWCS
FROM 'TWCSSampleData.csv'
WITH ( DATA_SOURCE = 'Abablobv1BlobStorage', FORMAT = 'CSV'); 
GO

I am facing this error:

Bad or inaccessible location specified in external data source "Abablobv1BlobStorage".

Does anyone have some idea about this?

I changed the Location of EXTERNAL DATA SOURCE to Location = abcd.blob.core.windows.net/invoapprover/SampleData.csv Now I get, Cannot bulk load because the file "SampleData.csv" could not be opened. Operating system error code 5(Access is denied.). For both statements using Bulk Insert or Open Row Set. I was not sure which access should be changed because the file is in Azure blob not on my machine, any ideas for this??

Alberto Morillo Alberto Morillo · Accepted Answer · 2018-04-09T12:39:05

Please try the following query

SELECT * FROM OPENROWSET(
   BULK  'TWCSSampleData.csv',
   DATA_SOURCE = 'Abablobv1BlobStorage',
   SINGLE_CLOB) AS DataFile;

Make sure the file is not located inside a container on the BLOB storage. In that case you need to specify the container in the Location parameter of the External Data Source. If you have a container named "files" then the location should be like 'https://abcd.blob.core.windows.net/files'.

More examples of bulk import here.

Azure Blob to Azure SQL tables creation

1 Answers