How to import data from Azure Blob Storage to a local file with SSIS

Problem

I have a csv file in Azure Blob Storage and I want to import to my local SQL Server. Is that possible?  What tools do I need to import the data?

Solution

Yes, in this new tip we will use the SQL Server Integration Services (SSIS) task named Azure Blob Source, which is installed with the Feature Pack for SSIS to connect to Azure.

We are assuming that you already have an Azure Storage Account and a Blob container. If you do not, please refer to this tip (Automatically upload SQL Server Database Backups to Azure with Integration Services) and follow steps 1 through 6. In that tip, our Azure Storage Account was mssqltips and the Blob container is bc1.

Requirements

1. SQL Server 2016.
2. The SSDT installed.
3. An Azure Subscription.
4. Internet Access.
5. Microsoft Azure Storage Explorer installed (MASE).
6. Feature Pack in SSIS for Azure installed.
7. An Azure Storage Account a Blob Container created (see our tip Automatically upload SQL Server Database Backups to Azure with Integration Services).

Getting Started

1. Create a csv file name file.csv with the following content:

Name;Lastname;email
John ;Lennon;jlennon@hotmail.com
Ringo ;Starr;rstarr@hotmail.com
Paul;Maccartney;pmaccartney@hotmail.com
George;Harrison;gharrison@hotmail.com

2. Using MASE, upload the file.csv of step 1 to the Azure Blob container bc1 created in the earlier tip:

3. Using SSDT, create a SSIS project. Drag and drop a Data Flow Task to the design pane:

4. Double click Data Flow. Drag and drop Azure Blob Source. This is a new task included in the SSIS Feature Pack for Azure installed as part of the prerequisites. This task will be used to connect to the csv file in Azure:

5. Drag and drop a Flat File Destination and join this task with the Azure Blob Source:

6. Double click the Azure Blob Source and press New to create a new Azure storage connection:

7. To connect, you need a Storage account and an Account key:

8. In your browser, go to Azure Portal and then to All resources > mssqltips (or your Storage Account if you used another name) > Access Keys:

9. Copy the Storage account name used and the key1:

10. Paste the Storage account name and the key1 of the step 9 in the Azure Storage Connection manager in the Window of step 7 in SSIS and Test the connection:

11. The Blob container name is the name of a Blob Container created in Azure using MASE. In that container is where the file .csv is located. The Blob name is the name of the file. We will use “;” as the column delimiter because this csv file is separated by semicolons as you can see in step 1:

12. Click Columns to check if the columns were recognized:

13. Save the properties and go to Flat File Destination Task:

14. Press New to create a new connection:

15. Select Delimited Flat File Format:

16. Press Browse to specify the destination file name and path:

17. Specify the path and name of the file:

18. In this example, the destination file will be exported.txt:

19. Press Mappings to map source columns with destination columns:

20. By default, the destination columns will be the same, but you can change them here if you want:

21. Start the package to copy the Azure information from the csv file:

22. If everything is OK, a new file will be created in the path specified in step 18:

23. Open the txt file created:

Conclusion

In this tip, we copied the data from a csv file stored in an Azure Storage Account to a local txt file using SSIS. For this, an Azure storage account and a container were needed. We used the new feature pack for SSIS that includes new tasks for Azure.

Next Steps

For more information, refer to the following links:

• Azure Feature Pack for Integration Services (SSIS)
• Move Data to or from Azure Blob Storage using SSIS connectors
• Azure Blob Source
• Check out the following resources:
  • SQL Server Integration Services Tips
  • SQL Azure Tips
  • SQL Server Integration Services Tutorial