Working with Message Queuing Task with SQL Server 2008 Integration Services

Overview

Every integration project template mainly has various packages. Each package is a unit of work, execution and deployment. Each package has only one control task which will be the logic or the work flow for the package. One of the tasks from these control flow tasks is Message Queue Task.

Overview of Message Queue (MSMQ)

Suppose there are 2 applications which want to share data with each other within a network. One application sends the data and the other application takes an action based on the data received. When both the applications are simultaneously running there is no problem with the communication. But if the sending application is running and the receiving application is not, then there is a problem. We need to have some temporary storage which keeps the data till the other application is in the state of receiving. This in between storage is in the form of a server which works in first in first out (FIFO) manner, transactional and only once delivery. This server is called as Message Queuing or MSMQ. So in order to have asynchronous communication across the applications we use MSMQ.

With MSMQ there are two types of queues Private and Public. As the name suggests the Private Queue keeps information on local system and can be accessed by path name which requires Computer name with Private$ and the queue name all separated by \. The Public Queue on the other hand is registered in the Active directory. These are available from other domains.

To install Message Queuing start Add/ Remove Programs and select Add/Remove Windows Components from Control Panel. Select Application Server, Click on Details, Check Message Queuing and Select Details

With Message Queuing Task with SSIS we communicate with MSMQ for sending and receiving messages. These messages can be between two SSIS packages or to any other application queue which can later be processed by a custom application.

We can use the Message Queuing Task for various purposes

1. Some activities can be kept pending till other packages can contribute. Example if there are update packages running on different machines, each of them can send a message to the central computer. This central computer takes action when messages from all different packages have been received.
2. A package can send data as well as a complete package as a part of message.

Check this note at http://msdn.microsoft.com/en-us/library/ms141227.aspx

The Message Queue task fails to comply with Federal Information Processing Standard (FIPS) 140-2 when the computer's operating system is configured in FIPS mode and the task uses encryption. If the Message Queue task does not use encryption, the task can run successfully.

The following types of messages can be created and sent with Message Queuing task

• Data File Message
  • This specifies that a file contains the message.
• Variable Message
  • This specifies that message has one or more variables. You will have to provide the name(s).
• String Message
  • This specifies the message is in string

Creation of Message Queuing Task

Let us see different steps of creating and configuring the Message Queuing Tasks

1. In order to use Message Queuing Task we need MSMQ installed on the box where package is being created and executed, we need to install it. Follow the instruction given earlier.
2. For this package we will be working with a private queue with the name DemoQueue. Open Computer Management by Programs – Administrative Tools. Open Service and Applications, Open Message Queuing, Right Click on Private Queue and enter name DemoQueue This queue can now be referred by <machine name>\Private$\DemoQueue. The machine name can be replaced by dot (.).
3. Create a package in SQL Server Integration Services Template and name it SendMessage. Drag and drop Meqqage Queuing task on the control flow.
4. Change the name to DemoMsgToPrivate and open Edit Menu
5. You will see the General Editor for the task
6. Create a new MSMQConnection manager (name it MessageQueueToPrivate) to the private queue we created earlier by entering the path as <machine name>\Private$\DemoQueue or dot (.) in place of machine name. Click on Test Connection and verify the connection is successful.
7. We will be using this task to send the massage. With this you can specify whether you want to encrypt message or not (which can have algorithm as RC2 or RC4 for encryption). You can also select the type of the message. We will keep the string message type and enter StringMessage as “Hello from Private Queue with SSIS”
8. Now that one package is ready let us create another package which will have the task of receiving the message. Create the package in another template.
9. This new package will have similar task with the change that this task will act as message receiver.
10. In order to receive message, change Message Property from General tab to Receive Message which changes the Send tab to receive.
11. For String Messages we can compare 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. We can use this choice. For example, you might set the task that monitors the private queue to wait for a message containing the string "with SSIS”.
12. We can also specify whether to remove the message from the queue by setting RemoveFromMessageQueue to true or false
13. Execute both the package. Execute the send package first and later the receiver package. Verify the message is reached the queue after executing sender package and later it has been removed from the queue by viewing Private queue.
14. The receiver package will wait till the sender package completes its task and executed immediately later. This is with the assumption that you have not enabled timeout parameter.
15. You can also use Send Mail Task and send the message received to e-mail address by first storing the MessageString in a variable (select Message type as String Message to Variable) and associating the same variable as Message Source.

Summary

With this article we briefly discussed first the concept of Message Queuing. We saw how a Message can be sent and received by packages in SSIS. For achieving this task, we used Message Queuing Tasks.

Read more on Message Queuing over here - http://msdn.microsoft.com/en-us/library/ms711472(VS.85).aspx