Load CSV from Blob to Azure SQL Server - Referenced external data source not found

2
votes

I'm trying to bulk insert a CSV hosted in blob storage into Azure SQL Server, as is described in this MSDN post.

My code is taken almost entirely from this Microsoft Github sample.

When running it I receive the following error:

Referenced external data source "MyAzureBlobStorage" not found.

-- Create Database Master Key
IF (select Count(*) from sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MYPASSWORD'; 
END

-- Create Storage Credential
IF (select Count(*) from sys.database_credentials where name = 'MyAzureBlobStorageCredential') = 0
BEGIN
print 'Creating credential'
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<secret>';
END

-- Create External Data Source
IF (select Count(*) from sys.external_data_sources where name = 'MyAzureBlobStorage') = 0
BEGIN
print 'creating external data source'
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
    LOCATION = 'https://myaccount.blob.core.windows.net/upload',
    CREDENTIAL= MyAzureBlobStorageCredential);
END

-- Create temp table to hold data
IF OBJECT_ID('tempdb..#mytemptable') IS NOT NULL DROP TABLE #mytemptable
CREATE TABLE #mytemptable(
    [Id] [uniqueidentifier] NOT NULL
    -- etc
)

-- Bulk insert into temp table
BULK INSERT #mytemptable
FROM 'mycsv.csv'
WITH ( DATA_SOURCE = 'MyAzureBlobStorage',
        FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
        FIRSTROW=2,
        TABLOCK);

Any ideas?

2
sql-servercsvazuresqlbulkcopyazure-sql-database

2 Answers

4
votes

I had the same issue. I found the answer in the comments by @DavidBrowne-Microsoft, pasting it here to make it more visible:

I repro'd, and BULK INSERT currently doesn't work against temp tables. Create a permanent table and try.

4
votes

Here's another sample. Note that the secret is the "SAS token" copied from the azure portal without the leading '?'

enter image description here

drop DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
drop  EXTERNAL DATA SOURCE MyAzureBlobStorage

go
-- Create Storage Credential
print 'Creating credential'
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2016-05-31&ss=bfqt&srt=sco&sp=rwdlacup&se=2017-05-08T23:03:46Z&st=2017-05-08T15:03:46Z&spr=https&sig=nxxxUJXfp%2BL23%2FULs2wY3%2BYAdFewzNsqp73rcsSoge4%3D';

-- Create External Data Source
--full url https://myaccount.blob.core.windows.net/files/tran.csv
print 'creating external data source'
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
    LOCATION = 'https://myaccount.blob.core.windows.net/files',
    CREDENTIAL= MyAzureBlobStorageCredential);

--test
SELECT * FROM OPENROWSET(
   BULK  'tran.csv',
   DATA_SOURCE = 'MyAzureBlobStorage',
   SINGLE_CLOB) AS DataFile;

David