BizTalk Services Mapper - WCF SQL Select Query - Cumulative Concatenate

Say you want to create a map in order to produce the SELECT statement for a WCF-SQL query and you start of with a list of values to include in the where clause.

How to achieve this with the BizTalk Services mapper?

The Source XML:

<ns0:GetProducts xmlns:ns0="http://GetProductsBizTalkService.Schemas">
  <Column>
    <Name>Name_0</Name>
    <Value>Value_0</Value>
  </Column>
  <Column>
    <Name>Name_1</Name>
    <Value>Value_1</Value>
  </Column>
  <Column>
    <Name>Name_2</Name>
    <Value>Value_2</Value>
  </Column>
</ns0:GetProducts>

The Destination XML:

<ns3:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns3:Columns>*</ns3:Columns>
  <ns3:Query>where Name_0='Value_0' and Name_1='Value_1' and Name_2='Value_2'</ns3:Query>
</ns3:Select>

First, how could we do this within XSLT?

This can be done with the following XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:s0="http://GetProductsBizTalkService.Schemas"
                xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products"
                exclude-result-prefixes="msxsl s0">
  <xsl:output method="xml"
              indent="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates select="/s0:GetProducts" />
  </xsl:template>
  <xsl:template match="/s0:GetProducts">
    <ns0:Select>
      <ns0:Columns>*</ns0:Columns>
      <xsl:if test="Column">
        <ns0:Query>
          <xsl:for-each select="Column">
            <xsl:if test='position() = 1'>where </xsl:if>
            <xsl:value-of select="Name"/>='<xsl:value-of select="Value"/>'
            <xsl:if test='position() != last()'> and </xsl:if>
          </xsl:for-each>
        </ns0:Query>
      </xsl:if>
    </ns0:Select>
  </xsl:template>
</xsl:stylesheet>

The result of the above will be:

<?xml version="1.0" encoding="utf-8"?>
<ns0:Select xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns0:Columns>*</ns0:Columns>
  <ns0:Query>
    where Name_0='Value_0'
    and Name_1='Value_1'
    and Name_2='Value_2'
  </ns0:Query>
</ns0:Select>

Finally, how to achieve the same thing with the mapper:



What did we do?

First of all we need a list and the list will contain the list of expressions based on the column name and values:

Name_0='Value_0'
Name_1='Value_1'
Name_2='Value_2'

For this we use the new "Create List" functoid:

include the "ForEach Loop" functoid - in order to loop the "Column" records -:

use a "String Concatenate" in combination with the "Add Item to List" functoid to produce the above list:


Now that we have the list, how to produce the query statement? "Cumulative Concatenate" comes to the rescue:


Since the "Query" element in the destination schema is optional, I also included a "Conditional Assignment" functoid to only map the query in case we have columns on the input.
I also used the "Cumulative Count" functoid to count the number of items in the list and used the "Logical Expression" functoid to check if the value is greater than 0:


Based on that result we will create the "Query" element or not, but first we need to prefix the query with the "where" word using the "String Concatenate" functoid:


Simple!

The thing to remember here is to include a "ForEach" in the "Create List"...

Koen

Installing Terracotta as a windows service

Since the release of webMethods 9.0, Software AG uses TerraCotta as distributed cache for their cluster solution on Integration Server. The software can be easily installed using the SoftwareAG installer, but has a big drawback on Microsoft Windows systems: there is no procedure to install Terracotta as a Windows Service. Therefore, the server needs to be started on command line with all its consequences.

Fortunately, there are several solutions available:

Tanuki java wrapper

Can be configured to work with Terracotta, but the community edition only supports 32-bit JVM. Due to its memory limitation, this is not a very good solution. You can find the software at http://wrapper.tanukisoftware.com/

In case you want to buy the software for 64-bit support, configuration can be done as follows:

• ·        Download the wrapper and unzip in folder D:\SoftwareAG\Terracotta\wrapper 
•       Make a wrapper file TerraCotta.conf with the following configuration and place it under D:\SoftwareAG\Terracotta. Suppose the Terracotta config file tc-config.xml can be found under D:\SoftwareAG\Terracotta\bin

