SQL Server 2000 DTS Part 11 - DTS Designer Tasks - the Message Queue task

Tuesday Mar 9th 2004 by Marcin Policht

Part 11 of DTS Designer Tasks examines the Message Queue task, discussing the concept of message queuing and its Microsoft-specific implementation, describes its features and provides an example demonstrating their use.

It is finally time to complete our coverage of DTS tasks - the only remaining one that we have not presented yet is the Message Queue task. However, before we describe its features and provide an example demonstrating their use, let's first spend some time discussing the concept of message queuing and its Microsoft-specific implementation.

The main purpose of message queuing is providing reliable data exchange between distributed (potentially heterogeneous) systems that might not be able to operate simultaneously at the same capacity. This way you can accommodate scenarios where some type of information from a data source needs to be processed, but you might not be able guarantee its immediate delivery due to connectivity problems or temporary unavailability of a data target. Similarly, you can allow for differences in processing ability between both systems, without being concerned about overwhelming the destination with message overflow. Such functionality can also be beneficial in load balancing configuration, where multiple targets can monitor a single queue and process any awaiting message.

Message queuing first became available on the Windows platform as part of Windows NT 4.0 Option Pack add-on, but starting with Windows 2000 it was included as a standard operating system component (available from the Add/Remove Programs applet in the Control Panel). Its Windows XP installation consists of the following elements:

  • Active Directory Integration - (applicable when a computer is a member of a Windows 2000/2003 Active Directory domain) - provides representation of public message queues (as opposed to private ones, which are not represented in the Active Directory) as subnodes of Active Directory msmq objects.
  • Common - implements core functionality and allocates local storage for messages.
  • MSMQ HTTP Support - allows sending and receiving messages over HTTP protocol (proxy settings are configurable with the proxycfg.exe utility). This option relies on Message Queuing ISAPI extensions being present on Internet Information Server (managed with IIS Administrator MMC snap-in).
  • Triggers - enables triggering execution of a code in COM components (or executables) in response to arrival of a message.

Full installation creates two services - Message Queuing and Message Queuing Triggers, and registers Message Queuing in the Active Directory database. (You can view it by launching Active Directory Users and Computers, selecting "Advanced Features" and "Users, Groups, and Computers as containers" options in the View menu, and displaying content of a computer object representing the system on which Message Queuing has been installed). You will notice that the computer object contains msmq sub-object. Every public message queue you define on the system running Message Queuing service will have a corresponding public queue object residing in the msmq node.

To install Message Queuing on Windows 2003 server, launch Windows Component Wizard (by clicking on the Add/Remove Windows Components button) from Add or Remove Programs in the Control Panel. On the first page of the wizard, locate the Application Server entry, use Details button to display its subcomponents, and select the Message Queuing option. In addition to the choices available for Windows XP, you will also be able to install:

  • Downlevel Client Support - provides Active Directory support for downlevel clients. This might require modifying Active Directory permissions if the Message Queuing services on downlevel clients are operating under local system account security context.
  • Routing Support - allows store-and-forward mechanism and routing of messages using Active Directory based information.

The most basic scenario in which Data Transformation Services utilize message queuing involves one DTS package serving as a Queue Sender, another as Queue Receiver, and an intermediate system providing Message Queuing services. While typically each of these components resides on a separate physical system, you can also co-locate all of them on the same SQL 2000 Server. Regardless of how you configure your solution, you have to install Message Queuing components on the intermediate server. In order to take advantage of Active Directory Integration and use public queues, this computer needs to be a member of Active Directory Domain. Once the installation of the Message Queuing has completed, open local Computer Management MMC snap-in. You will find Message Queuing subnode listed under the Services and Applications node. From here, you can create private and public (if you installed Active Directory integration) messaging queues. For the purpose of our exercise, create one public and one private queue, named SQLPublic and SQLPrivate, respectively. This is done by right clicking on the one of the Queues top-level folders (Public or Private) and selecting New from the context sensitive menu. Notice that a public queue is referred to simply by the name you assigned to it (prefixed with the computer name), while the private one has an additional private$ element. For example, assuming that our server is called MRBIG, the queues will be referred to as MRBIG\SQLPublic and MRBIG\private$\SQLPrivate. You also have an option of making each queue transactional, although in our case, this is not required. Creation of public queue will also result in the SQLPublic entry appearing under msmq subnode of MRBIG computer object in the Active Directory.

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

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved