BULK INSERT into Managed Instance from a file in Azure Files?

Question

How can I use BULK INSERT to load files that are in Azure Files, from a Managed Instance? Everything I can find talks about Azure Blob storage, but I've got an old Perl script that we don't want to modify that grabs the file currently. Our options appear to be:

• save it somewhere locally and run azcopy every x minutes to upload the file to Blob Storage
• BULK INSERT via an Azure Files share.

However, while the pages specifically don't say you can't (unlike SQL Database, which can only use Blob Storage), there's nothing about how you set up the share so that the Azure Managed Instance has permissions to it.

Thanks in advance.

Answer 1

As per my Understanding you can use BULK INSERT to load files this from an Azure Files share into an Azure SQL Managed Instance. you can try this steps:

To create Azure Files share : Create a storage account under the data storage you will find the Azure Files share.

Create a virtual network and subnet that can access the storage account.

Create a Managed Instance using the same virtual network and subnet. and In the (SSMS) Connect to the Azure SQL Managed Instance instance Execute the SQL statement

In the Managed Instance, create a SQL Credential using a Storage Account Access Key to authenticate to the storage account. Create SQL Credential:

CREATE CREDENTIAL Credential02 WITH IDENTITY = 'newadlsforgen2', SECRET = 'xxxxxxxxxxxxxxx';

Provide the details for your storage account

Use the SQL Credential to create an external data source for the Azure Files share. Create external Data source: Use this MS DOC as reference for creating External data source.

CREATE EXTERNAL DATA SOURCE AzureFilesDataSource
WITH (
    TYPE = HADOOPEXTERNAL,
    LOCATION = 'wasbs://new02@newadlsforgen2.file.core.windows.net',
    CREDENTIAL = AzureFilesCredential
);

Query to BULK INSERT Please refer the MS DOC for the BULK INSERT

use BULK INSERT statement to load the data from the file into a table.

BULK INSERT Table02
FROM 'data.csv'
WITH (
    DATA_SOURCE = 'AzureFilesDataSource',
    FORMAT = 'CSV',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);