Microsoft MVP in BizTalk Server

Everyone who becomes MVP for the first time has blogged about it for sharing his thoughts and commitment to the community. This is the first time I am honoured by Microsoft with the MVP award for my contributions to the community via my blog and BizTalk Server forums. I will be working more deeply with the technology and with the fellow BizTalk server MVPs.

Its a great feeling and pleasure to be an MVP and getting recognized for serving the community.  I will strive to continue my support and commitment to the BizTalk community via my blog. This year I plan to start a user group here in UAE and have a few webcasts related to BizTalk and SOA.

I thank Microsft, fellow MVPs and my peers for the award.

Enterprise integration pattern part -5- Aggregator (Sequential Convoy)

In integration it is a very common task to receive different messages from different locations process them individually and produce a result in the form of a single consolidated message. This task is performed by an aggregator. In the aggregator a Singleton orchestration with sequential or parallel convoys is implemented. There are variations in the collection of input messages i.e. it can be a parallel convoy or a sequential convoy with different or same message types.

You can read more about Aggregator Enterprise integration pattern here.

While implementing the aggregator orchestration you need to ensure that the single instance of the orchestration receives the convoy of messages. There can be two types of convoys.

Sequential Convoy

In sequential convoy all the types of messages are received in an ordered sequence. The first receive shape initializes the correlation set while the other receive shapes follow the correlation set. You can have the first receive shape’s activate property to true and set the initialize correlation property. While the other receive shapes will have the follow correlation set property set and activate property false.

Parallel Convoy

In the parallel convoy the messages arriving are not in a sequence and their sequence does not matter but the processing should be started when all the three messages are received. Configuring the parallel convoy is straight forward. Just drag the parallel shape and add the receive shapes in the parallel shape branches. If the parallel shape is the first shape in the orchestration then each parallel receive shape must have activate property to true and initialize the correlation set otherwise follow the correlation set initialized by any first receive shape or send shape.

In my example I have used a sequential convoy and used a transform shape to aggregate all the three messages into a single message and then send this message through the file port.

You can download the example from here.

Enterprise Integration Patterns Part-3 – Resequencer

At times ordered delivery of multiple messages are required and these messages are received in a non-sequential order. This problem is solved by the resequencer enterprise integration pattern. The resequencer integration pattern implies the following.

All the incoming messages must have the BatchID, their sequence and the last message of the sequence must be determined.

All the incoming messages are stored in a correct order in a temporary cache and when all of the messages are received the messages are sent in order.

You can read more about resequencer pattern here.

In the example shown the incoming messages have a BatchID which works as a correlation set. The property is promoted to the message context and the correlation type is configured. The correlation is initialized by the first receive shape and the orchestration is started when the first message arrives. The message is saved in the SortedList of type System.Xml.XmlDocument so that we can save the message as a key value pair.

The sequence of the message is determined from the message payload and each message has the field for its sequence. To receive all the messages a listen shape with a receive which follows the correlation based on the batchID is placed and on the other branch a delay of 20 secs is configured. The loop terminates when all the messages in the message stream are received. Each message has a field of TotalMsgs which determines the total messages. A loop variable is incremented when each message arrives.

In the next loop all of the messages are returned to the send port in the right sequence.

Note:
Resequencing pattern must have a cache where the messages should be stored temporarily and when all the messages are received they must be sorted and sent in a sequence. However in this simple example we have used memory for storing messages whereas this approach is not good if the message size are very large. Another approach to store messages would be in a Database or on the Disk as a file. Storing on disk would involve efforts to retreive the files again. However storing in a database would also require connection to database, involving SQLServer/Oracle Ports and handling failure exceptions. I haven’t still found the best approach to cache messages, though I think storing the messages in the database would be best considering the performance factor.

You can download the sample here.

Enterprise Integration Patterns Part-2 – Recipient List

In the recipient list enterprise pattern a message is sent to multiple recipients in the list through different channels. The recipient list inspects the incoming message determines the list of recipients based on the incoming message and forwards the message to all the recipients through different channels. You can read more about recipient list pattern here.

Determining Recipient List

From the impletation point of view in the first round the incoming message is inspected and then depending on the data the recipient list is determined. For this we create a parent orchestration who receives an order message and if the order amount is greater that 10,000 it forwards the message to its different suppliers for quote otherwise buys it from its favourite supplier. I have also applied the filter for recipients in the child orchestration. In the parent orchestration we have a variable amount which is passed as a parameter to the child orchestration. This variable determines the recipient of the message. The value of this variable is extracted from the incoming message.

Forwarding the message to recipients through different  channels

