Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 1, 2006

SQL Server 2005 Integration Services - Message Queue Task - Part 32

By Marcin Policht

After concluding a broad overview of various aspects of SQL Server 2005 Integration Services, ranging from its innovative architecture, through main package design principles, along with debugging and deployment techniques, to security and performance consideration, we will focus now on individual components that have more specialized, but very useful characteristics. We will start by discussing the Message Queue Control Flow task, and follow with coverage of a number of Data Flow transformations.

However, before we look further into SSIS-specific topics, we need to present the most essential concepts of message queuing. The main purpose of this middleware component, included in all current Windows operating system editions (with Microsoft Message Queuing 3.0 available in Windows XP Professional and Windows 2003 Server), is to provide a reliable exchange of messages between applications. Its functionality is based on store and forward mechanism, which ensures delivery even if intermediary or target systems are temporarily unavailable. Each message generated by a source application is placed in the local outgoing queue and routed to a destination queue where it awaits retrieval by its intended recipient. This feature can be employed in a number of scenarios; for example, coordinating execution across multiple components (with unreliable communication media between them) of a distributed system (e.g. order placement and order fulfillment software running on separate computers) or addressing performance and timing differences between the sender and receiver (where one can not keep up with the processing speed of the other or both operate according to different schedules).

There are two types of queues - private and public, with a number of differences between them, resulting mainly from the Active Directory integration of the latter. Private queues register their information exclusively on the local system where the MSMQ 3.0 is installed. They are accessible via their path name, which consists of three parts - computer name (if a queue is located on the same computer, it is possible to use "." instead), "Private$" character string, and the queue name - separated by backslashes (e.g. DBJournalServer\Private$\DBJournalPrivateQueue or .\Private$\DBJournalPrivateQueue for queues in case of a local queue). Public queues, whose address consists of their name, prefixed by the name of the computer hosting them, and the backslash character, are published in (and replicated across) Active Directory (keep in mind that this applies only to queue parameters - not its content), which makes them easier to find by running domain or forest-wide searches.

Note, however, that this future is typically of little practical use (unless queues need to be located at runtime). In addition, since referencing them requires a lookup of their registration parameters on a domain controller, their operational status depends on the health of Active Directory and might be a subject to latency issues (especially during initial access). For the same reason, they are not available in a workgroup environment. On the other hand, public queues simplify centralized management (private queues can be only administered locally) and backup, as well as provide built-in user authentication and message encryption (by leveraging user and computer certificates registered in Active Directory).

The ability to create public queues depends on the installation mode of Microsoft Message Queuing. In the workgroup mode, which applies to systems that are not members of an Active Directory domain or those without Active Directory Integration component (more about this shortly) present, you are limited to private queues only. With the domain mode in place (which requires Active Directory Integration component and domain membership), it is possible to form public queues as well as leverage enhanced security. Active Directory is also required in order to implement message routing, which provides the ability to automatically determine the least costly path across the message queuing infrastructure.

To install Message Queuing, start the Add/Remove Windows Components wizard from the Add or Remove Programs Control Panel applet. Depending on the operating system, you will be able to find the Message Queuing either in the list of main components on the first page of the wizard (Windows 2000 Workstation and Server or Windows XP Professional) or within subcomponents of Application Server (Windows 2003 Server). In both cases, default setup consists of Common (which implements the most basic messaging functionality and manages message store) and Active Directory Integration (which, as we explained earlier, provides the ability to create public queues and publish them in Active Directory) items. Other components, present on both platforms include Triggers (allowing you to associate messages arriving to a specific queue with invocation of an arbitrary action, such as calling a COM component or running an executable), selected by default on Windows XP Professional, and MSMQ HTTP support (handling transmission of messages over HTTP protocol). Windows 2003 Server also offers Downlevel Client Support (allowing access to queues from legacy operating systems) and Routing Support (leveraging Active Directory information to provide message routing functionality).

