How does SQL Server 2012 (or SSIS) notify NServiceBus after a task completes?

Question

We have very long ETL packages (some work for several hours) that need to be run by NServiceBus endpoints. We do not need to maintain a single transaction for the entire process and break it down into smaller transactions. Since the NServiceBus handler will be fully associated with the transaction, we do not want to process this in one transaction, because it will time out, not to mention the creation of locking problems in the DBMS.

My current thoughts are that we can start another process asynchronously, immediately return from the handler and post the event upon completion (success or failure). I did not find much documentation on how to integrate the new NServiceBus 4.0 SQL Server Broker support with traditional MSMQ transport. Is it possible?

What is the preferred way to have a lengthy process in SQL Server 2012 (or an SSIS package) to notify NServiceBus subscribers when it terminates asynchronously?

Answer 1

It looks like you can execute an HTTP request from SSIS, see How to make an HTTP request from SSIS?

With this in mind, you can send a message to NServiceBus through the gateway (the gateway is just an HttpListener ) for your publisher to let him publish a message informing all subscribers about the ETL package shutdown.

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!

Answer 2

In SSIS 2012 there is actually a task to post messages to MSMQ, the message queue task . You simply point this to your MSMQ connection and you can use the expression to customize your message with the package name, success / failure, number of lines, etc.

Depending on how many packages we are talking about and how your messages are configured, it is best to write a standalone utility to create messages in any format you want, and then use the execution task to call that utility with any parameters from the package you want transmit, must be formatted in the message.

You can also use the same code base and just create a custom SSIS task (much easier than it sounds).

Answer 3

The idea that I needed to adhere to the DRY principle would be to use the SSIS Master package.

In my opinion, this would look like an Execute Package task with X connected to it. Configure the package as the Package Name parameter. Configure the Execute Package task to use the parameter to determine which package to invoke.

X is likely to be a Script task, but perhaps, as @Kyle Hale points out, it could be a Message Queue Task . I leave this solution to those who are more versed in NServiceBus.

The important thing, in my opinion, is not to add this logic to every package, as this will be a maintenance nightmare.