Introduction

Loading smalls set of data into SQL Server has been always easy on Windows, where we have the traditional command-line based utility called BCP (Bulk Copy Program). If you are more a GUI person, you always have your old good buddy, SSMS (SQL Server Management Studio), which has a rich set of options to load flat files into databases and tables.

This is nice, but what about SQL Server on Linux? Microsoft has been doing a great job creating new cross platform tools for Linux. Azure Data Studio was released in September 2018. This tool is the released version of what was previously known as SQL Operations Studio.

Using SQL Server import extension for Azure Data Studio

Azure Data Studio has multiple extensions available already, and SQL Server Import is one of them. As the name suggests, this extension was made to help data professionals import flat files into SQL Server. According the documentation this extension supports CSV, TXT and JSON files.

This extension sounds like a nice solution to perform quick data imports from non-Windows environments, like Linux or macOS. Without further ado, let's put this extension to test.

Installation

Installation is pretty straight forward. Identifying the extension in the extension marketplace and then click on the Install green button.

This extension does require Azure Data Studio to be reloaded, but the time it takes doing this operation is minimal. The following output it is just an example of what you could expect to see in the messages tab after completing the extension installation:

  Downloading https://sqlopsextensions.blob.core.windows.net/extensions/import/osx.tar.gz
  (34534 KB)....................Done!
  Installing Flat File Import Service service to /Users/MyUser/.azuredatastudio/extensions/microsoft.import-0.2.0/flatfileimportservice/OSX/0.0.2
  Installed

Operation

For demonstration purposes, I have created a new database, called DataLoadTest. This database is hosted on a SQL Server 2019 instance running on a Linux environment (Docker container) because the main goal of this test is to use this extension in a cross platform scenario. In this specific case, I will connect to the database using Azure Data Sutudio from macOS to the instance running in Ubuntu. The flat file is a CSV file that contains sample Sales information and contains more than 50,000 records.

The wizard is called by right clicking over the target database from the instance explorer. The Import option has to be selected from the submenu that appears. Then, the wizard will ask for the input file path and other details, such as server name and database name. Once the file is loaded, the wizard will suggest a table name based on the name of the file. Unfortunately there is no option to use an existing table, so any data loaded will have to use a different table name.

After doing a quick check of the data at the Preview Data window, the wizard walks you through the classic modify columns window, which is always nice to have in case you want to make some changes like adjusting the data types or column names in the target table. In my case I didn't change anything rather than the table name, I called it SalesRecords.

Finally, a summary window with all the details about the data load is displayed. This contains all the relevant information about this data operation, such as server name, target database name, target table name, source file, etc. After waiting a couple of minutes, the extension had successfully loaded 50,000 records in my DataLoadTest database. The performance was not too bad for a small Linux container with just two CPU cores and one GB of RAM.

Conclusion

I found a little problem this extension. In the case of failure, it does not provide details of the issue; no error message or log location is provided, which makes completely sense as this extension was released just a few weeks ago and is still in preview phase. That give us the opportunity to make suggestions, requests to the team behind the development of this tool whom, by the way, are doing a wonderful job providing us these new tools.

Anyway, I went the extra mile and found the root cause of my failed experience. My failure was caused by the wrong data (invalid characters) from the flat files rather than a bug with this extension.

With that said, this tool does a pretty good job doing exactly what data professionals was used to do using SSMS. Microsoft provide us once again an easy way to perform a data load process with minimal user input, even running from cross platform environments.