Retrieve SQL Broker Messages - On Prem

Santhi Dhanuskodi 145

Hi,

I want to read sql broker messages from on-prem sql server and process and send back to another API.

What is the best solutions to achieve this? Can Azure data factory help on this?

Santhi Dhanuskodi 145 Reputation points

2024-04-10T07:27:23.8466667+00:00

SQL Server Linked services and dataset provide an option only to select table by default.

Will it show broker queues? Can I write the sql query to retrieve brokr messages?

my sample query would be

"WAITFOR ( RECEIVE conversation_handle, service_name, message_type_name, message_body, message_sequence_number FROM testQueue)";
BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-10T19:58:09.2266667+00:00

No, SQL Server Linked Services and Datasets do not provide a direct option to select SQL Broker queues or retrieve Broker messages.

To read SQL Broker messages from a queue, you would need to use a SQL Server connector and write a custom SQL query to retrieve the messages.

You can use the "Stored Procedure" or "Query" option to execute the custom SQL query that retrieves the Broker messages.

I hope this helps.
Santhi Dhanuskodi 145 Reputation points

2024-04-11T07:08:46.23+00:00

I was going through internet on ADF capabilities. Many of them said ADF can not read SQL broker messages. Could you please confirm that it can read sql broker messages?
BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-11T20:35:22.52+00:00

I am not an expert on the SQL service broker, but ADF can execute your custome query(sample query in your comment) that retrieves the SQL Service Broker messages and send the output to a target API.
BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-17T17:27:34.3166667+00:00

Santhi Dhanuskodi

I am checking to see if you have any further questions here.
Santhi Dhanuskodi 145 Reputation points

2024-04-18T09:29:27.8533333+00:00

Thank you. No more questions.
BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-18T22:17:05.2633333+00:00

Thank you Santhi Dhanuskodi

I have converted my comment to answer. If you don't have any further questions, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

Accepted answer

BhargavaGunnam-MSFT 26,136 Reputation points Microsoft Employee

2024-04-09T17:28:23.51+00:00
Hello Santhi Dhanuskodi,

Yes, I belive you can use ADF to read SQL Broker messages from an on-premises SQL and process them before sending them to another API.

You can use the dataflow for this.

Here are the high level steps:

Configure a self-hosted integration runtime and connect to source on-prem.

Use a data flow in the pipeline to transform the messages as needed. You can use the transformations like filtering, aggregating, and joining etc

Use the REST connector as sink in ADF to send the transformed messages to the target API.

Schedule the pipeline.

I hope this helps.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Retrieve SQL Broker Messages - On Prem

0 additional answers