PaulKroon.com

Sending a plain string to a MS Message Queue in SSIS

When dealing with an application that will remain nameless, I needed to use SQL Server Integration Services to generate some data, and then put a string into a MSMQ.  While trying to get it to work, I went through the MSDN forums; the thread is here.  You can follow it along there, but the basic solution is to add “MessageQueueTransactionType.Single” as the transaction type parameter when sending the Message to the MessageQueue.

For reference, here’s my code that worked, but didn’t put anything in the queue until I added that transaction type parameter:

Public Sub Main()
        Dim queueMessage As Message
        Dim theQueue As MessageQueue
        Dim messageStream As IO.StreamWriter

        Try
            theQueue = New MessageQueue(".\private$\queuename")
        Catch
            MsgBox("set up queue - " + Err.Description)
            Dts.TaskResult() = Dts.Results.Failure
            Exit Sub
        End Try

        Try
            messageStream = New IO.StreamWriter(New IO.MemoryStream)
            messageStream.Write(Dts.Variables("XMLString").Value.ToString())
            messageStream.Flush()

            queueMessage = New Message()
            queueMessage.BodyStream = messageStream.BaseStream
        Catch
            MsgBox("set up message - " + Err.Description)
            Dts.TaskResult() = Dts.Results.Failure
            Exit Sub
        End Try

        Try
            theQueue.Send(queueMessage)
        Catch
            MsgBox("send message - " + Err.Description)
            Dts.TaskResult() = Dts.Results.Failure
            Exit Sub
        End Try

        Dts.TaskResult() = Dts.Results.Success

    End Sub