CODEWITHSUNDEEP
t-sqlazure-sql-databasebulkinsert
Revils
Revils

Reputation: 1508

Bulk import into Azure

For a Bulk Insert, I have got a data file and a format file (xml);

This is working OnPremises with a Bulk Insert statement, however in Azure it seems to have a problem with the format file. Below are the steps I have taken

Set Storage Access

Create an Database Scoped Credential to the Storage

CREATE DATABASE SCOPED CREDENTIAL StorageCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'This is my secret' (Shared Access Signature Key)

Create an external Data Source

CREATE EXTERNAL DATA SOURCE Storage
WITH  (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<storagename>.blob.core.windows.net/<containername>', 
    CREDENTIAL = StorageCredential
);

File Query (Bulk insert or Openrowset)

BULK INSERT <Schema>.<Table>
FROM 'File.dat'
WITH (
    DATA_SOURCE = 'Storage',
    FORMATFILE = 'File.xml'
)

or

SELECT * FROM OPENROWSET(
    BULK 'File.dat',
    DATA_SOURCE = 'Storage',
    FORMATFILE =  'File.xml'
) AS DataFile;

They are both not working with the error;

'Cannot bulk load because the file is inclomplete or could not be read'

However if I can succesfully run the following query;

SELECT * FROM OPENROWSET(
    BULK 'File.xml', 
    DATA_SOURCE = 'Storage',
    SINGLE_NClob) AS DataFile

Upvotes: 1

Views: 951

Answers (1)

Revils
Revils

Reputation: 1508

I have found the answer and I will post it myself (In case other people also run into this problem).

The datasource of the format file should be specified individually. I tried the way specified in the documentation of Microsoft; Bulk Insert

However there is an error in the parameter name. It states that the correct parameter is 'FORMATFILE_DATASOURCE', however it should be 'FORMATFILE_DATA_SOURCE'. (This is commented at the bottom)

BULK INSERT <Schema>.<Table>
FROM 'File.dat'
WITH (
    DATA_SOURCE = 'Storage',
    FORMATFILE = 'File.xml',
    FORMATFILE_DATA_SOURCE = 'Storage'
)

Upvotes: 5

Related Questions