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...
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"> <Connections> <MsmqConnection Name="CM_MSMQ" Path=".\private$\POC" /> </Connections> <Packages> <Package ConstraintMode="Linear" Name="Task_MessageQueue"> <Variables> <Variable Name="MessageSource" DataType="String">What God hath wrought</Variable> <Variable Name="MessageReceipt" DataType="String"></Variable> </Variables> <Tasks> <MessageQueue MsmqConnectionName="CM_MSMQ" Name="MQ Send Message" > <VariableInput VariableName="User.MessageSource"></VariableInput> <Expressions> <Expression PropertyName="MessageString">@[User::MessageSource]</Expression> </Expressions> </MessageQueue> <MessageQueue MsmqConnectionName="CM_MSMQ" Name="MQ Receive Message" RemoveFromQueue="true" ReceiveMessageTimeOut="30" > <StringVariableOutput VariableName="User.MessageReceipt" /> </MessageQueue> </Tasks> </Package> </Packages> </Biml>
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 MessageA send message task will put our message onto our private queue, POC.
We have set the task to send a message
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.
ExpressionsThe 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 tabThe task is configured to receive messages. As you'll see, there's no need for expressions on this task.
In the Receive tab, we remove them from the source queue and assign the message into our Variable User::MessageReceipt.