# for logging

wrapper.java.command.loglevel=INFO

wrapper.java.command=D:\SoftwareAG\jvm\jvm170_32\bin\java

# Method 1 main class

wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperStartStopApp

# classpath

wrapper.java.classpath.1=D:\Temp\wrapper\lib/wrapper.jar

wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar

wrapper.java.library.path.1=D:\Temp\wrapper/lib

# Java Additional Parameters

wrapper.java.additional.1=-server

wrapper.java.additional.2=-XX:+HeapDumpOnOutOfMemoryError

wrapper.java.additional.3=-Dcom.sun.management.jmxremote

wrapper.java.additional.4=-Dsun.rmi.dgc.server.gcInterval=31536000000

wrapper.java.additional.5=-Dnet.sf.ehcache.enableShutdownHook=false

wrapper.java.additional.6=-Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBFactory

wrapper.java.additional.7=-Dtc.install-root=D:/SoftwareAG/Terracotta

# Initial Java Heap Size (in MB)

wrapper.java.initmemory=512

# Maximum Java Heap Size (in MB)

wrapper.java.maxmemory=512

# Application parameters. This is where you specify TC server main class

wrapper.app.parameter.1=com.tc.server.TCServerMain

wrapper.app.parameter.2=2

wrapper.app.parameter.3=-f

wrapper.app.parameter.4=D:\SoftwareAG\Terracotta\bin\tc-config.xml

wrapper.app.parameter.5=com.tc.admin.TCStop

wrapper.app.parameter.6=FALSE

wrapper.app.parameter.7=5

wrapper.app.parameter.8=-f

wrapper.app.parameter.9=D:\SoftwareAG\Terracotta\bin\tc-config.xml

wrapper.app.parameter.10=-n

wrapper.app.parameter.11=Server2

wrapper.app.parameter.12=9520

wrapper.logfile=D:\SoftwareAG\Terracotta/wrapper.log

# Format of output for the log file.  (See docs for formats)

wrapper.logfile.format=LPTM

# Log Level for log file output.  (See docs for log levels)

wrapper.logfile.loglevel=INFO

# On exit behaviour

wrapper.on_exit.0=SHUTDOWN

wrapper.on_exit.default=RESTART

• ·        You can install the service by running the following command: sc create Terracotta binPath= D:\SoftwareAG\Terracotta\wrapper\wrapper.exe -s D:\SoftwareAG\Terracotta\TerraCotta.conf start= auto

Yet Another Java Service Wrapper (YAJSW)

YAJSW is a pure java implementation of the Tanuki Java Wrapper. Its big advantage is that it’s free and supports both 32 and 64-bit JDK 5, 6 and 7. You can find the software at http://yajsw.sourceforge.net/

Configuration can be done as follows:

• ·        Download and unzip the wrapper under D:\SoftwareAG\Terracotta\wrapper 
•       Make a wrapper.conf file with the following configuration and place it under D:\SoftwareAG\Terracotta\wrapper. Important to mention is to use / instead of \ !!

wrapper.java.command=D:/SoftwareAG/jvm/jvm170_64/bin/java

wrapper.stop.conf = D:/SoftwareAG/Terracotta/wrapper/wrapper.stop.conf

wrapper.java.classpath.1= D:/SoftwareAG/Terracotta/wrapper/wrapper.jar

wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar

wrapper.java.app.mainclass = com.tc.server.TCServerMain

wrapper.console.title = TerraCotta

wrapper.ntservice.name = TerraCotta

wrapper.ntservice.displayname = TerraCotta

wrapper.ntservice.description = TerraCotta

wrapper.app.parameter.1= -f

wrapper.app.parameter.2= D:/SoftwareAG/Terracotta/bin/tc-config.xml

wrapper.logfile=D:/logs/wrapper.log

wrapper.logfile.format=LPTM

wrapper.logfile.rollmode=DATE

wrapper.logfile.maxdays=10

wrapper.java.additional.1=-server

wrapper.java.additional.2=-Xms1024M

