Import Azure Blob files into SQL Server (CSV/JSON/XML Driver)

Introduction

There might be a case when you have many CSV, JSON or XML files in Azure Blob and you want them to be imported straight into a SQL Server table. Here come ZappySys ODBC PowerPack and ZappySys Data Gateway (part of ODBC PowerPack) which will enable you to accomplish that. ZappySys ODBC PowerPack includes powerful Azure Blob CSV, Azure Blob JSON and Azure Blob XML drivers that let you connect to an Azure Storage Explorer and read the contents from the files in the Container. Bringing in ZappySys Data Gateway allows doing that right from a SQL Server. Let’s begin and see how to Import Azure Blob files into SQL Server (CSV/JSON/XML Driver).

These drivers are related to this article:

Azure Blob Driver (for CSV Files)

Azure Blob Driver (for JSON Files)

Azure Blob Driver (for XML Files)

Prerequisites

1. Firstly, Download and install Microsoft Azure Storage Emulator and Microsoft Azure Storage Explorer.
2. SQL Server instance installed (can be a SQL Server Express LocalDB instance).
3. SQL Server Management Studio (SSMS) installed.
4. ZappySys ODBC PowerPack installed (must be installed on a Windows machine; can be a different machine than SQL Server is installed on).

NOTE: If you want to use Live account (Azure Storage) then you can skip Step #3

Getting started

In order to start, we will show several examples. ZappySys includes Data Gateway that will help you in reading data of Azure Blob files. Here we are showing you is, how to Import Azure Blob files into SQL Server (CSV/JSON/XML Driver).

You can connect to Azure Storage Service from SSIS, you will need Storage Account Name and Access Key. Ask your SysAdmin or responsible person to provide that information to you. Click here to read more about how to get your Storage Account Name and Access Key. Here are sample Credentials.

If you don’t have Azure Storage account then you can try offline mode on your local machine. You can just download Azure Storage Emulator and start testing.

Setup Azure Storage Explorer

Setup Azure Storage client tools we will start from a simple example of how to load Invoice Data from a CSV that is located in Azure Blob, then we will load JSON contacts data from them and finally, we will proceed by loading contacts from many XML files those are compressed in ZIP.

1. Once you have downloaded and installed storage emulator You can launch Microsoft Azure Storage Emulator from its Physical location or from the desktop or start menu shortcut.

   

   Azure Storage Emulator Physical Location

2. If you can see the below-attached Command Prompt screen after Emulator started. Then you can proceed to start Microsoft Azure Storage Explorer as the Azure Storage Emulator is started successfully.

   

   Command Prompt Screen after Microsoft Azure Storage Emulator Started

3. Now, you have to download and install Microsoft Azure Storage Explorer and then you can launch Microsoft Azure Storage Explorer from its Physical location or from the desktop or start menu shortcut.

   

   Microsoft Azure Storage Explorer Location.

4. For Creating a Blob Container, First of all, you need to go to Microsoft Storage Explorer Window. Then you can go through like this way (Storage Accounts –> (Development) –> Blob Containers).
   

   Microsoft Azure Storage Explorer: Create a Blob Container

Open and Configure ZappySys Data Gateway

Import CSV file located at Azure Blob into SQL Server

Overview

Let’s say you have CSV file that is located in Azure Blob Container, and you want them all data into your SQL Server. We will follow the same steps for that we will do for import JSON and XML file into SQL Server.

Azure Blob Container – CSV File Located

Create an Azure Blob CSV data source in ZappySys Data Gateway

1. The first thing you will have to do is to create a data source in ZappySys Data Gateway. In the Data Source tab, Just click on Add button, give the data source a name, e.g. “MyInvoiceCSV“, and then select Native – ZappySys Azure Blob CSV Driver.
   

   Create Data Source – Azure Blob CSV Driver

2. Then click on Edit and add the Data Gateway user you created in the Users tab. We will use this user later when adding a Linked Server to the Data Gateway to authenticate:
3. Now, click on the Edit button to configure “MyInvoiceCSV” Azure Blob CSV Data Source.
   

   Configure Data Gateway

4. When a window open, click on Click here to Configure the Connection and select Use the Microsoft Azure Storage Emulator. If you have Online Storage Account and Account Key then Select Enter Storage Account Credentials and enter it.
   
   

   Create Azure Storage Connection

5. Now, Select Azure Blob Container and file from it and In the Data Format / Compression (Zip / GZip) tab set suitable file Compression Format (Zip or GZip).
   

   ZappySys Azure Blob CSV – Configure Driver

6. Go to the Preview tab and click on the Preview Data button to make sure everything is configured correctly and preview the results.
   

   ZappySys Azure Blob CSV – Preview Data

7. Now, Click on OK button also Save and Restart Service.
   

   ZappySys Data Gateway Service – Save and Restart

8. That’s all, now we are ready to proceed and add a Linked Server to this data source.

