Great news for the BizTalk community, for the third time the BizTalkCrew (Steef-Jan Wiggers, Tord Glad Nordahl, Nino Crudele, Saravana Kumar and me) are hosting the BizTalk Innovation Day, an one-day event focused purely on Microsoft BizTalk Server/BizTalk Services and related topics, in Norway!

The two previous BizTalk Innovation Day editions in Norway were carried out in Stavanger, this time the event will be held in Oslo, Thursday, September 25, 2014 from 9:00 AM to 6:30 PM at MESH AS, Tordenskiolds Gate 3, 0160 Oslo, Norway (see more information about the event and sign up here: BizTalk Innovation Day Sign up)

BizTalk-Innovation-day-Norway-2014

About my session…

Last year I had the opportunity to talk about BizTalk Mapping Patterns and Best Practices, this session led me to write a book about this topic, book that I have been writing for the past 12 months… more news will be release during the event.

This year I decide not to choose any topic and leave this task to the host of the event. So I ask to Tord Glad Nordahl, please choose a topic that the Norwegian community want to hear me speak, that pleases and interest them, I can speak about whatever you want! So it’s not my fault that Tord chose a similar topic Smile… my session will be this year about transformation in BizTalk Services, here is the abstract:

Migrating current BizTalk Server projects to BizTalk Services or starting new BizTalk Services projects can provide some challenges because although the developing experience is very similar, we are still talking about a new integration platform on Azure, and one of the main differences is the way we apply messages transformation with the new Transform Designer.

In this session, we will see the main differences between BizTalk Mapper Design (BizTalk Server) and the Transform Designer (BizTalk Services), and will address several other topics as:

  • How can we migrate existing maps by using the BizTalk Map Migration Tool and what are the limitations?
  • Can we use the existent maps in BizTalk Services projects?

However, mainly we address and explain how you can apply some common mapper problems, how the editor works, what are the functionalities and hopefully you will enhance new skills on BizTalk Services transformations

Target Audience

People interested in BizTalk Server/BizTalk Services and integration on several different levels, developers, administrators and architects. There is something for everyone

Agenda

So we invite you all to join us next September 25, 2014 in Oslo, Norway, where you can count with the following agenda:

08:45 – 09:00: Registration/Welcome/Kick-Off;

09:00 – 10:00: Hitchhiker’s guide to integration with Microsoft Azure BizTalk Service
by Steef-Jan Wiggers [Microsoft Integration MVP];

10:00 – 11:00: The Future of Integration
by Richard Seroter [Microsoft Integration MVP];

11:00 – 11:30: Break;

11:30 – 12:30: Developing like an Integration Person
by Nino Crudele [Microsoft Integration MVP];

12:30 – 13:30: Lunch;

13:30 – 14:30: Top 10 features we added in BizTalk360 in 2014
by Saravana Kumar [Microsoft Integration MVP];

14:30 – 15:30: The new Transform Designer available in BizTalk Services
by Sandro Pereira [Microsoft Integration MVP];

15:30 – 16:00: Break;

16:00 – 17:00: Developers Vs. Operation
by Tord Glad Nordahl [Microsoft Integration MVP];

17:00 – 22:00: Networking/Drinks

Click here to ensure your registration or to get more information’s about this event.

Sign up now and ensure your presence!

Last year was like this….

It was the biggest BizTalk event organized in Norway with a total of 70 people attended in this two days event, 6 Microsoft Integration MVP’s present as speakers and 1 member of Microsoft Product group, these are some of the numbers that reflect the huge success of this event!

1374196_656417837709552_1939283664_n577355_656417451042924_157939711_n539118_656417607709575_1484086093_n1380741_656417711042898_1267435491_n1231532_656417917709544_607306574_n1381450_656417994376203_490756296_n1379931_656418897709446_2064622026_n

553052_656418094376193_922409271_n1377565_740038886023088_1623312865_n1378846_656418134376189_836271093_n1380172_656418031042866_267856785_n1381701_656418584376144_720013891_n

Check more in our Facebook page.

Again, Reserve your ticket at http://www.biztalk360.com/Events/BizTalk-Innovation-day-2014-Norway/ and we are looking forward to welcoming you to Oslo for the BizTalk Innovation Day Norway in September, 25!

In this last week I’ve been migrating legacy custom adapters from previous BizTalk Versions (2004 and 2006) for recent version of BizTalk Server (2010 and 2013). In this particular case is an Isolated Adapter that in fact is a socket listener that will be listening on one or more TCP ports for a custom and private message format to address and solve an integration problem with legacy platforms of one of my clients. Working and creating custom adapter it’s always a funny task, it makes me remember and come back to my roots of .NET developer and how much I love this job.

Note: It’s also important to remember that recent version of BizTalk Server normally runs under 64-bit and because BizTalk Administration Console runs under 32-bits it’s imperative to register the adapter in both 32-bit and 64-bit versions of the BizTalk Adapter, at least if you want the adapter to run under 64-bit, nevertheless you always need to register under 32-bit.

If you are developing a custom adapter, you can register it with BizTalk Server by modifying and running one of the registry files included with the sample file adapter in the software development kit (SDK). Or, you can use the Adapter Registry Wizard to create a registry file. You can learn more about this topic here: Registering an Adapter.

Although it is a migration of an existing adapter which have worked correctly over several years I decide to optimize the adapter to run on new versions of NET Framework (4.0) and also to support 64-bit. The question and the main reason for this post is: How to properly register this custom BizTalk adapter?

How to properly register a custom BizTalk adapter

Again and this is very important:

  • On a 32-bit machine, the registry (.reg) file generated by the Adapter Registration wizard must be run from the command prompt.
  • On a 64-bit machine, the registry (.reg) file generated by the Adapter Registration wizard must be run both from the 32-bit and 64-bit command prompt.

So if you are in a 32-bit machine you only need to run the adapter registry (.reg) from the command prompt or even double-click in adapter registry (.reg) file.

However if you are in a64-bit machine you need extra steps:

  • If run the adapter registry (.reg) from the command prompt or even double-click in adapter registry (.reg) file – this will register the adapter under 64-bit.

If you try to check and add this new adapter in the BizTalk Administration Console (Adding the Adapter to BizTalk Server) you will notice that the adapter will not be present and referenced in the Adapter drop box.

  • So now you need to force to do the same in 32-bit, to accomplish that you need to open a 32-bit command prompt:
    • Click Start.
    • Type %windir%\SysWoW64\cmd.exe in Start Search box and press Enter
    • Run the same adapter registry (.reg) from this command prompt

Again, if you try to check and add this new adapter in the BizTalk Administration Console you will notice that the adapter now will be present in the drop box.

Recently I work in a project when I need to communicate with Oracle database in which I encountered some platform configuration problems. In general, communicate with Oracle is very simple and somewhat similar to SQL Server.

In development phase I didn’t have any problem, but notice that before you start developing you need to make sure that you have BizTalk Adapter Pack installed and Oracle client (or at least Oracle Data Access Components (ODAC) – 32 and 64 bits) installed to use BizTalk Adapter for Oracle in your BizTalk environment.

The BizTalk Adapter Pack consists of the following adapters:

  • Microsoft BizTalk Adapter for Oracle Database (Oracle Database adapter).
  • Microsoft BizTalk Adapter for Oracle E-Business Suite (Oracle E-Business adapter).
  • Microsoft BizTalk Adapter for mySAP Business Suite (SAP adapter). This also includes the .NET Framework Data Provider for mySAP Business Suite (Data Provider for SAP).
  • Microsoft BizTalk Adapter for Siebel eBusiness Applications (Siebel adapter). This also includes the .NET Framework Data Provider for Siebel eBusiness Applications (Data Provider for Siebel).
  • Microsoft BizTalk Adapter for SQL Server (SQL adapter).

And you can found more information how to install it here: BizTalk 2013 Installation and Configuration – Installing BizTalk Adapter Pack (Part 12)

The Microsoft BizTalk Adapter for Oracle Database is a Windows Communication Foundation (WCF) custom binding. This binding contains a single custom transport binding element that enables communication with an Oracle database.

The following figure shows part of “BizTalk Oracle Adapter Architecture”:

BizTalk-Oracle-Adapter-Architecture

According to BizTalk official documentation, the support version of Oracle are:

  • Oracle database version 11.1,
  • Oracle database version 10.2,
  • Oracle database version 10.1,
  • Oracle database version 9.2

And supported client versions:

  • Oracle Data Access Components for Oracle Client 11.1.0.6 with Patch Set 11.1.0.7,
  • Oracle Data Access Components for Oracle Client 11.1.0.7

