Does Microsoft support BULK INSERT command to load data from a csv file in an Azure Files location

Question

Does Microsoft support BULK INSERT command to load data from a csv file in an Azure Files location

Mo 0

I am wondering if using the BULK INSERT command in Azure SQL Database supports loading data from a csv file in an Azure Files folder? I know it supports Blob storage location but want to confirm if Azure Files configured as an external data source is supported or not?

MS Learn documentation doesn't explicitly state that Azure Files is unsupported, and also doesn't provide a syntax for BULK INSERT to use Azure files. Just can Microsoft confirm our understanding that BULK INSERT to use Azure files is unsupported.

0 comments

1 answer

Your answer

Answer 1

Hi @Mo

Thanks for your question.

You are correct. Azure SQL Database does not support using Azure Files directly as an external data source for BULK INSERT. Azure SQL Database supports bulk loading files from Azure Blob Storage, but it does not support reading files directly from Windows file paths or SMB shares.

Since Azure Files exposes file shares through SMB/NFS rather than the Blob Storage endpoint required by this bulk-loading mechanism, you cannot configure an Azure Files share as a BLOB_STORAGE external data source.

Supported alternative:

If your CSV files are currently stored in Azure Files, the recommended approach is to copy them into an Azure Blob Storage container and then use BULK INSERT.

For example, after configuring an external data source pointing to an accessible Blob container:

BULK INSERT dbo.YourTable
FROM 'data.csv'
WITH (
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FIRSTROW = 2
);

Here, MyAzureBlobStorage must be an existing external data source configured for the Blob Storage location, with the required permissions.

Alternatively, you can use an ETL process, such as Azure Data Factory, to transfer the CSV data from Azure Files into Azure SQL Database.

In summary, Direct BULK INSERT from Azure Files to Azure SQL Database isn't supported. Use Azure Blob Storage as the staging location, or an appropriate data integration service.

Hope this clarifies the supported behavior.

References:

Help make this community better for everyone: If this answer helped or resolved your issue, please accept it or upvote it. If not, share more details in a comment so we can continue the discussion and find the right solution. Thank you.