Reading MSMQ from within SSIS packages

  • Article

Recently, I had to implement an SSIS package which imports some data into SQL Server from MSMQ. It turns out reading MSMQ from within an SSIS package is more difficult than it seems at first. I’ve documented how to get things working because I could find little other documentation.

When reading data from MSMQ, there’s multiple steps to take:

  • Setting up a queue, if not yet available
  • Set up a connection with MSMQ by adding a connection manager to the SSIS package
  • Adding and configuring a Message Queue Task
  • Putting data onto the queue
  • Running the package to validate the data is read from the queue

Setting up a queue

In Windows Server (2008 R2 in my case), create a new queue. It doesn’t really matter whether this queue is transactional or not.

Setting up a connection with MSMQ from within an SSIS package

First things first, let’s start with setting up a connection manager for MSMQ. When creating a new SSIS package, this is the view you get:

In the “Connection Managers” area, right-click and choose “new connection”. From the list that is displayed, select the “MSMQ” type and click “Add”.

 

A new dialog pops up, in which you can configure the connection to be made. I choose “.\Private$\mh” (a local queue called “mh”). Click “Test” to ensure the connection can be made and click “OK”.

The connection manager is set up.

Adding a Message Queue Task

Adding a Message Queue task to the Control Flow is easy, drag and drop it from the Toolbox on the left (press CTRL-ALT-X if it’s not displayed) to the Control Flow surface, like displayed below.

To configure the Message Queue Task, double-click the Message Queue task’s icon on the left of the Task in the Control Flow. A new dialog pops up in which we’ll configure the task. Select the connection manager we set up previously for the “MSMQConnection” property within the dialog and change the default “Send message” value for the “Message” property to “Receive message”.

Change to the “Receive” configuration page by click “Receive” on the left-hand side of the dialog. Choose whether the message should be removed from the queue after it is read by SSIS. I’ve chosen not to timeout reading the queue. Set the “MessageType” property to “String message to variable”.

Click on the drop-down for the “Variable” property and choose “New variable". This will show a new dialog, in which a new variable can be defined. For this example, let’s use the information shown below and click “OK”.

After closing the dialog, we’ll be returned to the “Message Queue Task editor” dialog, which shows the newly created variable.

The Message Queue Task is configured to read messages from the “.\Private$\mh” queue.

Putting data onto the queue

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.

Validation the package

Pressing F-5 (Debug) from within the Business Intelligence Development Studio will run the package, effectively picking up a message from the queue if there is any.

 

HTH, happy trails!