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. DBJournalServerPrivate$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).
