The “EAI MSMQ Transport” Business Service

Inspire by the “reloaded” Siebel Hub Business Service Library, I felt motivated to write about the “EAI MSMQ Transport” business service. We use MSMQ extensively in my current project and I’m impressed with the simplicity and power of the technology. The JMS equivalent (“EAI JMS Transport”) has it’s own very nice article from @lex and works in much the same way as the MSMQ flavour. What follows is a short summary of some of the key features of the MSMQ technology, used alongside the various EAI functions provided by our favourite enterprise software.

The premise is relatively simple: Microsoft Message Queuing (hereafter refered to as MSMQ) allows systems and processes to communicate in a “fail safe” manner. That is, if the connection between systems were to break or disappear, message communication resumes when the connection is re-established. This is in contrast to socket or point to point communication mechanisms which require an “always on” connection.

Creating an MSMQ queue

Setting up MSMQ is simple, provided “Message Queueing” has been enabled on the Windows host. This is typically enabled via Server Manager on Windows Server hosts and through Windows Features on Windows Client hosts, such as Windows 7.

Creating a queue is equally as straight forward and can be done using PowerShell or via the UI. Via the UI, the process is easy:

  1. Right click ‘My Computer’ and select ‘Manage’
  2. Under ‘Services and Applications’ select ‘Message Queueing’
  3. Expand ‘Private Queues’ to see a list of queues enabled on that machine
  4. Right click ‘Private Queues’ and select ‘New > Private Queue’
  5. Give the queue a name and check the ‘transactional’ checkbox
    • A transactional queue allows blocks of messages to be treated as a single, atomic unit. If MSMQ fails to process that entire block, all messages that have been processed are rolled back
  6. Click ‘OK’ to create the queue

CreateQueue

MSMQ introduces the concept of Private, Public, Outgoing and System queues. We use Private queues in order to strictly control which service and administrative users have access to the queues. You can read more about the other queue types on MSDN.

Sending to an MSMQ

Sending XML to an MSMQ is simple, using the “EAI MSMQ Transport” business service. Two methods should peak your interest:

  • Send – this method will send a message to a specified queue. The method is “fire and forget” and Siebel will not be informed of the fate of the message
  • SendReceive – this method will send a message to a specified queue and await a response message. This allows for end to end processing, where the recipient sends either a confirmation of receipt or some sort of response or feedback from a process initiated by the outound message.

Method arguments that you’ll typically use are:

  • <Value> this should be initialised with the XML or text that you want to post to the queue
  • MsmqPhysicalQueueNamethe name of the queue to post to. This should be an MSMQ path such as “private$\mytestqueue”
  • MsmqQueueMachineName – the host name of the machine that contains the target queue. This can be localhost if you’re running everything locally

Using this Business Service method in a Workflow Process is usually combined with calls to the following EAI Business Services:

  • EAI Siebel Adapter.Query – this method creates a Integration Object instance from data in your Siebel Database.
  • EAI TransformationEngine.Execute – this BS method uses a Siebel Data Map to transform an IO instance into an external IO instance with a different schema
  • EAI XML Converter. IntObjHierToXMLDoc – this method tranforms the IO instance into an XML string, ready for transmission to MSMQ

An MSMQ outbound Workflow Process may look something like this:

MSMQSendWorkflow

Running the Workflow, with an appropriate “Object Id” of course, will result in an XML message containing your EAI query results on the queue. You can see it by going back to your MSMQ machine, expand your queue name and click on “Queue Messages”:

NewMessage

You could also invest a few pennies in the MSMQ Explore tool, available exclusively from Ollerenshaw IT Ltd!

Receiving from an MSMQ