wrapper.java.additional.3=-Xmx1024M

wrapper.java.additional.4=-XX:+HeapDumpOnOutOfMemoryError

wrapper.java.additional.5=-Dcom.sun.management.jmxremote

wrapper.java.additional.6=-Dsun.rmi.dgc.server.gcInterval=31536000000

wrapper.java.additional.7=-Dnet.sf.ehcache.enableShutdownHook=true

wrapper.java.additional.8=-Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBFactory

wrapper.java.additional.9=-Dtc.install-root=D:/SoftwareAG/Terracotta

wrapper.java.additional.10=-Djava.net.preferIPv4Stack=true

wrapper.on_exit.0=SHUTDOWN

wrapper.on_exit.default=RESTART

• ·        Also make a wrapper.stop.conf file in the same folder with the following configuration:

wrapper.stopper = true

wrapper.java.command=D:/SoftwareAG/jvm/jvm170_64/bin/java

wrapper.java.classpath.1= D:/SoftwareAG/Terracotta/wrapper/wrapper.jar

wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar

wrapper.java.app.mainclass = com.tc.admin.TCStop

wrapper.app.parameter.1=-f

wrapper.app.parameter.2=D:/SoftwareAG/Terracotta/bin/tc-config.xml

wrapper.app.parameter.3=-n

wrapper.app.parameter.4=Server1

wrapper.app.parameter.5=9520

wrapper.logfile=D:/logs/wrapper_stop.log

wrapper.logfile.format=LPTM

wrapper.logfile.rollmode=DATE

wrapper.logfile.maxdays=10

wrapper.java.additional.1=-server

wrapper.java.additional.2=-Xms512M

wrapper.java.additional.3=-Xmx512M

wrapper.java.additional.4=-XX:+HeapDumpOnOutOfMemoryError

wrapper.java.additional.5=-Dcom.sun.management.jmxremote

wrapper.java.additional.6=-Dsun.rmi.dgc.server.gcInterval=31536000000

wrapper.java.additional.7=-Dnet.sf.ehcache.enableShutdownHook=true

wrapper.java.additional.8=Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBactory

wrapper.java.additional.9=-Dtc.install-root=D:/SoftwareAG/Terracotta

wrapper.java.additional.10=-Djava.net.preferIPv4Stack=true

·        You can test the configuration by running D:\SoftwareAG\Terracotta\wrapper\bat\runConsole.bat

In that case the wrapper will load the configuration file and start the process. This can be used to debug the wrapper configuration file. Afterwards the service can be installed using the D:\SoftwareAG\Terracotta\wrapper\bat\installService.bat command

For both wrappers you can define the wrapper on exit behavior. By setting wrapper.on_exit.0=SHUTDOWN the wrapper will do nothing when it gets a normal return code 0 from terracotta. For all other return codes the wrapper will automatically restart the terracotta service. We do this by setting wrapper.on_exit.default=RESTART.

We need this option in an active-mirror terracotta setup. If we shut down the passive node and restart it, we will get the following error in the terracotta server log:

ERROR com.tc.l2.ha.ClusterState -
********************************** ERROR ***********************************
* This server is running with persistence turned on and was stopped in
* PASSIVE-STANDBY state. Only the ACTIVE-COORDINATOR server is allowed  to
* be restarted without cleaning up the data directory with persistence
* turned on.
*
* Please clean up the data directory and make sure that the
* ACTIVE-COORDINATOR is up and running before starting this server. It is
* important that the ACTIVE-COORDINATOR is up and running before starting
* this server else you might end up losing data
****************************************************************************

[WrapperStartStopAppMain] ERROR tc.operator.event - NODE : Server2  Subsystem: CLUSTER_TOPOLOGY Message: Started with dirty database. Exiting!! Restart enabled
[WrapperStartStopAppMain] ERROR com.terracottatech.dso - Marking the object db as dirty ...
[WrapperStartStopAppMain] ERROR com.terracottatech.console - This standby Terracotta server instance had to restart to automatically wipe its database and rejoin the cluster.

