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

Question

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?

Jakub · Accepted Answer · 2017-06-06 15:15:36Z

7

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.

answered Jun 6, 2017 at 15:15

Jakub

1265 bronze badges

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

1 Comment

Vladislav Over a year ago

Currently: Temp tables are actually supported on Azure SQL, so weird they are not supported on latest SQL 2017 (14.0.3008.27)

David Browne - Microsoft · Accepted Answer · 2017-05-08 20:10:27Z

4

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

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

answered May 8, 2017 at 20:10

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

6 Comments

Seafish Over a year ago

Somebody edited my post and took out my (sanitized) secret, but I am using the SAS without the ?. So the OPENROWSET query works, but I still get the same error if I then try to execute a BULK INSERT into a temp table. Could you see if executing a BULK INSERT into a temp table works on your end?

David Browne - Microsoft Over a year ago

I repro'd, and BULK INSERT currently doesn't work against temp tables. Create a permanent table and try. And if you get this error: "Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)"" try specifying ROWTERMINATOR = '0x0a',

Seafish Over a year ago

I'm trying against a permanent table and sure enough I'm getting the Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)" error. I tried the row terminator option but it doesn't help. How did you debug that error to know what option to try?

David Browne - Microsoft Over a year ago

Apparently that error can mean any sort of mismatch between your file and the expected format. I ran through the sample here:github.com/Microsoft/sql-server-samples/tree/master/samples/… You should try with those files to isolate the difference.

Michael Fender Over a year ago

@DavidBrowne-Microsoft Do you happen to know why BULK INSERT does not work against temp tables?

|

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related