In the first part we use the parent orchestration in which a loop is started to call the child orchestration asynchronously using a Start Orchestration shape. A self-correlating port, the request message and the filter variable is passed to the child orchestration as a parameter. You can read more about self correlating ports in my previous post about Scatter and Gather Enterprise pattern. In the second iteration all the response messages are collected back using the self correlating port. Remember to set the child orchestration long-running scope timeout so that if the orchestration fails to return the message the parent orchestration does not waits for it. Then the response messages can be processed and a response can be returned back from the orchestration.

You can download the sample from here.

Verifying Schemas after deployment

After a developer builds and deploys the BizTalk application one verifies that the assembly of the application is present in the GAC. Sometimes when we test our solution we often get these errors below.

There was an error executing receive pipeline: Finding the document specification by
message type [MessageType] failed.Verify the schema deployed properly.

Or

There was a failure executing receive pipeline: Cannot locate document specification
because multiple schemas matched this message type [MessageType]

This error occurs when the BizTalk runtime cannot find the schema which conforms to the type of the message. To BizTalk the type of message is a combination of target namespace of the schema and the root node separated by pound (#) sign. for e.g. http://mycompany.schema#orders.

The BizTalkMgmtDb is the database where the application information is saved. The error mentioned above means that the schema is not deployed in BizTalk. The records of all the schemas that are deployed to BizTalk are in the bt_DocumentSpec table.  To check that the schema exists and is deployed properly open the query analyser on your BizTalk Server and use the BizTalkMgmtDb and run the query below. In the where clause place the correct target namespace and root node name which is being reported in the error log from the Event Viewer.

Select msgtype, assemblyid, clr_namespace, clr_assemblyname
from bt_documentspec
where msgtype = ‘http://mycompany.schema#orders’

If the record is present this verifies that the schema is deployed properly to BizTalk, if not the project containing the schema should be redeployed.

Recommended Platform Configuration for BizTalk Server

In order to ensure that the live environment is secure, stable, performing well and is highly available following are the recommendations from Microsoft which you need to consider.
1) Operating System Service Pack and Critical Updates.
To ensure that all the live environment operating systems for BizTalk and SQL Server has the latest service packs and has latest security updates. The Operating Systems must be checked weekly for latest updates in order to keep the OS environment healthy and secure. The MBSA (Microsoft Baseline Security Analyzer) tool can identify that if your system is outdated and needs to be updated. Following links would be helpful

Windows Service Pack Road Map
Microsoft Baseline Security Analyzer (MBSA)
Microsoft Support Lifecycle | Service Pack Support
Microsoft TechNet Security Center

2) COM and DTC updates
BizTalk uses COM and Microsoft Distributed Transaction Coordinator (MSDTC) and COM+ for its operations both internally and when interacting with external systems. So to benefit from these you should have their latest updates from here

3) General Network Health
To ensure that your network connectivity is good between the SQL Servers and BizTalk Servers Run the Pathping <SQL Sever Name> command on the command prompt on the BizTalk Servers. Also Find a 100 MB File and copy it on a shared Directory on each of the SQL servers from each BizTalk Servers. The result should be 0% packet loss and the file should be copied within 5 secs on a 1 Gigabit network.

PathPing Reference
How to troubleshoot network connectivity problems
How to troubleshoot TCP/IP connectivity with Windows XP

4) Avoiding TCP/IP Port Exhaustion
When the range of available TCP ports on the Windows operating systems get exhausted the Client applications or BizTalk may not be able to connect with with via TCP and the following symptoms may be observed:

• Client applications may fail to connect to the BizTalk Server.
• The BizTalk Application service may fail to connect to a remote SQL Server.
• BizTalk Server adapters may fail to connect to a remote server.
• Each port reservation that is made by a client application consumes kernel memory. If an unusually high number of client port reservations are made then Windows kernel memory use will increase accordingly.

To verify that one or more client applications are not generating excessive TCP/IP socket connections perform the following on the BizTalk servers in the group:

• From a command prompt window execute “netstat -ano -p tcp”.
• Count the number of unique Local Address TCP ports open above 1024 for each IP address.

If a large number of client applications initiate the expected number of TCP/IP socket connections but there are not enough available short-lived ports to satisfy the connection requests, then implement one or more of the registry modifications go to Avoiding TCP/IP Port Exhaustion.

If less than 3000 ephemeral ports are currently in use then its fine.

5) Avoiding DBNETLIB Exceptions
Most often the DBNetLib error is when the MessageBox database servers are busy and the biztalk runtime cycles every minute to check the database for availability. Whenever the runtime attempts to connect to the messagebox databases fails the exception below can be found in the event log.

