MSMQ & Integration Services

I have a SSIS package in SQL Server 2005 that attempts to publish some data into a MSMQ Private queue located on another machine.

While I was creating the package the connection string was pointing to the local server something like: localhost\private$\csin

Now I'm trying to get to the remote machine but I'm not sure what type of connection string I need to enter:

I tried: mw59\private$\csin where mw59 is the name of the remote machine I need to publish to but that result into a "Invalid Queue Path Name."

I tried: formatname:DIRECT=OS:mw59\private$\csin but that results in "The specified format name does not support the requested operation...."

Any idea?

Thanks,

Thierry

I haven't tried MSMQ in SSIS yet but I know that in SQL 2000 DTS the standard MSMQ task could not access remote private queues. I got around this with a couple of fairly simple/generic VBS tasks.

One task reads a text file into the message body and sends the message to a remote private queue. The second taks reads messages from the queue and merges them into a single text file. The next step in this tasks uses the text file source connection to read the data from the text file into a SQL table.

Dick Campbell

|||

The next Web update of Books Online, scheduled to coincide with Service Pack 1, contains an already-written new topic titled "Sending to a Remote Private Message Queue with the Script Task." Sadly, as we all know, it is not a pretty sight when one attempts to copy and paste the HTML of a BOL topic. Here is my best effort after converting it to plain text:

Sending to a Remote Private Message Queue with the Script Task

Microsoft Windows Message Queuing makes it easy for developers to communicate with application programs quickly and reliably by sending and receiving messages. A message queue may be located on the local computer or a remote computer, and may be public or private. The Integration Services MSMQ Connection Manager and Message Queue Task do not support sending to a private queue on a remote computer. However, by using the Script task, it is easy to send a message to a remote private queue.

Description

The following example uses an existing MSMQ connection manager, together with objects and methods from the System.Messaging namespace, to send the text contained in a package variable to a remote private message queue. The call to the AcquireConnection method of the MSMQ connection manager returns a MessageQueueTask object whose Send method accomplishes this task.

To configure this Script Task example

Create an MSMQ Connection Manager with the default name. Set its path to a valid remote private queue, in the following format:

FORMATNAME:DIRECT=OS:<computername>\private$\<queuename>

Create an Integration Services variable named MessageText of type String to pass the message text into the script. Enter a default message as the value of the variable.

Add a Script Task to the design surface and edit it. On the Script tab of the Script Task Editor, add the MessageText variable to the ReadOnlyVariables property to make the variable available inside the script.

Click Design Script to open the Visual Studio for Applications script editor.

Add a reference in the script project to the System.Messaging namespace.

Replace the contents of the script window with the code in the following section.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Messaging

Public Class ScriptMain

Public Sub Main()

Dim remotePrivateQueue As MessageQueue
Dim messageText As String

remotePrivateQueue = _
DirectCast(Dts.Connections("Message Queue Connection Manager").AcquireConnection(Dts.Transaction), _
MessageQueue)
messageText = DirectCast(Dts.Variables("MessageText").Value, String)
remotePrivateQueue.Send(messageText)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

-Doug