You can read more on how to install Oracle client here:

As I said earlier, I didn’t had any problem in development phase however in runtime I catch the following error:

The adapter failed to transmit message going to send port “SEND_Oracle” with URL “oracledb://connection”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.IO.FileNotFoundException: Could not load file or assembly ‘Oracle.DataAccess, Version=2.112.1.2, Culture=neutral, PublicKeyToken=89b483f429c47342′ or one of its dependencies. The system cannot find the file specified.

File name: ‘Oracle.DataAccess, Version=2.112.1.2, Culture=neutral, PublicKeyToken=89b483f429c47342′ —> System.IO.FileNotFoundException: Could not load file or assembly ‘Oracle.DataAccess, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342′ or one of its dependencies. The system cannot find the file specified.
File name: ‘Oracle.DataAccess, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342′
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.

So I tried to check in the GAC if the correct assemblies were there and I realized that the client add installed a different version of the Oracle Client: Oracle 11g R2.

BizTalk-Oracle-Client-GAC-dll

CAUSE

When installing the Oracle WCF Adapter for BizTalk there is a design time requirement to use Oracle.DataAccess Version 2.111.7.0.

Official the Oracle 11g R2 is not supported and again according to official documentation I should use one of the supported clients. However my client only had access to this version and did not want to use another. Although not officially supported you can connect to Oracle 11g R2 database and use Oracle 11g R2 client with minor configuration adjustments.

SOLUTION

One possible solution to fix this problem is configuring Assembly Binding Redirection in the machine configuration file (Machine.config):

  • 32-bit: c:\Windows\Microsoft.NET\Framework\[version]\config\machine.config
  • 64-bit: c:\Windows\Microsoft.NET\Framework64\[version]\config\machine.config

Note: You should apply this in both 32 and 64-bit machine configuration files.

By using the <assemblyBinding> Element for <runtime> that will contain all the information about assembly version redirection and the locations of assemblies.

In this case you should apply the following configurations:

<runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
        <assemblyIdentity name="Oracle.DataAccess"
          publicKeyToken="89b483f429c47342" />
 
        <bindingRedirect oldVersion="2.111.7.0" newVersion="2.112.1.2" />
      </dependentAssembly>
       </assemblyBinding>
</runtime>

Just to be sure, I add this configuration in both 32 and 64-bit in .NET Framework 2.0 and 4.0 machine configuration files.

Thanks to all participants, however only 59% of the responses were correct, all due to a small trap – I apologize for that – that I put the third question. The correct answers for the questions were:

  • Does BizTalk Services Bridges applies the VETER (Validate, Enrich, Transform, Enrich and Route)?
    Correct answer: A) Yes
  • Where the Enrichment component occurs inside the bridges?
    Correct answer
    : c) both, pre and post transformation
  • What are the name of the components that we drag from the Toolbox windows to the grid pages of BizTalk Services Maps to perform transformations?
    Correct answer: B) Operations

Getting-Started-with-BizTalk-ServicesI think the first two questions were quite simple, as was proven in the responses of the participants, however the equivalent of Functoids in BizTalk Server maps are called Operations in the BizTalk Services maps.

Again, thanks to all participants and congratulations to:

  • Marcus Rangell from Sweden
  • Christophe Brando from France

for winning the Getting Started with BizTalk Services eBook from Packet Publishing.

The book is available through Amazon and PACKT is self, so go to either of one and order it! (Also available for the Kindle)

I have a BizTalk Server 2010 Test Environment that was working properly for some time, all the adapters from BizTalk Server Adapter Pack 2010 were installed with the last cumulative updates and also working properly, in this environment we use the SQL Server Adapter.

However the last time the team try to configure the receive location present in the application they unexpected got the following error:

“Unable to create binding configuration element for editing. Check the values of the bindyingType and BindingConfiguration properties. (Microsoft.BizTalk.Adapter.Wcf.Converters.CreateBindingException) Unable to get binding type for binding extension “sqlBinding”. Verify the binding extension is registered in the machine.config.”

WCF-Custom-Transport-Properties-Error

Also every time they try to enable the Receive Location they automatically become disabled again. When I checked the Event Viewer I also found this two errors:

