Automatically load on-prem CSV data into Azure SQL with Azure Functions

Lately .csv (or related format, like .tsv) became very popular again, and so it’s quite common to be asked to import data contained in one or more .csv file into the database you application is using, so that data contained therein could be used by the application itself.

In the era of the Cloud, what can we do to simplify such popular requirement so that, for example, the user can just drop a file into a folder, and have it automagically imported into Azure SQL? (With small changes the same principle could be used for any database)

Azure Functions (that I love more and more every day) are the answer. Azure functions support the concept of trigger, which is an event that (as the name implies) when happens, will have an Azure Function executed, passing to that function information about the event that triggered it.

One of the available trigger is the Blob Trigger. So 40% of the work that is needed to implement what the title of this article says is already done.

The other 40% is provided by a (quite) new feature that has been added to the BULK INSERT command in Azure SQL: that ability to use a Blob Storage as a source.

The remaining 20% is the code that needs to be written to “glue” these two functionalities together. And this is the beauty of Azure Functions! Out of 100% of the code you would have had to write, you actually have to care only for 20% of it!

The full solution, with detailed instruction on how to set it up using, when possible, the new Azure CLI 2.0, is here:

https://github.com/yorek/AzureFunctionUploadToSQL

Each time a file will be saved into the Azure Blob Store's “csv” folder, within a couple of seconds, if the format is the expected one, data will be available in Azure SQL for you to be used as you wish. Yeah, is that simple and easy, really!

Enjoy!