Exploring the SSIS MQ Task

I’ve been exploring the Message Queue Task in SSIS and in this blog I will share some experiences, because the BOL is not really clear on these points.

Quote from the Books Online:

The Message Queue task allows you to use Message Queuing (also known as MSMQ) to send and receive messages between SQL Server Integration Services packages, or to send messages to an application queue that is processed by a custom application. These messages can take the form of simple text, files, or variables and their values.

So does this mean it is not designed to read messages send by a custom application, from a queue? After exploring the task, my response would be: no, it’s not designed for that. But to take that a step further, I’d say is not even designed to send messages to a queue that is processed by a custom application. There are just to many quirks in the design. Here’s a summary:

– There is a very strict limitation on the Message label
– You can’t get a list of messages in the queue.
– You can’t use remote private queues.
I’ll describe these issues in more detail below.

Message label restriction

This was actually the first issues I came across. I set up a test within SSIS, where I successfully put a message on a local private queue with the MQ task, but when trying to read it with the MQ task, it simply hang there and did nothing. Then I found this blog on MSDN that had useful warning: http://blogs.msdn.com/b/martijnh/archive/2010/06/10/reading-msmq-from-within-ssis-packages.aspx

For SSIS to be able to read the queue, ensure you label everything you put onto the queue with the label ‘String message’. If you don’t the message will not be picked up by SSIS.

First of all, I have not been able to find this warning documented in the BOL at all. But it’s not the complete story either.

Why was my SSIS MQ Read Task unable to read the message put on the queue by the SSIS MQ Send Task? That’s because my message had the message label “Variables Message”. Why would SSIS set that label if it can’t read messages with that label itself? That’s because it’s a different kind of message. The BOL has a section on Message Types, but it doesn’t clearly sate that there are effectively three different message types:

– Data file message
– Variables message
– String message

With choosing the “Message type” in the GUI, you do not only specify the Message Type, but also the Message label and the source or destination of the Message. The Message label is the same as the message type in the GUI, except for the Variable Message; there the label is “Variables Message”! (Notice the extra s.). The message type should really be Variables message, because you can select multiple variables to be included in the message. Furthermore, the Message Type option “String message to variable” in the GUI isn’t really a different Message Type; it is just a String Message with a different destination.

So the problem I had was that I incorrectly assumed that the task would send the contents of my variable (a string) as a string. But it actually creates a special SOAP document that includes all the details of the variable(s) specified. If you want to read this kind of message with the SSIS MQ task, you will have to specify that you want to read a Variables Message. This message type is apperantly specially designed for SSIS to exchange variables between SSIS packages.

Same with the Data file message; if you want to put the contents of a data file (for example a xml-file) on a queue to be processed by a custom application, then you do NOT want to use the Data file message, because it will create a special type of message that includes the contents of the data file within a predefined XML-message, probably also designed (only) for SSIS to SSIS messaging.

Because of the label restriction, you also can’t put the contents of a file on a queue with a Data File Message and expect to be able to read it directly into a SSIS variable, because the MQ Task only supports writing the contents of a Data File Message back to a file.

If you want to put the contents of a file or a SSIS variable as a simple string in a message, you will have to use the String Message type. Since the String Message type only supports a constant (the MessageString) as a source, you will have use SSIS Property Expressions to assign the value of the property during runtime:

I wondered how these different messages would look like, so I used a small Powershell script to read out the queue:

$queuename = ‘.private$test_rpellicaan’
[Reflection.Assembly]::LoadWithPartialName(“System.Messaging”) | out-null

$queue = new-object System.Messaging.MessageQueue($queuename)
$messages = $queue.GetAllMessages()
foreach ( $message in $messages )
{
if ($message.Label -eq “String Message”)
{
$StreamReader = new-object System.IO.StreamReader( $message.BodyStream, [System.Text.Encoding]::Unicode, $true )
}
else
{
$StreamReader = new-object System.IO.StreamReader( $message.BodyStream, [System.Text.Encoding]::Default, $true)
}
$StreamReader.ReadToEnd() | Out-File “C:tempmsmqout$($message.LookupId)”
}

Notice that I had to change the encoding to Unicode when reading messages of the String Message type. The other two Message types do not use Unicode.

Variables Message

Notice the SOAP envelope in this message type.

Data File Message

The actual file contents is within the <fileContents> tag.

String Message

It’s clear that you most probably want to use the String Message type to exchange information with a custom application, because the other types add SSIS specific contents to the message. A side-effect is that your message will automatically get the Message Label “String Message” assigned to it when using the SSIS MQ Task and that the MQ task will only read message on a queue with that label.

Retrieving list of messages in the queue

Another quirck in the SSIS MQ Task is that it lacks any logic to recieve allmessages on the queue. Neither does it give you possibility to get a list of messages, which makes using a ForEach Loop Container rather difficult.

As discussed here: MSMQ Task to Receive Multiple Messages, you could have the MSMQ Task Receive Time Out set to raise an error and attach an Event Handler to the DataFlow Task and set its Propagate OnError property to false, but I would not call that a very elegant approach.

The only elegant option seems to be to add a Script Task to get the number of messages and use that as an input to a ForEach Loop.

Using remote private queues

Sending messages to a remote private queue can’t be done with the MQ Task. Microsoft recommends using Script Task for it instead and fortunately gives us a code example for it:

Sending to a Remote Private Message Queue with the Script Task

Conclusion

The SSIS MQ Task seems primarily designed to exchange a single message between two SSIS packages. It can be used to interact with other (custom) applications, if you are able to live with its limitations. By using a Script Task instead, you will get much more flexibility.