“The Messaging Engine failed to add a receive location “WcfReceiveLocation_SqlAdapterBinding_TypedPolling ” with URL “mssql://SERVER:PORT/INSTANCE/DB?InboundId=id” to the adapter “WCF-Custom”. Reason: “Microsoft.BizTalk.Adapter.Wcf.Converters.CreateBindingException: Unable to get binding type for binding extension “sqlBinding”. Verify the binding extension is registered in machine.config.”

“The receive location “WcfReceiveLocation_SqlAdapterBinding_TypedPolling ” with URL ” mssql://SERVER:PORT/INSTANCE/DB?InboundId=id ” is shutting down. Details:”The Messaging Engine failed while notifying an adapter of its configuration. “.”

 

WCF-Custom-Ports-disabled

Suspecting the problem but curious to understand the problem I try to create a new Receive Location and I found out that the SQL binding was not present:

WCF-Custom-Transport-Properties-list-bindings-without-sql

CAUSE

The SQL Database adapter (also the Oracle Adapter or the Oracle E-Business Suite) is a WCF custom binding, which is registered under System.ServiceModel in the machine.config file.

Important note: A 64-bit platform has two machine.config files, one used by the 32-bit applications and the other used by the 64-bit applications. Actually they have several machine.config for different .NET Frameworks, however in this case we are talking about the last .NET Framework v4.0.30319 normally present in BizTalk Server 2010 environment which you can find in the following folders:

  • 32-bits: C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config
  • 64-bits: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config

When you install the 64-bit version of the BizTalk Adapter Pack, the setup wizard registers the bindings in the 64-bit version of the machine.config file. However, BizTalk Server Administration console runs as a 32-bit process and hence when you configure a port for the adapter, it checks for the bindings in the 32-bit version of the machine.config file and fails giving an error. This is also the reason why you should always installed 32 and 64 bits version of the adapters.

However in my particular case that wasn’t the problem because I had both version of the adapter installed and they were working properly. So in my case the problem started to happen because one of the members installed a new .NET Framework version (4.5.2) in the BizTalk Server machines and then uninstalled. After this all the adapters present in the BizTalk Adapter Pack stopped to work.

SOLUTION

It depends on the configuration of your environment.

Solution 1:

If 32-bit versions of the BizTalk Adapter Pack are note installed in your machine you should:

  • Install both the 32-bit and 64-bit versions of the BizTalk Adapter Pack on a 64-bit WCF LOB Adapter SDK installation.
    • Note: You must only have a 64-bit WCF LOB Adapter SDK installation. Side-by-side installation of 32-bit and 64-bit WCF LOB Adapter SDK on a single computer is not supported.

Solution 2

Otherwise you should check in the machine.config if the custom binding extension are configured properly in both 32 and 64-bit files.

To check or register the adapter bindings or the .NET Framework Data Providers:

  • Navigate to the machine.config file on the computer.
  • Open the file using a text editor or using the SvcConfigEditor.exe util to edit the config file. It is easy to add the binding extensions in this utility otherwise with a common text editor like notepad is very easy to make errors while editing the config file.
  • Check if there are present the adapter bindings otherwise you should register them:
    • Search for the element “<client>” under “<system.serviceModel>”:

machine-config-system-serviceModel-client

      • If not present add the following line under it:
<client>
   <endpoint binding="sapBinding" contract="IMetadataExchange" name="sap" />
   <endpoint binding="siebelBinding" contract="IMetadataExchange" name="siebel" />
   <endpoint binding="oracleDBBinding" contract="IMetadataExchange" name="oracleDb" />
   <endpoint binding="oracleEBSBinding" contract="IMetadataExchange" name="oracleEBS" />
   <endpoint binding="sqlBinding" contract="IMetadataExchange" name="mssql" />
</client>
    • Search for the element “<bindingElementExtensions>” under “<system.serviceModel><extensions>”

machine-config-system-serviceModel-extensions

      • Look for the missing adapter binding and if they are not present add the following lines under the “<bindingElementExtensions>” node:
