SQL Server 2008 R2 Integration Services offer a number of built-in features that simplify implementation of data extraction, transformation and loading (ETL) scenarios in order to bring them within reach of database administrators lacking extensive programming experience. Unfortunately, simplicity of these prepackaged components frequently comes at the cost of limited functionality. This becomes apparent, for example, when attempting to facilitate interaction between SSIS packages and private Microsoft Messaging queues residing on remote systems. While this capability is missing from the Message Queue Control Flow task, it is possible and relatively straightforward to remediate this shortcoming by taking advantage of existing .NET Framework classes using code hosted by a Script Task. In this article, we will demonstrate such an approach.
Microsoft Message Queuing functionality is incorporated into .NET System.Messaging namespace classes (if you are interested in comprehensive overview of its characteristics, refer to the relevant section of MSDN Web site). Our presentation will focus on demonstrating how their properties and methods can be used to emulate the feature set of the Message Queue Task, effectively allowing you to overcome its deficiencies.
In particular, we will provide a code that implements sending and receiving messages to arbitrary queues, including private ones hosted on a remote system. In the simplest form, this is handled using Send and Receive methods of the MessageQueue class, which also gives you ability to read messages without removing them from their queue with the Peek method. There is also an auxiliary mechanism involving manipulating Message class instances, yielding considerably greater degree of control over the way messages are transmitted (including, for example, their formatting, encryption levels, or time-out behavior), which we will employ when retrieving queued messages. In addition, rather than relying on pre-created Message Queue Connection Managers, as we have done when discussing Message Queue Task, we will forgo this dependency by referencing a target queue (in the Format Name notation, which takes the form FORMATNAME_DIRECT=OS:computernameprivate$queuename) directly in the code.
To explore these techniques, launch Business Intelligence Development Studio and create a new project based on the Integration Services template. Drag the Script Task icon from Toolbox and drop it on Designer interface. With the newly generated task highlighted, open the Variables window, define a variable named sMessage of String data type and Script Task scope, and assign to it an arbitrary string (this will become the content of our message). Use context-sensitive menu of the Script Task to display its Editor dialog box. Designate Visual Basic .NET 2008 as the ScriptLanguage and add User::sMessage to ReadOnlyVariables textbox. Click on Edit Script… command button to access the Visual Studio Tools for Applications 2.0 interface.
Since, as we already pointed out, the required functionality is incorporated into .NET Framework System.Messaging namespace classes, you need to add a reference to it into the Visual Studio Tools for Applications project that contains your Script Task code. To accomplish this, activate Add References option from the Project menu. In the resulting dialog box, select System.Messaging entry on the .NET tab and click on OK command button (you can verify the outcome by turning on Show All Files view in Project Explorer window via the corresponding toolbar icon and reviewing the References node). In case you encounter an error stating No template information found, see the application log in Event Viewer for more detail. Execute DevEnv.exe /InstallVSTemplates from the command prompt while in the Program FilesMicrosoft Visual Studio 9.0Common7IDE directory. To simplify coding, add the Imports System.Messaging statement at the top section of the code and populate the body of Public Sub Main() with the following:
Public Sub Main() Const QUEUE_NAME As String = "FORMATNAME:DIRECT=OS:remoteprivate$q" Try Dim sMessage As String = DirectCast(Dts.Variables("sMessage").Value, String) Dim privQueue As New MessageQueue(QUEUE_NAME) privQueue.Send(sMessage) Dts.TaskResult = ScriptResults.Success Catch ex As Exception MessageBox.Show(ex.Message.ToString, "Error") Dts.TaskResult = ScriptResults.Failure End Try End Sub
As you can see, we start by defining a constant that contains the Format Name notation of the target queue (which we call simply “q”) on a remote computer (bearing the equally unimaginative name “remote”). Note that, as we mentioned earlier, it is possible to reference a queue defined using Message Queue Connection Manager (in which case we would be able to instantiate privQueue by executing privQueue = DirectCast(Dts.Connections(“Message Queue Connection Manager”).AcquireConnection(Dts.Transaction), MessageQueue) statement). The message is extracted from the SSIS variable sMessage and sent by invoking Send method of our instance of MessageQueue class (you can assign a label to the message arriving in the queue by adding the second string parameter to the overloaded Send method). To verify its delivery, examine Message Queuing node in Server Explorer on the remote computer. Note that sending a more complex content (such as a data set), would be just as straightforward, since, by default, .NET Framework takes care of serializing it using XML format (for information regarding other formatting options, refer to the MSDN article on Message Serialization
Now let’s review the procedure of retrieving a message from the target queue. To test it, create another package containing a Script Task (or simply an additional Script Task within the existing package) and configure it in the same way as the first one. Just as before, to simplify coding, add the Imports System.Messaging statement at the top section of the code and populate the body of Public Sub Main() with the following:
Public Sub Main() Const QUEUE_NAME As String = "FORMATNAME:DIRECT=OS:remoteprivate$q" Dim oMessage As Message Dim targetTypes(0) As Type Try Dim privQueue As New MessageQueue(QUEUE_NAME) oMessage = privQueue.Receive() targetTypes(0) = GetType(String) oMessage.Formatter = New XmlMessageFormatter(targetTypes) MessageBox.Show(oMessage.Body.ToString) Dts.TaskResult = ScriptResults.Success Catch ex As Exception MessageBox.Show(ex.Message.ToString, "Error") Dts.TaskResult = ScriptResults.Failure End Try End Sub
In this case, we also define target queue using the Format Name notation. Note, however, that we store its extracted content in a variable of Message class and instantiate an XmlMessageFormatter object to comply with the requirement that the formatting methods used to receive and send messages are the same. We also utilize a variable of Type class to ensure that our code accepts appropriate data type (also matching the sent message). Finally, we use a message box to display its content.