How can SQL Server 2012 (or SSIS) notify NServiceBus upon completion of a task?

Question

We have some very long running ETL packages (some run for hours) that need to be kicked off by NServiceBus endpoints. We do not need to keep a single transaction alive for the entire process, and can break it up into smaller transactions. Since an NServiceBus handler will wrap itself in a transaction for the entirety, we do not want to handle this in a single transaction because it will time out--let alone create issues with locking in the DBMS.

My current thoughts are that we could spawn another process asynchronously, immediately return from the handler, and publish an event upon completion (success or failure). I have not found a lot of documentation on how to integrate the new NServiceBus 4.0 SQL Server Broker support with the traditional MSMQ transport. Is that even possible?

What is the preferred way to have a long running process in SQL Server 2012 (or an SSIS package) notify NServiceBus subscribers when it completes in an asynchronous manner?

Knowing nothing of nservicebus, is there something that prevents your SSIS package from calling a method/dropping a message into the queue as it completes? — billinkc
Not that I'm aware of, I'm simply asking what the easiest way to do that would be. Worst case is that you could manually write code in SSIS (or some managed code in the CLR on SQL Server) to drop a message in an MSMQ queue--however, you then lose some of the functionality built into NServiceBus that helps you manage those queues. — JD Courtoy

John Simons John Simons · Accepted Answer · 2013-08-23T02:17:24

It looks like it is possible to do a http request from SSIS, see How to make an HTTP request from SSIS?

With that in mind you can use send a message to NServiceBus via the Gateway (the Gateway is just an HttpListener) to your Publisher to tell it to publish a message informing all the subscribers that the long running ETL package has completed.

To send a message to the gateway you need to do something like:

var webRequest = (HttpWebRequest)WebRequest.Create("http://localhost:25898/Headquarters/");
webRequest.Method = "POST";
webRequest.ContentType = "text/xml; charset=utf-8";
webRequest.UserAgent = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)";

webRequest.Headers.Add("Content-Encoding", "utf-8");
webRequest.Headers.Add("NServiceBus.CallType", "Submit");
webRequest.Headers.Add("NServiceBus.AutoAck", "true");
webRequest.Headers.Add("NServiceBus.Id", Guid.NewGuid().ToString("N"));

const string message = "<?xml version=\"1.0\" ?><Messages xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns=\"http://tempuri.net/NServiceBus.AcceptanceTests.Gateway\"><MyRequest></MyRequest></Messages>";

using (var messagePayload = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(message)))
{
    webRequest.Headers.Add(HttpRequestHeader.ContentMd5, HttpUtility.UrlEncode(Hasher.Hash(messagePayload))); //Need to specify MD5 hash of the payload
    webRequest.ContentLength = messagePayload.Length;

    using (var requestStream = webRequest.GetRequestStream())
    {
        messagePayload.CopyTo(requestStream);
    }
}

using (var myWebResponse = (HttpWebResponse) webRequest.GetResponse())
{
    if (myWebResponse.StatusCode == HttpStatusCode.OK)
    {
        //success
    }
}

Hope this helps!

How can SQL Server 2012 (or SSIS) notify NServiceBus upon completion of a task?

3 Answers