Set up a SQL Server Linked Server

Execute the SQL query

Once again, if you created the Linked Server with name “GATEWAY”, execute this SQL query in SSMS:

SELECT * INTO MyInvoices FROM OPENQUERY([GATEWAY], 'SELECT * FROM $')
SELECT * FROM MyInvoices

The results

You should see a similar view, once you execute the query:

The results of loading many CSVs from Azure Blob into SQL Server

Import JSON file located at Azure Blob into SQL Server

Overview

Let’s say you have JSON file that is located in Azure Blob Container, and you want them all data into your SQL Server. We will follow the same steps of the import CSV file into SQL Server and we will do for XML file into SQL Server.

Create an Azure Blob JSON data source in ZappySys Data Gateway

Follow the steps as in Create an Azure Blob CSV data source in ZappySys Data Gateway section when adding a new data source, except for these two steps:

1. The first thing you will have to do is to create a data source in ZappySys Data Gateway. Just click Add button, give the data source a name, e.g. “MyContactsJSON“, and then select Native – ZappySys Azure Blob JSON Driver.
   

   ZappySys Data Gateway – Create New Data Source
2. After that, select a JSON file from container you want to load, and then click Select Filter button to choose data you want to be displayed in SQL Server.
   

   Configuring the JSON data source to load a JSON from Azure Blob container into SQL Server

Now you are ready to create a Linked Server and connect to the data source you just created. If you used “MyContactsJSON” as the data source name, make sure to use the same name when creating a Linked Server.

Set up a SQL Server Linked Server

Again, follow the very same steps from the section above where we added the Linked Server to a CSV data source, except that change Catalog property of the Linked Server to match the name of the Azure Blob JSON data source we created. Once you do that, you are ready to load JSON into your SQL Server.

Execute the SQL query

Once you created the Linked Server to ZappySys Data Gateway, you are ready to execute the SQL query and load data into SQL Server. Supposedly, you created the Linked Server with the name “GATEWAY”, then open SSMS and execute the following query:

SELECT * INTO MyContacts FROM openquery([GATEWAY], 'SELECT * FROM $')
SELECT * FROM MyContacts

The results

You should see similar results after you execute the query:

The results of getting JSON data straight from Azure Blob JSON

Now we are ready to proceed to the next section and import many XML files at once.

Import many XML files located at Azure Blob into SQL Server

Overview

Supposedly, you have many XMLs where each one is zipped and you want to load them all into a SQL Server table.

Zipped XML files located in Azure Blob container to be loaded into SQL Server

The first thing you will have to do is to create a data source, based on ZappySys Azure Blob XML Driver. Let’s proceed and just do that.

Create an Azure Blob XML data source in ZappySys Data Gateway

Follow the same steps as in Create an Azure Blob JSON data source in ZappySys Data Gateway section when adding a new data source, except for these two steps:

1. When adding a data source select Native – ZappySys Azure Blob XML Driver as Connector Type.
   

   Choosing “ZappySys Azure Blob XML Driver” to load XMLs to SQL Server
2. Then click on Edit and select Azure Blob Container and file from it and In the Data Format / Compression (Zip / GZip) tab set suitable file Compression Format (Zip or GZip).
   

   Configuring data source based on ZappySys Azure Blob XML Driver to load XMLs into SQL Server

3. Now we are ready to set up a Linked Server to this newly created data source.

Set up a SQL Server Linked Server

Follow the very same steps from the section above where we added the Linked Server to a CSV data source, except that change Catalog property of the Linked Server to match the name of the Azure Blob XML data source you created. Once you do that, you are ready to load XMLs into your SQL Server.

Execute the SQL query

Again, if you created the Linked Server with name “GATEWAY”, execute the following SQL query in SSMS:

SELECT * INTO MyContacts FROM openquery([GATEWAY], 'SELECT * FROM $')
SELECT * FROM MyContacts

The results

You should see a similar view once you execute the query:

The data of many XMLs loaded from Azure Blob Container into SQL Server

Now we are ready to move to the next section and recursively scan CSVs and load them into SQL Server.

Conclusion

We had a goal to import Azure Blob files into SQL Server from SQL Server itself. We learned how to Import Azure Blob files into SQL Server (CSV/JSON/XML Driver), which some of them were zipped and were located at different folders and subfolders. It was possible to accomplish that using ODBC PowerPack, ZappySys Data Gateway, and ODBC PowerPack drivers – Azure Blob JSON Driver, Azure Blob XML Driver, and Azure Blob CSV Driver. At first, we created data sources in the Data Gateway, then we created Linked Servers in SQL Server and connected to ZappySys Data Gateway, which finally made it possible to load data from Azure Blob straight from SQL queries in SSMS.

References

Finally, you can use the following links for more information:

• Azure Blob CSV Driver
• Azure Blob JSON Driver
• Azure Blob XML Driver