A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, March 10, 2014

Biml - Message Queue Task

Biml - Message Queue Task

Why have one tool when you can multiples? In this case, queuing technologies. As a SQL Server professional, I was aware of Service Broker but there's a whole native queuing technology built into Windows: Microsoft Message Queuing. Until doing the research on the SSIS Message Queue Task, I had assumed it pulled from Service Broker. Yeah, well assumptions...

MSMQ Setup

I originally created a private queue on a different box than I was running my package on but the native Message Queue Task can't converse with a remote server. MSDN has a write up on how you can use a script task to talk to a remote private queue


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <MsmqConnection Name="CM_MSMQ" Path=".\private$\POC" />

        <Package ConstraintMode="Linear" Name="Task_MessageQueue">
                <Variable Name="MessageSource" DataType="String">What God hath wrought</Variable>
                <Variable Name="MessageReceipt" DataType="String"></Variable>
                    Name="MQ Send Message"
                    <VariableInput VariableName="User.MessageSource"></VariableInput>
                        <Expression PropertyName="MessageString">@[User::MessageSource]</Expression>

                    Name="MQ Receive Message"
                    <StringVariableOutput VariableName="User.MessageReceipt" />


A package is created with a pair of Message Queue Tasks. One to send and one to receive. There are two Variables: MessageSource and MessageReceipt, both of type String. MessageSource is preloaded with a value of What God hath wrought. After execution, if all went well, that value will be loaded into MessageReceipt. All of this will use our MSMQ connection manager, CM_MSMQ.


Here's a screenshot of what the connection manager looks like. The value of the path is .\private$\POC to indicate the message queue server is local, it's a private queue named POC.

MQ Send Message

A send message task will put our message onto our private queue, POC.

General tab

We have set the task to send a message

Send tab

Sent tab is set to send an unencrypted message. While the StringMessage property appears to be hard coded, that's just an artifact of how the UI works.


The MessageString is actually configured based on our @[User::MessageSource] Variable.

MQ Receive Message

A receive message task will pull messages off the queue and assign their value to our Variable.

General tab

The task is configured to receive messages. As you'll see, there's no need for expressions on this task.

Receive tab

In the Receive tab, we remove them from the source queue and assign the message into our Variable User::MessageReceipt.

Execution Results

Green is good. Even better is that the value of @[User::MessageReceipt] matches @[User::MessageSource]

No comments: