SSIS Connectivity to SQL Server Service Broker

Question

SSIS Connectivity to SQL Server Service Broker

sriram979

Old Hand

Points: 300
More actions
January 25, 2012 at 10:37 am

#391405

Hi,
Is there anyway we can use SSIS to push data to SQL Server Service Broker queue for asynchronous processing of data?
Regards,
SriRam.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Well in a data-flow you could use a OLE DB Command to write items into the queue but I don't believe you could do a bulk insert.

You could also use an EXEC SQL command to write an item to the queue..

How much data are we talking about here?

CEWII

sriram979 Old Hand Points: 300 More actions · Answer 2

Hi Elliott,

Thanks for the reply. I understand, when we speak about using OLEDB command, evidently there would be performance bottleneck. I am right now not sure about the amount of data, but the intention is not to hit the live tables directly while loading the data. Can you please pass me the exec SQL command to pass data to the queue.

Thanks again for your help.

Regards,

SriRam.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Script examples for Service Broker:

http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Koen posted a link.

I would do it through a stored procedure. I am guessing you don't have much experience with Service Broker, I would go through that link and get some additional familiarity. It isn't real hard to do but you should get a passable understanding of it.

If there are a lot of rows you are likely to experience substantial bottlenecks which might force you to alter the process. A few thousand rows would probably be fine, the write into the queue should be fairly swift on its own..

CEWII