SQL Server 2000 DTS Part 11 - DTS Designer Tasks - the Message Queue task - Page 2
March 9, 2004
Next, from SQL Server Enterprise Manager on the SQL Server functioning as the message sender, launch the DTS Designer. Create a new package called "MQ Sender" and add to it a single Message Queue task (from the Task menu or the left-hand side toolbar). In the Message Queue Task Properties dialog box, type in description (e.g. "Send private"), in the Message drop-down list select Send messages, and specify MRBIG\private$\SQLPrivate as the Queue entry. In the Messages to be sent area, create each one of the following message types:
As you will see shortly, for each message type, you can define filtering criteria in a target package that will help to determine whether a particular message has been received. Save the package, execute it, and ensure that you receive confirmation of its successful completion. Re-open the Message Queuing node in the Computer Management MMC snap-in and check the content of the SQLPrivate private queue. You will notice that it contains three messages, which we defined in the DTS "Send private" package.
You could easily create a similar package (or use the same package by adding another Message Queue task) that would send the same three types of messages to our public queue. To accomplish this, you would need to specify the target queue as MRBIG\SQLPublic. In addition, unlike with private queues, at the time of execution of the package, you need to be able to access information stored in Active Directory, so it is important to verify that you have connectivity to one of Windows 2000/2003 domain controllers.
Now that messages are stored in their respective message queues, let's look into a way of retrieving them. Launch DTS Designer from SQL Enterprise Manager on the target SQL Server and create a new package called "MQ Receiver." Add three Message Queue tasks to it. For each of them, select the "Receive messages" option from the drop down list, set the Queue name to MRBIG\private$\SQLPrivate, and assign the following descriptions and configuration parameters:
For each type of message, you can specify whether you want to remove a processed message from the queue (we will use the default, which causes message removal) and set timeout, determining how long the DTS task will wait for a message matching filtering conditions before failure (leave no timeout default on). Once all tasks are configured, save the package, execute it and verify that it completed successfully. If you check the content of the SQLPrivate private queue in the Message Queuing node of the Computer Management MMC snap-in, you will notice that all messages have been removed (following the "Remove from message queue" setting on the Message Queue Task Properties dialog boxes we just configured).
Since DTS Message Queue tasks do not provide transformation capabilities (but are intended for reliable transfer of data between data stores), they typically are just one of the subcomponents of multi-task DTS packages. For more elaborate examples, refer to Books Online. You can also find a plethora on information on Microsoft Message Queuing (including white papers, case studies, and downloads) at the Microsoft Message Queuing Center web site.