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.
It’s fairly straight-forward.
registerComponent.ps1
script, included in the solution.ID
, Label
and Body
– as its output.Or, if you’re really trusting, download the compiled DLL here and skip the first two steps.
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.