I have an Azure Data Lake Storage (Gen 2) account with several containers. I would like to import the salesorderdetail.csv
file from the Sales container into an Azure SQL database.
I've successfully built the same process using Azure Data Factory, but I now want to try and get this working via standard T-SQL statements only.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XxxxxxXX#'
CREATE DATABASE SCOPED CREDENTIAL MK_Cred_Data_Load
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX%3D';
CREATE EXTERNAL DATA SOURCE MK_ADLS_Sales
WITH (TYPE = BLOB_STORAGE,
LOCATION = 'https://mkpracticestorageaccount.blob.core.windows.net/sales',
CREDENTIAL = MK_Cred_Data_Load);
The above code appears to work correctly as I can see the External Data Source created in the Object Explorer window on SSMS:
The following code should insert the data from the salesorderdetail.csv file into the [lnd].salesorderdetail
table:
TRUNCATE TABLE [lnd].[SalesOrderDetail]
BULK INSERT [lnd].[SalesOrderDetail]
FROM 'salesorderdetail.csv'
WITH (DATA_SOURCE = 'MK_ADLS_Sales', FORMAT = 'CSV', FIRSTROW=2,
FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
Instead, it outputs the following error message:
Referenced external data source "MK_ADLS_Sales" not found.
I have also tried the following but the same error message appears as above.
SELECT *
FROM OPENROWSET(BULK 'salesorderdetail.csv',
DATA_SOURCE = 'MK_ADLS_Sales',
SINGLE_CLOB) AS DataFile;
Can someone please kindly assist? Thanks!
(Using SSMS v17.9, Azure SQL database standard edition, Windows 10)