Event Type: Warning
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5410
Computer: BIZTALKSERVER
Description:
An error occurred that requires the BizTalk service to terminate. The most common causes are the following: 1) An unexpected out of memory error. OR 2) An inability to connect or a loss of connectivity to one of the BizTalk databases. The service will shutdown and auto-restart in 1 minute. If the problematic database remains unavailable, this cycle will repeat.
Error message: [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation.
Error source:
BizTalk host name: BizTalkHost
Windows service name: BTSSvc$BizTalkHost

To avoid DBNETLIB Exceptions

6) Time Synchronization
For the servers in the BizTalk group to operate correctly with their supporting SQL servers and with external systems it is important that the system time for all servers are kept synchronized with the domain. On each of the Biztalk servers in the group and SQL servers hosting the BizTalk databases, open a command prompt window and type the following command:

w32tm /stripchart /computer:<domain controller>

Where <domain controller> is the name of one of the domain controllers for the domain.

If a discrepancy is detected then clocks can be resynchronized by running the following command at a command prompt:

w32tm /resync /computer:<domain controller>

Where <domain controller> is the name of one of the domain controllers for the domain.

Windows Time Service Tools and Settings

7) NetBIOS over TCP/IP
If the BizTalk servers reside in a vulnerable network location then check to ensure any internet/public facing network adapters have NetBIOS over TCP/IP disabled. It should be disabled and should not be exposed to the public as it could be a high security risk.

Open Control Panel, Network Connections, and locate the network adapters which are internet facing. Open the properties of the network adapter(s), open the properties of TCP/IP, and on the General tab click “Advanced…”. Click the WINS tab and review the NetBIOS settings.

technorati tags : BizTalk

Microsoft announces BizTalk Server 2009

After the BizTalk Server 2006 R2 Microsoft announces BizTalk Server 2009 which will serve enterprises to build integration solutions with a more powerful Microsoft suite which is the main attraction for me to get my hands dirty with BizTalk Server 2009.
The main features which I found useful on the BizTalk Server Roadmap page on Microsoft’s site are below.

Enhanced development platform:
It supports the Windows Server 2008, VS 2008, .NET Framwork 3.5 and SQL Server 2008 which I think is a major change and will have advantages of the new advanced platform. The main feature of Windows Server 2008 is the Hyper-V virtualization which can also be a standalone product to use virtualization on their costly gigantic machines. The BizTalk Server 2009 Hyper-V guide is available for download. The ESB Guidance 2.0 is also available at Microsoft site and the ESB Guide package is available at CodePlex ESB page. With VS 2008 we have new features for debugging artifacts such as Maps, Pipeline components and Orchestrations. With SQL Server 2008 Analysis Services we can get more functionality out of BAM.

Adapters:
Unlike the release of BizTalk Server R2, BizTalk 2009 comes with two new adapters Oracle E-Business Suites and SQL Server, while there has been an enhancement in the existing adapters which I still have to explore.

Other Enhancements:
On the MS Roadmap for BizTalk Server page on Microsoft’s website you can see enhancements in B2B integration, SOA and web services, Device Connectivity and of course the enhancement in message processing by adding recoverable interchange processing support in the disassembling and validation stages in pipeline. There are also new queries for message tracking in the BizTalk Management Console.

 For more information read MS Roadmap for BizTalk Server.

codeproject
technorati tags : BizTalk

Using Polling Statement and Executing Custom SQL using BizTalk Oracle Adapter

The BizTalk oracle adapter can be used to poll a table after specific intervals. The oracle adapter transport properties must be set to poll the specific table. Username, password and Service Name (The TNS alias used in the TNS file) should be configured along with the Bin Path of the Oracle Client installed. You can check whether all the properties are configured properly by clicking the ellipsis in the managing events property in the Oracle transport property. Select the SQLNative as receive property.

 

 
This Type of schema should be generated in the project by adding the oracle adapter metadata as shown in the section. A message of Type NativeSQL schema is returned after the execution of the polling statement query containing the data. Optionally a post polling statement query can be set that will run before the polling statement.

 

 
In the orchestration we have to create the type of Schema NativeSQL from the Oracle Adapter metadata.

Caution: It is better to keep the Oracle Schemas project separate because if another project uses the same service with NativeSQL schema a routing failure will occur.

 When you create the metadata from Visual studio, multi-part messages and port types will be created along with the orchestration by selecting NativeSQL service. If you are using a separate project for Oracle adapter metadata and your working project configure your port and multi-part messages accordingly.

 In this working example create a Request Message of type SQLEvent by configuring the message type property and referencing the message part from the Oracle Schemas project assembly. The response message returned will be of type “SQLEventResponse”.

  

 
Create a new receive only port and connect your receive shape of the request message you configured in the Orchestration.

 

  

 The response message however is not normalized according to your needs and contains two records “ColumnMetaData” and “RowMetaData”. The Column meta data contains names of the columns and rowdata contains data but with no expected column name as XML tag. Therefore you need an intelligent map to normalize it to make it more useful. I have seen a lot of tutorials on the internet and have never come across a single post or article that explains what this map will look like. I tried it with a table looping funcoid and indexing functoid but it won’t work.

 