<add name="sqlAdapter" type="Microsoft.Adapters.Sql.SqlAdapterBindingElementExtensionElement, Microsoft.Adapters.Sql, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
<add name="sapAdapter" type="Microsoft.Adapters.SAP.SAPAdapterExtensionElement, Microsoft.Adapters.SAP, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
<add name="oracleDBAdapter" type="Microsoft.Adapters.OracleDB.OracleDBAdapterExtensionElement, Microsoft.Adapters.OracleDB, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
<add name="oracleEBSAdapter" type="Microsoft.Adapters.OracleEBS.OracleEBSBindingElementExtensionElement, Microsoft.Adapters.OracleEBS, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
<add name="siebelAdapter" type="Microsoft.Adapters.Siebel.SiebelAdapterExtensionElement,Microsoft.Adapters.Siebel, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
  • To check and register the .NET Framework Data Providers:
    • Search for the element “<DbProviderFactories>” under the “<system.data>” node.
    • Look for the missing .NET Framework Data Providers. Add the following sections under the “<DbProviderFactories>” node, depending on the missing provider. You must register all the providers if the setup wizard fails to register any.
<add name="SAPClient Data Provider" invariant="Microsoft.Data.SAPClient" description=".NET Framework Data Provider for mySAP Business Suite" type="Microsoft.Data.SAPClient.SAPClientFactory,Microsoft.Data.SAPClient, Version=<version>, Culture=neutral, PublicKeyToken=<public key>" />
<add name="SiebelClient Data Provider" invariant="Microsoft.Data.SiebelClient" description=".NET Framework Data Provider for Siebel eBusiness Applications" type="Microsoft.Data.SiebelClient.SiebelProviderFactory,Microsoft.Data.SiebelClient, Version=<version>, Culture=neutral, PublicKeyToken=<public key>" />
  • Save and close the machine.config file.

After I edit and fixed the machine.config file the WCF-SQL adapter started to work again.

WCF-Custom-Transport-Properties-list-bindings

WCF-Custom-Ports-enabled

Exciting news for BizTalk community! I have 2 e-copies of the new book Getting Started with BizTalk Services to give away, courtesy of PACKT Publishing.

This book was written by Karthik Bharathy (Lead Program Manager in the BizTalk product group) and Jon Fancey (Microsoft Integration MVP) published by Packet Publishing. You can read more about this book here on Packt’s website and do not forget to download the free sample chapter here. You can also see my review in my last post hehe.

Getting-Started-with-BizTalk-Services

Contest: “MABS Contest”

  • Answer the following three questions:
    • Does BizTalk Services Bridges applies the VETER (Validate, Enrich, Transform, Enrich and Route)?
      Options: A) Yes B) No
    • Where the Enrichment component occurs inside the bridges?
      Options
      : A) pre transformation B) post transformation c) both, pre and post transformation
    • What are the name of the components that we drag from the Toolbox windows to the grid pages of BizTalk Services Maps to perform transformations?
      Options: A) Functoids B) Operations c) Map Functions

Closing date: August 18th 2014.

Terms of participation:

  • Each participant can only participate once;
  • The response must be sent by email to sandro-pereira@live.com.pt with the following subject: “Contest – Getting Started with BizTalk Services
  • Along with the answers the participant must include the following personal information (This information will not be used for any commercial purpose – only need to send the email to the winners):
    • First and last name
    • Country
    • Email

Award:

Two participants will win:

  • One e-copy of the book: Getting Started with BizTalk Services

The awards will be distributed by lottery and the e-copies will be sent to winners via email by PACKT Publishing.

Good luck!

Special note: The book is available through Amazon and PACKT is self, so go to either of one and order it! (Also available for the Kindle)

Feel free to comment this initiative (have you found it interesting or do you think this kind of initiative should be repeated), the book or my blog.

Since the book came out I have this task to do, I was fortunate to receive an e- copy of the book Getting Started with BizTalk Service written by Karthik Bharathy (Lead Program Manager in the BizTalk product group) and Jon Fancey (Microsoft Integration MVP), but only now I found the time to accomplish.

