Rapture in Everything

MSMQ reader component for SSIS

zblesk -

Last year I had to use SQL Server Intergration Services package to read some data from a MSMQ queue and pump them into a database. It sounded easy enough, but I quickly ran into difficulties. While SSIS does provide some MSMQ reading/writing components, they seem to be primarily meant to facilitate communication between two SSIS packages – I hadn’t been able to make them just read the queue and dump the textual data.

So I set out to implement my own component. It was a major pain, so I’m open sourcing it, in case someone else needs it; you can grab it on GitHub under the MIT license.

I’ve been able to successfully deploy and use the component in production, but this is not the same component. The original one was messier and contained some application-specific additions, so in order to open-source it, I just re-wrote the component from scratch. It should run just as well, but is a lot cleaner.

This post contains some bits and pieces that should get you up and running.

Usage

It’s fairly straight-forward.

  1. Clone the repo
  2. Open solution and build. (Keep in mind you need the DLLs from the Business Intelligence package.)
  3. Register the component for use in SSIS – copy in the appropriate folders and register in GAC. This is done by the registerComponent.ps1 script, included in the solution.
  4. Open Data Tools (The Visual Studio shell that lets you build Integration Services projects). You should see “MSMQ Reader” in the Data Component menu. (Note you have to start the VS after registering the component, so if it was already running, restart it.)
  5. After you drag-drop the component into the project, you only have to set a single property – the path to the queue to be used.
  6. Use the component like any other. It provides three columns – ID, Label and Body – as its output.

Or, if you’re really trusting, download the compiled DLL here and skip the first two steps.

Other details

If you do not specify any queue path (i.e. you leave the property blank), the reader doesn’t throw an exception; it just doesn’t produce any output and quits. (You can also set it via a package parameter.)

A reminder: if you need to target a local queue, you can just write the path. Example for a private queue:

.\Private$\QueueName

If you’re targeting a (private) queue on a remote computer, you most probably want something like:

FormatName:DIRECT=PathToHost\Private$\QueueName

What sould it look like in action:

[

And let me stress this again:

I’ve successfully used the MSMQ component in production; however, this one has been re-written from scratch and isn’t battle-tested yet. It should work (the principles are the same and the code is cleaner), but keep it in mind.

Update: While trying to get this to work, I’ve had to manipulate MSMQ queues many, many times. I rewrote the scripts that I used and made them available, too.