Receiving a message is a little more involved, especially if you want Siebel to monito a queue and automatically process messages from it. Three additional elements are required to configure Siebel to systematically process messages from an MSMQ queue. There elements are:

  1. An “MSMQSubsys” profile configuration – this profile details the name and host of the MSMQ queue. The following parameters should be set:
    • MsmqPhysicalQueueName – the name of the queue to post to. This should be an MSMQ path such as “private$\mytestqueue”
    • MsmqQueueMachineName – the host name of the machine that contains the target queue. This can be localhost if you’re running everything locally
  2. A “EAITransportDataHandlingSubsys” profile configureation – this profile configuration specifies what Siebel should do with a message when it is received. Typical scenarios would invoke either a Workflow Process or a Business Service. The following parameters should be set:
    • DispatchWorkflowProcess – <Name of the Workflow process to invoke>
    • DispatchService – Workflow Process Manager
    • DispatchMethod – RunProcess
  3. A  “Enterprise Application Integration Receiver” Component Definition – this component effectively references the two profiles to “poll” the MSMQ and pass received message contents onto the handling Workflow or Business Service. Set the following parameters as a minimum:
    • ReceiverConnectionSubsystem – the name of the MSMQ Queue profile
    • ReceiverDataHandling Subsyst – the name of the Handler profile
    • ReceiverServiceName – EAI MSMQ Transport
    • ReceiverMethodName – ReceiveDispatch
    • Default Tasks – set this to 1 to ensure the component comes up and is listening from when the Siebel Server starts up

A Workflow Process, who’s name you’ll reference in the handler profile above, will look remarkabely similar to the Workflow you created for sending messages, using different methods of the same Business Services:

  • EAI XML Converter. XMLDocToIntObjHier – this method tranforms the XML message back to an Integration Object instance
  • EAI TransformationEngine.Execute – this BS method uses a Siebel Data Map to transform the external IO instance into an internal IO instance with a different schema
  • EAI Siebel Adapter.Upsert – this method applies your IO instance to the business layer, updating and creating data

A receiving Workflow Process may look something like this:

MSMQReceiveWorkflow

Using listeners and handlers is a mechanism to consistenly poll queues for messages, acting upon them as soon as they are received. It is possible to “manually” receive a message from the queue using one of the three “Receive” methods:

  1. Receive – simply receives / pops the next message from the queue and places it into a property
  2. ReceiveDispatch – receives the next message from the queue and dispatches it to a handling service
  3. ReceiveDispatchSend – receives the next message from the queue, dispatches it to a handling service then sends a response message

MSMQ, and other queuing technologies such as JMS, are a simple and elegant mechanism for providing failsafe, transactional communication between systems. The Siebel EAI business service family provide everything you need, and more, to extract, manipulate and manage structured message data using industry standards. Combined, you have a very easy to use mechanism for Siebel to communicate with the outside world.

 

InfoPath Integration And Base64 Decoder

I’ve been working on some functionality within Siebel to “integrate” Microsoft InfoPath. The premise of the requirement is to export an instance of an entity, Account for example, using Siebel EAI and allow a 3rd party, non-Siebel user to view the data in a convenient and useful manner.

InfoPath allows you to define templates based on an XSD. This template can be modified to present an XML instance, using this XSD, in a friendly and readable manner. Perfect for my requirements! However, there’s a snag – isn’t there always? The problem is with File Attachments.
InfoPath_BS

Siebel EAI is really helpful when it comes to extracting file attachments in XML – it will base64 encode the binary file and stick it in a node in the XML. InfoPath does the same thing with file attachments, but it expects a 24 byte header within the base64 encoded file string. This is not something that Siebel does out of the box.

So I wrote a business service that creates that InfoPath header and presents a base64 encoded string that InfoPath is happy with. This business service is invoked in a Data Map that is used to extract the data to my InfoPath XSD.

The solution to building this byte stream header, and encoding everything in a way InfoPath expects, used some eScript functions that I haven’t used before. Specifically, String.fromCharCode() came in really handy as a way of building up an arbitrary byte stream.

For example, InfoPath has a fixed, 4 byte identifier as part of the header. This can easily be created using:

The same principal can be used to create the header size, version and reserved blocks – they are all 4 bytes long. The file size, name and terminator can be constructed using similar principals, using data supplied via EAI in the XML.

The final element of the solution is to decode the Siebel encoded string, append the header, then re-encode. The result is a Base64 encoded string that InfoPath will be happy with.

Here’s the code I used to decode the Siebel encoded attachment string. I pinched the basis for this from Stack Overflow. Thanks to Sniper and broc.sieb for these snippets. Feel free to use this in your own projects, with the usual caveats of course!