Terracotta will stop with a return code different than 0. By using the on exit options, the wrapper automatically restarts the service.

BizTalk Integration Summit in Seattle

Hi,
 
I had the luck this year to attend the BizTalk Integration Summit in Seattle, Washington.
This blog post is about how this went for me and what I will remember from it, based on the notes I took.

First of all we were about 300 people attending. We had Partners, Customers and off-course Microsofties. Somebody told me that we were 1/3 partners, 1/3 customers and 1/3 Microsoft.

From Belgium we only had a CodIt delegation and myself representing i8c. Our Dutch colleagues had more people present and I had the pleasure to get to know better the Motion10 people.

Also all the Microsoft Integration MVP’s were present, so I had the chance to meet some in person.

These are my notes from day 1:

BizTalk Integration Summit 2013 Agenda
Time	Session	Speaker
Thursday, November 21 (Day 1)
8:50am - 9:00am	Welcome to BizTalk Summit 2013	Mark Mortimore, Microsoft
9:00am - 10:30am	Delivering Modern Enterprise Applications	Scott Guthrie, Microsoft Vivek Dalvi, Microsoft
11:00am - 12:30pm	Using Windows Azure BizTalk Services to Accelerate Application Integrations	Karthik Bharathy  & Sameer Chabungbam, Microsoft
1:30pm - 2:30pm	Business Process Management	Alok Jain, Microsoft
2:30pm - 4:00pm	What's New in BizTalk Server 2013?	Jorge Gomez Basanta, Paul Larsen, & Mark Mortimore, Microsoft
4:30pm - 5:15pm	Best Practices for Enterprise Clinical Integration	Alan Scott, HCA

Delivering Modern Enterprise Applications

The trend today is: Mobility, Apps, Big data and cloud.
The challenge is how to connect and collaborate.

The Microsoft Could OS is the common platform to get this to work. It enables the creation of modern applications.
 
API Mgmt is new to the Cloud OS so we definitively have to look into that.
Mimeo.com (are live with the Windows Azure BizTalk Services preview)

We have Cloud EAI: in order to bridge the on-premise LOB’s and to integrate with SaaS solutions.
We also have Cloud EDI to extend the EAI with B2B functionality.

   I think Cloud EAI will always be used in conjunction with Cloud EDI.

At the summit they announced that Windows Azure BizTalk Services is now general available.
Customers should now no longer hesitate to start using it.

The road ahead will focus on on-premise (BizTalk Server) updates and cloud integration with quarterly updates. Microsoft stays committed to BizTalk Server.

The Key trends that were mentioned are:

• Experience is important since integration = complex. At Microsoft they have the experience J
• SaaS is getting more and more important for Cloud integration
• Hybrid applications are a reality.

Microsoft aims at making everything available out-of-the-box, so that we only need to concentrate on configuring the integration solution. We will have to wait and see this happen.
 
Still, everything will be extensible. 1st party experience for 3rd party ==> They expect a marketplace to be put in place, which will be a great opportunity for partners.

They announced that BPM will become part of WABS next to EAI en B2B.
A common management tooling infrastructure will be put in place.

Release:

• BizTalk server every 2 years
• R2 minor release (platform, key features) every 2 years

Meaning one year a major and the next year a minor release.

• CU every quarter
• WABS refresh every 3 months (at least)

Microsoft is really committed to let BizTalk and WABS evolve and finally merge into 1 product.

BizTalk Server 2013 R2 will be released in the first half of 2014. It will allow the product to align itself with:

• VS 2013
• SQL 2014
• WS 2012 R2

It will also contain Healthcare improvements.
 
One thing I learned from this summit is that Healthcare is a big thing for BizTalk (certainly in the US).

The WABS - GA also contains isolation and predictable performance. We now can have an isolated environment per subscription.
EDI is now available is all WABS editions!

WABS what is next:

• AAD
• Scheduled backup and restore capability
• EAI
• Adapter extensibility (this is the main focus)
• Light weight BizTalk adapter service
• Pull from queue and topics
• B2B
• EDIFACT
• Custom code in EDI bridge
• BPM
• Workflow on Azure
• Rules engine designer
• Process designer 