Getting Started with BizTalk Service is guidance book, rather than a reference manual, that nicely summarizes and well organized in 8 chapters and proximally 150 pages that will cover all of the basic concepts of the Microsoft Azure BizTalk Services like Messages and Transforms, Bridges, EAI and B2B Integration, API, Tracking and Troubleshooting and the process of moving from on-premises to the cloud:

  • Chapter 1, Hello BizTalk Services, introduces BizTalk Services, its architecture, and how to create an instance of the service and deploy solutions.
  • Chapter 2, Messages and Transforms, explains message processing and how to transform messages to different formats. Also, it explains how to use mapping operations to aggregate data, perform reference data lookups, and use custom code in transformations.
  • Chapter 3, Bridges, gives a detailed look at bridges and explains how to enrich messages and route messages to different endpoints.
  • Chapter 4, Enterprise Application Integration, explains sources and destinations and how to connect BizTalk Services to enterprise applications and systems on-premises from the cloud.
  • Chapter 5, Business-to-business Integration, discusses B2B integration using industry standard protocols such as EDIFACT, X12, and AS2. It also discusses how to create partners and agreements in BizTalk Services to connect with trading partners and how to utilize message batching and archiving.
  • Chapter 6, API, discusses a rich API underpinning BizTalk Services. Also, it explains what it can do and how to use it in different contexts, including REST, PowerShell, and custom code.
  • Chapter 7, Tracking and Troubleshooting, discusses how messages are tracked in BizTalk Services and how to find and resolve problems when they occur using the tools BizTalk Services provides.
  • Chapter 8, Moving to BizTalk Services, explains how to move from BizTalk Server to BizTalk Services, the differences between the two products, and future plans.

And is addressed for software developers, IT pros, architects, and technical managers who wish to understand the main concepts of BizTalk Services.

Getting-Started-with-BizTalk-Services

Although the authors referring that knowledge of BizTalk Server is neither assumed nor expected, in my opinion is important and very helpful to have this prior knowledge of BizTalk Server to fully understand the book for a simple reason, this is not a step-by-step book that will describe all the steps that you need to do implement solution in BizTalk Services, it will not explain how to create a schema from scratch for example, because it is not the purpose of the book. Nevertheless the code samples provided with the book helps to really understand how to practically apply and strengthen the main concepts of BizTalk Services.

I especially liked the last chapter of the book “Moving to BizTalk Services” that address the challenges of moving BizTalk Server solutions on-premises to BizTalk Services. I have the pleasure to be a speaker in the last BizTalk Summit 2014 – London event along with Jon Fancey where he did a great presentation about “How to move to BizTalk Services” so I recommend you to watch this session after reading this chapter.

Although closely following BizTalk Services since the beta version, and speak about it in many technical sessions, I did learn a few things with the book… so I personally recommend this book to anyone interested in BizTalk Services or in integration technologies whether it be a beginner (with no BizTalk Server knowledge), in this case this case should consider this book as a guide and deepen their knowledge using other sources, or an expert. This book is a perfect start if you wish to start understand BizTalk Services.

Congratulations Karthik Bharathy and Fancey you did a great job!

Where you can order the book?

You can order the book and find the complete table of contents on Packt Website or in Amazon.

Let’s have a look to a little of inside information that will be in my upcoming Book about maps: demystify and clarify some features about the Table Looping Functoid.

First of all this functoid requires at least 3 inputs and a maximum of 100, in which the official documentation states that:

  • Parameter 1: A link from a repeating node in the source schema. The number of instances of this structure that occur in a particular input instance message defines the number of times that the associated table looping grid is processed.
  • Parameter 2: A constant input parameter that defines the number of columns in the associated table looping grid.
  • Parameters 3 – 100: A link from a node in the source schema or from another functoid, such as a Value Extractor functoid, or a constant input parameter. The relative order of parameters 3 – 100 is unimportant.

I think, at least for those who know the minimum about functoid, the last parameter(s), from 3 to 100, does not present any doubts. They are the values that we want to map from the source to the destination. And the order is not important because we need to define the Table Looping Grid property were we will define the order of appearance of these values.

The Table Looping Grid is basically a configurable table containing an arbitrary number of rows, configurable while editing the table, and a number of columns specified by the second input parameter of the Table Looping functoid. Each table cell is configured by using a drop-down list where the entries in the drop-down list are the values from to third through the last input parameters of the Table Looping functoid. These input parameters consist of a combination of the links into the functoid and any constant input parameters that have been defined. If link input parameters have been given a Label property value, that value is shown in the drop-down lists; otherwise, the value of the Link Source property is shown (generally, the former is friendlier than the latter). Constant input parameters are shown according to their constant value.

Important note: However the number of rows present in the Table Looping Grid are not defined in any input parameter from the Table Looping Functoid and in under no circumstances they are dynamic. The number of rows are statically defined during developing in order to apply a specific transformation rule and resolve a particular transformation problem.

Although people use this functoid correctly, especially if we are using it with a repeating record in the source schema, we will see in more detail further on in this chapter, is went we are trying to use this Functoid to transform a flat structure to a recursive structure that we realize that many developers don’t properly understand the first input parameter of this Functoid and to be honest, neither the explanation present in the official documentation will explain it correctly.