That’s it for another post – until next time!

Siebel Web Services – XML Attributes

I was building an External Web Service the other day, when I noticed that the message format exposed by the external application relied, rather heavily, on XML attributes. For example:

I wanted to use the Declarative UI Data Mapper to bring this information into my Contact BC model and had immediate concerns about the use of attributes.

I needn’t have worried, as the WSDL import process does something rather clever with the Integration Object for the external message structure.

ICFields

What I found was that the import wizard created IC fields for each of my attributes:

The “type” and “lineNumber” IC fields have an “XML Style” property of “attribute”, allowing the EAI mapping engine to map between the IO instance and resulting XML message without losing the context of the data.

I was also then able to use the EAI Data Mapper, by means of a Source Component Search Spec, to retrieve the “first” and “last” names for use in my internal IO instance.

I love the integration capabilities of Siebel and have yet to find a requirement that cannot be met by declarative means.

Loading XML via EIM

We recently came across a requirement to consume large volumes of data from a system that published a text feed of individually formatted XML “records”. It’s an odd integration mechanism, involving a text based feed of header blocks and individual XML:

There could be in excess of 10 million records published in this way – crazy, I know, but in the Enterprise world you do come across some legacy mechanisms that were built for some purpose or other, often long forgotten.

Anyway, my goal was to consume this data en mass into Siebel.

Looking at the format of what we’re consuming, my first thought was how to get at the structured data. Header lines in text files fill me with dread – as does the use of arbitrary delimiters to separate records. XML is a wonderfully descriptive language, ideally suited to describe payloads and define relationships. A quick Perl script later and I have a list of records in a format that I’m comfortable with.

Working with XML? Got to be EAI, right? Well, not when you’re dealing with millions of records it isn’t. We all know that EIM is the mechanism of choice for bulk loading data, but how do we get from structured XML to EIM staging tables? The answer that I found lies in Microsoft’s Integration Services technology.

SQL Server Integration Services (SSIS) is an integration technology that allows you to extract, transform and load from one data format to another. It used to be called Data Transformation Services (DTS), if you’re old enough to remember. As it is, SSIS allows the specification of an XML file, backed up by an XSD, as input; we can then define either an Oracle schema as output (mapping directly to EIM, if you want to get straight to the point) or to automatically generated MS SQL Server staging tables. I went for the latter, as it gives you an ability to do some transformation and / or reconciliation before loading in to EIM.

Creating a process flow to extract from XML to a structures table schema is surprisingly straightforward. Here’s how I achieved it using MS SQL Server 2014:

  1. Create a new Execute SQL task in your Control Flow. Call this “Truncate Tables” and leave it empty for now
  2. Create a new Data Flow object in the Control Flow. Name this “Load Staging from XML” and double click for more detail
    ControlFlow
  3. Drag an XML source into the flow
  4. Double click this and select an XSD to determine the data types, size and cardinality for the input source. SSIS will ensure that data flowing through the component is validated against the XSD.
  5. Select an XML file for input
  6. Now drag an OLE DB Destination component into the flow. Left click and give it a sensible name, based on the XML node data that it will consume
  7. Drag an output from the XML input into the OLE DB Destination – this will conveniently inform the component of the XSD schema for the input data. Double click, click “New” for the “Name of table or the view”
  8. SISS will automatically create a table schema based on the XSD that is being consumed – great stuff:
    Map
  9. Click the “Mappings” item and SISS will automatically map the XSD schema to your new table
  10. Repeat the above for all node levels of your XSD:DataFlow
  11. Now revisit your Control flow and add some SQL to truncate the tables created as part of your mapping

The beauty of this process is that SISS will maintain the referential integrity of your XML as it maps it out to the table schema. So, if you’ve got “ParentNode” with one or more “ChildNode” records, the process will generate a primary key for the parent and use this as a foreign key in the child table, via a column called “ParentNodeId”. This can then be used when mapping your staging tables out to EIM.

SISS is an excellent ETL tool, if you’re licensed to use it. Mapping large XML data sources out to a structured schema is the first step in getting data into EIM – you can even bypass the staging altogether and map XML directly.

Do you have experience with other ETL tools? Post below and share your experience!