WABS Futures:

• BAM
• Partner directory
• Self service partner on-boarding
• Managed file transfer

 It seems that the future looks very bright for WABS.


Using Windows Azure BizTalk Services to Accelerate Application Integrations

GA: It also includes some extra performance counters
TPM migration tool to migrate BizTalk Server parties.
EDI in all editions, no longer limited to the Premium version!!

They also made it possible to have more than one user deploying WABS solutions per subscription. It is even so that per e-mail one now can manage several subscriptions.

Backup/restore tool.

Next:

• Edifact
• Pull service bus
• Edi bridge agreement association
• Scheduled backup/restore
• Workflow

They showed us an EDIFACT demo including batching. Now we only need to be allowed to participate in the TAP program to get hold of the bits and start doing WABS EDI projects in Belgium.
 

Business Process Management

This is new in the WABS. They only started developing it, so it will take another year before we see anything of this.

Model & Execute & Monitor business processes.
Those can be document, people or system (integration) centric.

Model:

• Process modeller
• Standard notation. They are thinking of using BPMN notation. I say they should use the BPMN notation!
• Automated business processes
• Define check-points and kpi's
• Repeatable and flexible

Key capabilities:

• BPMN notation
• Unified tooling
• Process, Rules, BAM
• EAI, B2B, BP
• Rich set of out-of-the-box activities
• Extensible, third party extensions
• .net workflow
• Support for custom code

Rule engine:

• Declarative
• Logic in Rules, process in workflow
• Key capabilities
• BizTalk BRE goes to the cloud
• Vocabulary and versioning included

What is missing: SCORECARDS!!! I think they should take this opportunity to implement this too.

Monitor:

• BAM
• KPI's and alerts
• Track messages and status
• Identify bottleneck in Real-time
• Message tracking for troubleshooting
• BAM Portal

Deploy to an artefact store.
A marketplace for the activities will become available. Third party activities will also be possible.
Management portal for deploy/config/start/stop of the business processes.
A BAM Portal for tracking business processes.

A TAP program will become available next year around the time of the summit I presume.

Well, BPM is coming to WABS, so we will no longer be restricted to WF or SharePoint for workflow solutions. WABS will include BPM built on top of WF.

What's New in BizTalk Server 2013?

 

Coming:

• SFTP: proxy support, SSO
• REST: JSON, Empty message support exposing REST
• Service Bus: more authentication options
• ESB toolkit: BRI revolver, SSO configuration
• Healthcare: MLLP 64 bit, dynamic adapter, better troubleshooting experience, HL7 2.6, freetext data type

Those will be included in the BizTalk Server 2013 R2 version.

Best Practices for Enterprise Clinical Integration

HCA presented their impressive BizTalk implementation.

As with any BizTalk installation they also learned from their mistakes J

I found the first day the most interesting part of the summit. A lot of new stuff announced and we know where they are heading with BizTalk and WABS.

For those of us investing in BizTalk and WABS the future looks bright.
 

 

These are my notes from day 2:

BizTalk Integration Summit 2013 Agenda
Time	Session	Speaker
Friday, November 22 (Day 2)
9:00am - 10:00am	One Microsoft Integration – Focus on Solutions	Mark Mortimore & Hector Rodriguez, Microsoft Eric Stott, Stott Creations
10:30am - 12:30pm	Deep dive on EAI	Sameer Chabungbam, Microsoft
10:30am - 11:00am	Applying Cloud Integration Patterns in the real world	Sam Vanhoutte, Impack Alliance
11:00am - 11:30am	Going live on Windows Azure BizTalk Services Preview - Tales from the bleeding edge	Kent Brown, Nimbo Dwight Goins, Nimbo
11:30am - 12:00pm	Management and Monitoring of your BizTalk Environments using BizTalk360	Saravana Kumar, BizTalk 360
12:00pm - 12:30pm	Running at Microsoft scale on Biztalk technologies	Eric Watson, Microsoft
1:30pm - 2:30pm	Learn about Service Bus	Ziv Rafalovich, Microsoft
1:30pm - 2:00pm	Exposing Enterprise Data Using APIs, BizTalk, and Windows Azure Service Bus to the Mobile World	Simon Barere, SOA Software
2:00pm - 2:30pm	BizTalk Server 2013 Performance Optimization and Lessons from the field	Ashish Bhambhani, Sandeep Kesiraju, & Todd Rivers, Microsoft
2:30pm - 3:00pm	SOA Governance for BizTalk Server  - Concepts and Practice	Andrew Slivker, Nevatech
2:30pm - 3:30pm	Deep dive on EDI / B2B	Karthik Bharathy, Microsoft
3:00pm - 3:30pm	Healthcare Integration with BizTalk Server and Corolar from Dapasoft	Ramraj Rajkumar, Dapasoft

