How to read messages from a azure service bus queue using data factory

Question

Hi, I am new to Azure data factory and i am trying to understand is there a way in azure data factory by which i can read messages off a service bus queue. What i am trying to achieve is, there are messages coming into queue from a third party which have messages in different JSON formats. I want to process those messages transform them and save them into different sql server database tables. How can i consume those messages using ADF. Just for FYI volume of incoming messages in queue is very high.

Answer 1

Hello @rohit singh

Azure Data Factory (ADF) does not have a native connector for Service Bus. However, there are several options available to you:

1. Create a Consumer for ADF to Call Upon: This could be a Function App which batch-reads the messages and returns them, utilizing ADF Azure Function, or some code to do the same with the ADF Batch Service Custom Activity.
2. Use ADF Web Activity to Retrieve a Message: ADF Web Activity can be used to retrieve a message.
3. Re-route Your Messages to Blob Storage: You could re-route your messages to be written to blob storage, and then leverage the Blob Event Trigger.

For processing and transforming JSON messages, you can use the JSON format in ADF. You can create a JSON dataset and connect it to the source. Then, connect the source output to a flatten transformation and select the array you want to process.

To save the transformed data into SQL Server database tables, you can use the Copy Activity in ADF5.

This allows you to copy data from and to SQL Server database and use Data Flow to transform data in SQL Server database.

Handling a high volume of messages can be challenging. One approach is to check the incoming file size, and based upon that, create a pool of worker nodes. Give each of the worker nodes a chunk of records. The workers go at their own speed, processing the rows to API. When a worker finishes a chunk, give it another chunk of records. Continue until there are no more chunks to give.

Please note that these are general guidelines and the exact implementation may vary based on your specific use case and the structure of your data. Let us know if this helps you plan your solution further.

Answer 2

Service bus connector is not available in Data Factory. Nevertheless, there are different choices that you can be aware of :

• You can specify a consumer for Data Factory to use in subsequent calls.
• You can post feature request in the feedback forum.
• You can reroute your messages to a blob using this method, and then avail yourself to the functionalities of the Blob Event Trigger.
• Retrieve a message using the Web Activity from ADF.