Although the Table Looping Functoid states that the first input must be a scoping element linked from a repeating group, that is not really true, in fact for me this description is complete false!

  • A link from a repeating node in the source schema – most common used but not entire true, you can also make use of a constant value, a simple node or an element.
  • The number of instances of this structure that occur in a particular input instance message defines the number of times that the associated table looping grid is processed. – I understand and in a way yes, but sometimes developers understand that this as the number of rows that they have to define in the table Looping Grid, with is not true, or that this input requires a number and in fact this is also not true.

So to be more clear, for me the best description of this parameter is:

  • Parameter 1: the first input parameter defines the action scope of the Table Looping Functoid and it can be defined by a link from a source tree node, repeating record, simple records or even elements, or by a constant value. This means:
    • If the scope is defined by a repeating record or repeating element, the Table Looping functoid will be created/execute in each occurrence of the record or element (in each iteration over the node). We can define this as multiple scope action.
    • If the scope is defined by a simple element or record (only occurs one time), than the Table Looping will be executed only one time. We can define this as simple scope action.
    • If the scope is defined by a simple constant value, regardless if it is an integer, alpha-numeric or string, than the Table Looping will be executed only one time. Again this is a simple scope action.

Table-Looping-Functoid-Scope-defined-by-a-simple-constant-value

Both this options are valid! More details will be found in my upcoming BizTalk Mapping Patterns and Best Practices free eBook.

Basically there are two properties inside the schema element decides whether an element can be absent from the document: Min Occurs and Nillable.

If Min Occurs property is set 0 then that element can be absent from the XML message but if it is set to 1 it has to be present though its value can be empty. This is useful to reduce the size of the document if only not all the elements are mandatory to end systems.

In other hand, if the Nillable property of the element is set to true, this will indicate that the value of an element in the document may be null. This NULL values will be expressed with xsi:nil = true attribute in the element, ex:

<IntExist xsi:nil="true" />

An element with the attribute xsi:nil = true explicitly means that the value is unavailable or unknown at that moment and sometimes the end system explicitly requires to be notified that the value of the element is NULL so that they can take appropriate action.

In this sample scenario we will have a 2 mandatory elements that can be nillable that we need to map to the destination schema. In this scenario all the destination elements are also mandatory and we need to fill them with a valid value or specify the nillable property as true: so if the element exist we need to map the correct source value otherwise we need to set the destination element as nillable.

The first element “DateExist” is a mandatory element that can be null. If null we need to set a null value in the destination element also as null, otherwise we need to map the source value. To accomplish that we need to:

  • Drag one IsNil Functoid from the Toolbox window onto the Grid.
    • Drag a link from the “NillValue” field element in the source schema to the IsNill Functoid
  • Drag one Nil Value Functoid from the Toolbox window onto the Grid.
  • Drag one Logical NOT Functoid from the Toolbox window onto the Grid.
  • Drag one Value Mapping Functoid from the Toolbox window onto the Grid.
  • To create a rule for mapping the value if the element is null
    • Drag a link from the IsNill Functoid to the Nil Value Functoid
    • Drag a link from the Nil Value Functoid to the “NillValueOutput” field element in the destination schema
  • Otherwise, To create a rule for mapping the value if the element different of null
    • Drag a link from the IsNill Functoid to the Logical NOT Functoid
    • Drag a link from the Logical NOT Functoid to the Value Mapping Functoid Functoid
    • Drag a link from the “NillValue” field element in the source schema to the Value Mapping Functoid
    • Drag a link from the Value Mapping Functoid to the “NillValueOutput” field element in the destination schema

Do the exact same logic for the second element present in the source schema.

BizTalk-Mapper-Working-With-Nillable-Values

Sometimes the maps are misunderstood and notorious for producing a lot of unnecessary code that may cause a in some cases lack of performance. So the question that we can and should ask is whether this is the best solution or not to address this type of operations. To respond this question we should also inspect the generated code produce by the BizTalk Mapper:

<xsl:variable name="var:v1" select="string(NillValue/@xsi:nil) = 'true'" />
    <xsl:variable name="var:v2" select="userCSharp:LogicalNot(string($var:v1))" />
    <xsl:variable name="var:v4" select="string(AnotherNilValue/@xsi:nil) = 'true'" />
    <xsl:variable name="var:v5" select="userCSharp:LogicalNot(string($var:v4))" />
    <ns0:OutputSchema>
      <xsl:if test="string($var:v1)='true'">
        <NillValueOutput>
          <xsl:attribute name="xsi:nil">
            <xsl:value-of select="'true'" />
          </xsl:attribute>
        </NillValueOutput>
      </xsl:if>
      <xsl:if test="string($var:v2)='true'">
        <xsl:variable name="var:v3" select="NillValue/text()" />
        <NillValueOutput>
          <xsl:value-of select="$var:v3" />
        </NillValueOutput>
      </xsl:if>
      <xsl:if test="string($var:v4)='true'">
        <AnotherNilValueOutput>
          <xsl:attribute name="xsi:nil">
            <xsl:value-of select="'true'" />
          </xsl:attribute>
        </AnotherNilValueOutput>
      </xsl:if>
      <xsl:if test="string($var:v5)='true'">
        <xsl:variable name="var:v6" select="AnotherNilValue/text()" />
        <AnotherNilValueOutput>
          <xsl:value-of select="$var:v6" />
        </AnotherNilValueOutput>
      </xsl:if>
    </ns0:OutputSchema>
  </xsl:template>

In fact is a pretty decent XSLT code but the reality is that it can be better, we don’t need to use any support variables and we can remove one if condition by replacing the xsl:if condition for one xsl:choose condition.

This is a very simple approach, easy to implement and readable that you should use even in small or large messages (transformations) but only if you have to deal with a small number of nillable element.

However applying this approach in transformation that will need to deal with a large number of nillable elements, can lead to two problems:

  • A lot of unnecessary XSLT code that can in fact and of course always depending in the size of the message can lead to some lack of performance
  • A lot of functoid shapes (4 Functoids) and links (7 links) for each element that can lead to lack of visual Readability

So can we improve this solution for transformations that needs to deal with a large number of nillable elements?

Well that’s the problem, there isn’t a simple solution for that. At the first look you may think that’s easy, just copy the XSLT code inside to a Scripting Functoid and optimize the XSLT code.

However by doing that you will receive an error:

error btm1050: XSL transform error: Unable to write output instance to the following <file:///C:\…\MapNillValuesWithCustomXSLT_output.xml>. Prefix ‘xsi’ is not defined.

The problem is that the nil attribute is defined in the XML Schema instance namespace, http://www.w3.org/2001/XMLSchema-instance (commonly associated with the prefix xsi) and this namespace is not declared by default in the XSL code generated by the BizTalk Mapper.

This namespace is automatically declare only if you use the Nil Functoids in the map.

So the normal solution here is… to bypass the BizTalk Mapper and generate an external XSLT code and add it to the map by specifying the Custom XSLT Path by:

  • Open the map
  • Click the grid zone and on the properties window there will be a “Custom XSLT Path” property.  Click the ellipses and navigate to the file containing the XSLT.

BizTalk-Mapper-Working-With-Nillable-Values-External-XSLT

You then can use a similar code to check and map the elements:

<xsl:choose>
  <xsl:when test="NillValue/@xsi:nil">
    <NillValueOutput>
      <xsl:attribute name="xsi:nil">
        <xsl:value-of select="'true'" />
      </xsl:attribute>
    </NillValueOutput>
  </xsl:when>
  <xsl:otherwise>
    <NillValueOutput>
      <xsl:value-of select="NillValue/text()" />
    </NillValueOutput>
  </xsl:otherwise>
</xsl:choose>

However applying this approach we have a major problem for me:

  • We lose all the BizTalk Mapper functionalities.
Workaround

Well, at least that I know, unfortunately there is no simple way to declared the xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance namespace to the stylesheet of the map.

However we can apply one small workaround, is not perfect but in most of the case it will solve my problems:

  • For only one of the nillable elements we need to use the Nil Functoids explained in the beginning of this post.
    • This will declare automatically the name xsi namespace for us.
  • In the rest of the elements we now can use Scripting Functoids with the optimized XSLT code described above

BizTalk-Mapper-Working-With-Nillable-Values-XSLT

I’m still working to find a better way but until then

You can download the source code from:

BizTalk Mapper: Working With Nillable Values (xsi:nil=”true”) (92.7 KB)
Microsoft | Code Gallery