At the end we are left with a simple choice of using XSLT for normalizing our response to our desired schema. Below is a simple XSLT inline script that maps the result from SQLEventResponse Message to Our IFX based schema Message. We have to use a scripting functoid and use Inline XSLT Call template script. The script is below which is self explanatory. Thefor-each loops that iterates over the rows and we map it onto our destination schema record.

 

//Name of the template

<xsl:template>

 

//For each loop that iterates over “Column data” which actually makes a record row

 

<xsl:for-each select=”/*[local-name()='SQLExecuteResponse' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='Return' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='rowData' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='columnData' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]”>

 

//Use a variable for indexing a record

<xsl:variable select=’position()’ />

 

//Log_Reference is a column in the destination schema

//The text highlighted in the script is the $index variable which returns the

//row being iterated and the column order is known so I hard coded it, alternatively //you can use another foreach loop and index for columns as well

 

  <LOG_REFERENCE><xsl:value-of select=”/*[local-name()='SQLExecuteResponse' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='Return' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='rowData' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='columnData' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘ and position() = $index]/*[local-name()='string' and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘  and position()=1]” /></LOG_REFERENCE>

 In this way the result is a normalized message other than which is returned from the Oracle Adapter.

Alternatively NativeSQL schema is used when we have a custom query that cross-references different tables and Oracle Adapter Metadata cannot be generated for such type of queries. You can create a message of type “SQLEvent” which will contain your custom SQL Query that can be a string created dynamically in your orchestration. To generate the XML change the Root reference property of your NativeSQL schema from Default to SQLEvent. Right click the schema and click generate instance. In the message assignment shape load the XML through the XMLDocument type variable in your orchestration and assign the XMLDocument variable to SQLEvent Type message variable.

 

 
The response will be of type SQLEventResponse which can be normalized to the procedure explained above.

codeproject
technorati tags : BizTalk

Executing Stored Procedure using BizTalk Oracle Adapter

Stored procedures from the Oracle adapter can only be called when they are within a package, this is due to the Oracle driver that does not provide the list of parameters. Visit Microsoft MSDN to read more about Oracle adapter calling the stored procedure.

Limitations of Oracle adapter calling a stored procedure wrapped in a package

For procedures, large-object (LOB) types are supported as IN parameters only. When they appear as INOUT, RETURN and OUT, the procedure is not displayed in the browser and therefore is not callable. LOBs are supported in tables in the Insert, Update, or Query methods.

In this article we will assume the package containing the procedure as stored procedures. In order to call a stored procedure wrapped up in a package generate the metadata of the stored procedure as shown below.

 
The schema containing all the types of the package, port type and multi-part message will be generated. In order to construct the request message select the root-reference property of the schema generated to the desired Request Message. Construct the message in a message assignment shape by loading the xml into an XMLDocument variable. The oracle adapter creates request, response and exception type messages and schema. Create a new solicit request-response port in the orchestration and select the exiting port created in the orchestration as below.

 

codeproject
technorati tags : BizTalk

How to Select, Insert, Update and Delete in BizTalk using Oracle Adapter

Select, Insert, Update and Delete statements can be executed using the BizTalk Oracle adapter by generating the Oracle Adapter metadata for a specific table. Right click the solution and click add generated items and click “Add adapter metadata”. A schema along with multi-part message types and port will be created along with an Orchestration file. For creating Oracle adapter metadata see Generating MetaData From Oracle Adapter.

Multi-part messages for Selecting (Query, QueryResponse), Update(Update, UpdateResponse), Insert(Insert, InsertResponse) and Delete(Remove, RemoveResponse) will be created with a message part named parameter for each message.

You can generate requests for each Request messages by changing the Root Reference property of the schema to the request message you want. Generate the instance and load the xml into the XMLDocument type variable with the SQL Statement in the message.

Bulk Insertion is an advantage using Oracle adapter and if you have several records to be inserted or updated you can create their messages and send it to the oracle adapter. In the response message the rows affected is returned.

For Select, Update and Delete you have to fill the Filter tag. For filters we supply the where clause just as in any other SQL Statement. For e.g. If we want to select particular records we give “columnname1 = value and columnname2 = value”.

• Select and Remove Type multi-part message only requires the filter parameter to be filled.

 

• For Update we supply the values of the columns that have to be changed in the request message along with the filter parameter.

 

 

• Insert type messages only require records to be inserted without filters.

 
Create a new port with solicit request-response type. Select the existing port type which was auto-generated with the metadata created.

 

codeproject
technorati tags : BizTalk