Is service broker a good option? RRS feed

  • Question

  • Hi,

    I am developing a solution where an external app (.net based) at different client locations would upload CSV files in a queue and SQL server would then pick those files (from all different client) and then process them one by one.

    I am new to Service Broker, and was wondering if its worth spending time looking into Service Broker to achieve the goal. 

    My main question is, is it possible to queue files in the service broker? if not, can someone please suggest a better solution.



    Monday, October 14, 2019 5:20 AM

All replies

  • Service Broker could be a fit here.

    Although you cannot queue files as such. SQL Server reads data from a database, working with files does not fall withing its core business. But you could post a filename on a Service Broker queue, and an activation procedure could then use BULK INSERT to load the file. This would require the .NET app to put the files in a place where SQL Server can read the files.

    An alternative is that the .NET reads and parses the file and post the data directly on the Service Broker queue as XML or JSON. (Not as CSV, since SQL Server has no built-support to parse CSV.)

    This is a really great book to learn Service Broker:

    It's written by the Program Manager who was responsible for the development of Service Broker. In difference to many other computer books, this is not a brick - it's only 222 pages.

    Also check out Remus Rusanu's blog, http://rusanu.com/. He was one of the developers of the product, and his blog has some really good stuff.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 14, 2019 9:52 PM