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:

• 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