One Microsoft Integration – Focus on Solutions

On-premise revenue counts as azure revenue. Interesting fact.

We need to connect to a business unit manager (list will become available), in order for us and them to do more business J
One integration stack: mobile, BizTalk, API, services bus, workflow.
Again focus on Healthcare industry.

"why" integration reduces friction

"opportunity" reduce waste, less defects, new customer, ..
"cloud" ROI 1.7x compared to on-perm, 40% less consulting J

Move to hybrid cloud: trend for 2015
One consistent hybrid platform: private, cloud
Experience = app + devices + data + security.
ESB helps Healthcare solutions to consolidate workflow and make them more generic.

Deep dive on EAI

Overview of the WABS EAI capabilities

• Validation
• Enrichment
• Transform: xslt based but in the future it will also support JSON and .NET data

.NET object model for transforms is not available for development (maybe in the future)

• Routing is the first match gets the message: that is why we have a sequence number in the routing list
• For PUB/SUB routing we need to use service bus topics
• BizTalk Service Explorer VS plugin

http://visualstudiogallery.msdn.microsoft.com/1f75a6a6-a54e-44eb-8b11-1b5ea8928754?SRC=VSIDE
It allows to debug a bridge via the plug-in. Wauw J

• Extension is possible via message inspectors, scripting functoids and custom wcf bindings

IMessageInspector interface, PipelineProperty attribute to include custom design properties
Custom WCF Binding

• In order to deploy custom assembly we need to set copy local to true!
• They are working on a framework to provide our own source adapter framework

Focus on the system NOT on the framework
 
PowerShell: Management samples available.
  

Learn about Service Bus

Loosely coupled apps

Message oriented middleware
Hybrid solutions

Push/event model: OnMessageOptions (cliënt.OnMessage) OnSession is coming

               Replaces the fact that we need to loop to receive messages.
Shared Access Signature authentication (SAS)
QueueClient Peek allows us to peek for messages.
Retry on transient errors MessagingFactory RetryPolicy
TopicDescription, QueueDescription EnablePartitioning

Service Bus 1.1 (on-premise) compatible with SDK 2.1

AMQP 1.0

Paolo's samples on hybrid solutions

Next:

• Reliability: SLA, dedicated resources
• Lazy writing (push to DB later) results in better throughput
• Device to cloud, the next big thing for Service Bus
• Smart mobility: 2018 30% smart cars
• Smart grid (power)
• Smart home
• Smart building
• Smart factory
• Smart logistics
• Intelligence Delivery via backing services

Q2 2014 Customer adoption program for Device to cloud

Deep dive on EDI / B2B

Dev max 10 agreements

Basic max 25 agreements
Standard max 250 agreements
Premium max 1000 agreements

TpmContext to create agreements @ runtime, all about using WCF Data Services

Windows Azure BizTalk Services Explained book coming…

Including the Xbox one supply chain solution @ Microsoft:

http://msdn.microsoft.com/en-us/library/dn521038(v=bts.10).aspx

 

I found the second day less interesting, still it made it even clearer that WABS is ready for the integration world and that we also should take the device to the cloud thing, seriously.

Finally, the sessions have been recorded and will become available as will be the presented slide decks, so stay tuned. 

Koen Van Oost

Business Integration Architect  @ i8c