- 1 Introduction
- 2 Prerequisite
- 3 What is Azure Blob Storage
- 4 What is Azure Queue Storage?
- 5 Getting Started
- 6 Setup Azure Storage client tools
- 7 Create an Azure Blob Storage Queue
- 8 Video Tutorial – SSIS Azure Queue Storage – Bulk Read, Write, Update, Delete Queue Messages
- 9 Read Azure Queue Messages in SSIS
- 10 Write Azure Queue Messages in SSIS
- 11 Performance Tips
- 12 Conclusion
- 13 References
In our previous blog we saw How to load data into Azure Blob Storage. Now in this blog, we will How to Read and Write in Azure Queue Storage in SSIS. To illustrate, we will use ZappySys SSIS PowerPack, Which includes several tasks to import/export data from multiples sources to multiple destinations like flat files, Azure, AWS, databases, Office files and more. They are Coding free, drag and drop high-performance suite of Custom SSIS Components and SSIS Tasks. If you like perform other operations on Azure Blob Storage Files (e.g. Download, Upload, Create, Delete) then check these articles.
In a nutshell, In this post will focus on how to Read and Write in Azure Storage Queue in SSIS using following tasks/sources.
Components Mentioned in this article
- First, you will need to have SSIS installed
- Secondly, make sure to have SSDT
- Download and install Microsoft Azure Storage Emulator
- Download and install Microsoft Azure Storage Explorer
- Finally, do not forget to install ZappySys SSIS PowerPack
NOTE: If you want to use Live account (Azure Blob Storage) then you can skip Step #3
What is Azure Blob Storage
Azure Blob storage is Microsoft’s object storage solution for the cloud. you can store large amounts of unstructured data, such as text or binary data. Blob storage discloses three resources:
- Storage account (You can access data objects in Azure Storage through a storage account. For more information, click here.)
- The containers in the account(constructs a set of blobs, it is similar to a folder in a file system.
All blobs lie within a container, Note: Container name must be lowercase)
- The blobs in a container. (Azure Storage offers three types of blobs: block blobs, append blobs, and page blobs)
See the below-attached diagram. It shows the relationship between these resources.
You can also use Azure Storage Explorer on your Local machine. Azure Storage Explorer is a standalone app that enables you to easily work with Azure Storage data on Windows, macOS, and Linux. You can use Blob storage to expose data publicly to the world or to store application data privately. Connect to an Azure storage account or service
What is Azure Queue Storage?
Azure Queue storage is a service for storing large numbers of messages. You access messages from anywhere in the world via authenticated calls using HTTP or HTTPS. A queue message can be up to 64 KB in size. A queue may contain millions of messages, up to the total capacity limit of a storage account.
Common uses of Queue storage include:
- Creating a backlog of work to process asynchronously
- Passing messages from an Azure web role to an Azure worker role
In order to start, we will show several examples. ZappySys includes an SSIS Azure Queue Storage Source that will help you in reading messages from Azure Queue Storage. And SSIS Azure Queue Storage Destination that will help you in insert and delete messages from Azure Queue Storage. Here we are showing you is, How to Read and Write in Azure Storage Queue.
You can connect to your Azure Storage Account by entering your storage account credentials. Here I am showing an example of the use of local Azure Storage Emulator.
Setup Azure Storage client tools
- 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.
- 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.
- 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.
Create an Azure Blob Storage Queue
For Creating a Blob Queue, First of all, you need to go to Microsoft Storage Explorer Window. Then you can go through like this way (Storage Accounts –> (Development) –> Queues).
And enter the appropriate name to that queue as Queue names can contain only lowercase letters, numbers, and hyphens, and must begin and end with a letter or a number. The name can’t contain two consecutive hyphens..
Video Tutorial – SSIS Azure Queue Storage – Bulk Read, Write, Update, Delete Queue Messages
Read Azure Queue Messages in SSIS
Let’s start with an example. We use SSIS Azure Queue Storage Source to read Azure Queue Messages.
- First of all, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
- Furthermore, Drag and Drop ZS Azure Queue Source from the SSIS Toolbox.
- Now, double click on that Component to Edit the Configurations. Create New Connection and test it.
- Now select the desire Queue from where you wants to read the messages and click on preview.
- That’s it now we are ready to load this queue messages to another Azure Storage Queue
Write Azure Queue Messages in SSIS
Let’s start with an example. We use SSIS Azure Queue Storage Destination to insert Azure Queue Messages.
- First, Drag and Drop ZS Azure Queue Destination from the SSIS Toolbox.
- Double click on it to Edit the Configurations. And select the desire Azure Storage Connection.
- Go to Component Properties and select the operation as insert and desire queue from the drop down where you wants to insert messages.
- Now on Column Mapping tab map the desire columns and click on OK button.
- That’s it now execute the package and it will read the Azure Queue messages and insert the messages to that selected queue.
- Furthermore, go to MS Azure Storage Exporter and open/refresh that queue and you will find that 15 messages there.
Disable Nagle in SSIS
Nagle’s algorithm is widely implemented across TCP/IP networks as a means to improve network performance. However, it is not optimal in all circumstances (such as highly interactive environments). For Azure Storage, Nagle’s algorithm has a negative impact on the performance of requests to the table and queue services, and you should disable it if possible.
Please refer to this link for more information: Click Here
To disable Nagle algorithm in SSIS we need to use Script Task at very First step of the Package.
- In the Package File at the very first step add the Scrip Task and click on Edit Script.
- So that it opens ScriptMain.cs Script class in another visual studio instance. Now add below Line in the Main() function and save it.
1System.Net.ServicePointManager.UseNagleAlgorithm = false;
- Now go to Script Task and click on OK , so that it’s save the script and close the class window.
- That’s it we have disabled the Nagle Algorithm now you can go to next steps. In upper case we need to add data flow task and follow the steps from the 2# step.
Increase Max Parallel Threads size
We can increase the thread size to improve performance further more, in the azure connection manager we can increase Max Parallel Threads size like 20 or more.
Above all, in this blog, we learned how to Read and Write in Azure Storage Queues. We used Azure Queue Storage to read messages from Azure Queue Storage. And Azure Queue Storage Destination to insert messages from Azure Queue Storage. You can download SSIS PowerPack here to try many other scenarios not discussed in this blog along with 70+ other components.
Finally, you can use the following links for more information: