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:
-
String Message - used to notify a target system about specific
events. In our case, simply type in "Moe" as the content of the
message.
-
Global Variable Message - used to exchange data between two
packages. In our case, create a new global variable (The "Create Global
Variable" command button is available in the same dialog box) called gvMQPriv
of String type and assign to it the value "Larry"
-
Data File Message - used to send a file to a target computer. In
our case, create a text file in the C:\Windows directory and call it
"Curly.txt." For the purpose of this example, its content is
irrelevant.
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:
"Receive private string" - in the Message type list
box, select "String Message." This gives you a number of filtering
options - None, Exact match, Ignore case, and Containing. None will accept
every message string from the private queue you specified, while the remaining
three will compare strings with text typed into the Compare string text box.
You can experiment with each of these choices, by either selecting None or
typing, for example "Moe", "moe", or "Mo".
"Receive private global variable" - in the Message type
list box, select "Global Variable Message." This gives you a number
of filtering options - No filter, From package, and From Version. The last two
allow you to connect to a SQL Server where the DTS "Send private" was
defined, selecting package store (SQL Server, SQL Server Meta Data Services, or
Structured Storage File), and choosing either a package name or its specific
version. In our case, choose simply DTS "Send private" package.
"Receive private file" - in the Message type list box,
select "Data File Message," type a target file name in the "Save
file as" text box (e.g. "CurlyOut.txt"), and use a check box to
specify whether a target file should be overwritten if it already exists. This
gives you the same three filtering options that are available with athe "Global
Variable Message" message type. To complete configuration, choose DTS
"Send private" package, as above.
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.
»
See All Articles by Columnist Marcin Policht
» 
Comment and Contribute
