Azure SQL Database - How to get list of files in Storage account from T-SQL

Question

Hi
I created a set of SQL stored procedure to load all CSV in a folder into a table
Unfortunately I found out the customer has no permanent PC to install SQL Express (all portables)
So I decided to use an Azure Sql Database.
So far I was able to import a CSV using BULK INSERT and from EXTERNAL DATA SOURCE

My problem is that I dont know in advance the names of the csv file the user will put into the Azure Storage
I need a way to list all files so I can iterate each file with a cursor and BULK INSERT THEM

I search an was not able to find a way to list files in a Storage from T-SQL (please do not propose SSIS or Azure Studio)
My solution must be 100% Stored Procedure

The only thing left to have my solution working is to get the list of files in the Storage.

I can issue command SELECT * FROM sys.external_data_sources and it shows my folder
I need the same to show the actual files in this folder/data_source

Thank you

Answer 1

Hey @m trudel ,
Based on my knowledge it is not possible directly from TSQL and that too in Azure SQL database.
In Azure synapse and SQL 2016 and above wherein we have polybase, we can map the external table to folder and the select query can give the union of all data of all files (but not the file list).

Hence we have metadata activity in Azure data factory to list the files .

Answer 2

Hi @m trudel , welcome to Microsoft Q&A forum.

I think there is no direct way to get the list azure storage files using stored procedure. However this can be achieved in 2 steps process as a workaround.

1) Loop through the files using PowerShell or any other language through Azure Function or Azure Automation and load the file names in some transaction table. Every time this script runs, it will truncate the transaction table and inserts the name of files. Below is the some example to iterate through files.

PowerShell script to iterate all containers and blobs in a storage account

How to Get All the Blobs from an Azure Storage Account using PowerShell

2) In stored procedure, we can loop through this transaction table and perform the required task as needed.

Not the cleanest way but it could serve the purpose. Other way as mentioned by @Nandan Hegde is to use the ADF.

Please let us know if you need any further details on the same and we will be delighted to get into more details.