Bulk import into Azure

Question

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

• File.dat
• 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

• Created a Shared Access Signature
• Set the container Access Policy to 'Blob (anonymous read access for blobs only)

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

Answer 1

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'
)

Answer 2

Use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option always requires the -f option, and to create an XML format file, you must also specify the -x option. In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

Reference Document: Use a Format File to Bulk Import Data (SQL Server)