The installation results in the creation of Message Queuing service (and, depending on your selection, it might also include Message Queuing Triggers service). If you decide to take advantage of Active Directory Integration, its existence will be registered in Active Directory. This registration is represented by the msmq subnode within account of the computer hosting the service and is visible in the Active Directory Users and Computers utility (once you select the "Users, Groups, and Computers as containers" option in its View menu). From there you can test the installation status by sending MQPing messages as well as create new or administer existing public queues. In order to manage all queues (including private ones), launch Computer Management MMC snap-in on the local system and locate the Message Queuing entry under Services and Applications node. Its content includes Outgoing Queues (holding messages that are waiting to be sent to remote queues), Public Queues, Private Queues, System Queues (storing system generated messages), and Message Queuing Triggers. Within each, you will find several sub-nodes, such as Queue Messages (of primary interest to us, since that is where current messages awaiting reception are stored), Journal Messages (if the Journaling feature is enabled, copies of received and read messages are placed here), and Triggers (where you can register triggers).

With this rather lengthy introduction behind us, let's take a look at how we can take advantage of message queuing within SQL Server Integration Services. As you can imagine, its functionality might be very useful in situations when the execution sequence of two independently developed packages need to be synchronized (launch of one depends on the completion of another) or when the processing outcome from one needs to be communicated to another over an unreliable communication link (this applies to interaction with any MSMQ aware applications). The implementation is based on the Message Queue Control Flow task. Since the task requires MSMQ to be present on the same system on which packages are executed, start by performing its installation (following the procedure we described earlier), including Active Directory Integration component (assuming that you operate in the domain environment). Next, launch the Computer Management console, expand Message Queuing subnode under Services and Applications node, and create a new private queue (using the context sensitive menu of Private Queues subfolder) called SSISPrivate. Similarly, add a public queue SSISPublic under the Public Queues subfolder (it is also possible to accomplish this from the msmq subnode of your computer account node in Active Directory Users and Computers). Start the Business Intelligence Development Studio, create a new SSIS project, open its Designer interface, and drag Message Queue Task from the Toolbox onto the Control Flow area. Select Edit item from its context sensitive menu. You will be presented with the General section of the task editor interface. Set the Name to Private Send Message Queue. Leave Use2000Format Boolean parameter (intended for communication with DTS packages that implement message queuing) with its default value of False. Create a new Message Queue Connection Manager via the MSMQConnection entry and assign .\Private$\SSISPrivate to its Path parameter. Note that Message entry, indicating whether the task will be used to send or receive messages impacts the appearance of the second section of the Task Editor (which toggles between Send or Receive, depending on your choice). Leave it at its default (Send) and click on the second section to display its content. From here, you can specify whether messages should be encrypted (with UseEncryption Boolean parameter and EncryptionAlgorithm allowing you to choose between RC2 and RC4) as well as define MessageType, which takes the form of a Data file, String, or Variable. Choose the String message option and type in "Say hello to my little friend." Close the Editor window and follow the same process to add another Message Queue Task named Public Send Message Queue with its own Connection Manager and Path set to .\SSISPublic, configured to send the string message "You talkin' to me?" to it.

Now it is time to create another package that will serve as recipient of these two messages. Its general structure is the same as the first one, with two Message Queue tasks pointing to the same private and public queues and identically configured connection managers. The main difference is the Receive message value of Message entry in the General section of both task editors, which, as we mentioned earlier, changes the second section to Receive. Using its options, you can control whether messages are removed from the queue once they are received (with Boolean RemoveFromMessageQueue parameter), set the Timeout (with Boolean ErrorIfMessageTimeout and integer TimeoutAfter parameters), and specify types of messages to process, which include the following:

  • Data file message - indicates that reception of a data file is expected. You can point to its sender by turning on the Filter parameter and specifying package name with Identifier parameters. Using SaveFileAs and Overwrite parameters, you can save the file to a different location.
  • Variable message - relies on a message being stored in a package variable, which name you need to provide. You have an option of pointing to a specific sender package (with values of Filter and Identifier parameters).
  • String message - locates anticipated message in the queue based on string comparison criteria you select (None, Exact match, ignore case, or Containing) against the value of Compare String parameter. This choice can be used in our case. For example, you might set the task that monitors the private queue to wait for a message containing the string "little friend" and the one overseeing the public queue to look for "talkin'."
  • String message to variable - similar to the previous option, but also allows you to save the content of the string to a variable.

As expected, regardless of the sequence in which you launch our two sample packages, the second one always waits for the first one to complete and executes immediately afterwards (providing that you did not enable the Timeout parameter).

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM