BizTalk Server 2006 has around 80 functoids, or functions; these functoids are helpful while building your maps; more about that later. BizTalk Server 2006 Mapper IDE is the same as the VS.NET 2005 IDE; when you build a map, you will be inside the same environment that you are used to, and if you open a map, the functoids toolbox will display all the standard and custom functoids. The standard functoids are categorized in a neat way based on the functionality of each, but sometimes the functoids library you have is not enough, so you will opt to build your own functoid. In this article, I will give you an introduction to the BizTalk Server 2006 Mapper, and I will try to explain and boil down the standard functoids provided by BizTalk Server 2006, giving one practical example of the Database Lookup functoid. Then we'll go through building a custom functoid after we examine the architecture of custom functoids. The functoid we'll build is a Currency Converter functoid. I tried to propose a useful and reusable functoid that you may use later. This functoid is built upon a Web Service that provides currency rates. Also, you will learn how to install your functoid in the GAC (Global Assembly Cache), and add it to your toolbox. So let's start our journey with functoids.
Knowledge Prerequisites and Software Requirements
- BizTalk Server core principals; such as messaging, how to create schemas, maps, etc. I will try to explain these concepts as much as I can, but if you have prior experience, it will be great.
- WebServices core principals.
- Software requirements
- Visual Studio .NET 2005.
- BizTalk Server 2006; these are my versions, I think you can use VS.NET 2003 and BizTalk 2004.
BizTalk Server 2006 Mapper
BizTalk Mapper is just a tool to define the relationships between an input source schema and a destination schema. In other words, it binds elements, attributes, records, etc., to one another using a polished designer, right inside VS.NET 2005. You can directly connect a field element to any other schema element, or you can process the input data and send it to the destination element; the processing is maintained using functoids. For example, if the destination schema allows only a Name field element, while the source schema has two fields for the name, firstName and lastName, you can use the String Concatenate functoid that accepts the first and second names. It actually accepts 1 to 100 parameters. Then you can create a link from this functoid output to the destination schema element, which is Name in this case.
BizTalk saves a map with a .btm (BizTalk Map) file extension, and this is an XML based file. If you are curious, like me, open it in any XML editor or even in Notepad, and you will find out that everything about the map is stored inside it. You will find XML nodes and elements about everything on the design surface, including Links, Functoids, Input Parameters, Pages, Source and Destination schemas, etc. Pages are design pages that you use when your map gets sort of spaghetti and you need to organize your links. It's a good practice to keep direct links in one page and functoid based links in another page. This would be more comprehensive, and once your map is built, it gets converted to an XSLT file. A link is provided in the VS.NET output window and you can open the XSLT file and view its contents.
Fig. 01: BizTalk Server 2006 Mapper
Basically, maps are used for two purposes: Translation and Transformation of Messages. Translation is just about Message Format, while Transformation is all about Data. In other words, when you use a map to change the format of a message from one to another, you are doing Translation. Consider translating a message from a flat file format to XML format. Transformation is copying data between schemas using maps; if you have a source schema that contains a Salary data element, and we copy this salary value from the Human Resources department application to another department such as Finance, that is what we mean by Transformation.
BizTalk Server 2006 Standard Functoids
In this section, I will give a brief about the Standard Functoids included in the Functoids toolbox. Some functoids are self-explanatory while others somehow need some clarification:
||Common string operations, as trimming, extracting substrings, getting index of one string within another, case conversion, concatenation, etc..
||All string operations are 1-based index.
||+, -, *, /, %, Min, Max, Round, Square, Square Root, etc..
||Round is a bank or even rounding; 4.5 rounds to 4; all .5 numbers will give the nearest even number.
||>, <, =>, <=, !=, ||, &&, !, IsNil, Logical Date, Numeric, String, Existence
||Logical Date checks if input could be date; inputs may be string, numeric, boolean, and they are converted at runtime to boolean, while output is always boolean. Non-zero numeric inputs are considered true, while a zero represents false. If the inputs are strings, comparing inputs is a case sensitive comparison; so c is not equal to C.
||Date, Time, Date and Time, Add Days
||Time in 24 hrs format, and Add Days takes a date in ISO-8601 format.
||Octal, Hexadecimal, ASCII to Char, Char to ASCII
||Sine, Cosine, Tan, ArcTan, Logn, Log10, Ln, Pow, e, etc..
||Trigonometric functoids use radians not degrees.
||Cumulative Concatenate, Sum, Minimum, Maximum, Average; input should be link from a Record, Field Element, or Field Attribute node. A function of this category is used to process a recurring group of schema elements, using scope; default is 0 that manages the whole message.
||There is a second input parameter called Scope which determines the elements to be accumulated; it's an optional parameter. The default value is zero that accumulates the entire message.
||Contains database functoids and Cross Referencing Functoids. Database functoids are Database Lookup, Value Extractor, Error Return. All other functoids in this category are Cross Referencing Functoids. Error Return and Value Extractor functoids are used in conjunction with Database Lookup, which returns the first matching row from an ADO.NET recordset.
||Value Extractor is used to get a column value from the Database Lookup functoid output, and Error Return is used to hold an exception message thrown at runtime which can be written to the destination schema node.
||Value Mapping, Looping, Table Extractor, Assert, Iteration, Index, Mass Copy, Scripting, etc. The Mass Copy functoid is used to copy an element including all or some of its sub elements. On the other hand, Conditional Mapping is acting like Intermediate If or the famous IIF. Record Count, Table Looping, and similar looping functoids are used to manage an unpredictable number of repeating elements.
||Assert is used for the sake of troubleshooting; it makes sure that one value is true at runtime. Also, Scripting is important as it calls a script or compiled code when used.
Cross Referencing Functoids: these functoids use data stored in BizTalkMgmtDb. The configuration wizard creates these tables during configuration. You can find them in the BizTalkMgmtDB database. There are 9 tables prefixed with xref_, but the tables stay empty until you fill them out from 9 XML files. This will get the database tables filled in, and if you don't fill these tables, you will not be able to use this set of functoids. If you try, you will get exceptions. BTSXRefImport.exe is the import utility to copy data from XML documents to database tables. This document will show you how to import XML files: Importing Data for the Cross Referencing Functoids. Actually, MSDN is somehow unclear regarding setting up database tables for cross referencing functoids, and you will not find examples in MSDN with enough explanation. However, I didn't import these files into the database. I will try to do that later and post about it, but basically, cross referencing is used when you have, for example, an OrderID which travels from an application to another and it differs in these different applications, so you can hold the IDs in the management DB, and look up the destination ID in the management database. Suppose the Order Status in system A is OrderSent, it may be OrderSubmitted in System B; so you use cross referencing to lookup the new Status value of system B. Also, you will need to fill the XML files adhering to the schema supplied at MSDN before using the import tool. I couldn't find any way around to reset these tables. Any suggestions from you would be helpful, until Microsoft enhances the documentation of Cross Referencing.
As we have seen above, a functoid takes some input parameters; these parameters need to be configured, to inform the functoid how to operate based on the input parameters. In the following section, we'll see a simple example that looks up some data from a database, based on an ID passed. We'll use the Database Lookup Functoid, with Value Extractor, and we'll also add the Error Return functoid to the map to demonstrate how to use these three functoids together.
Let's say that you have a group of stores that are managed only by one headquarters, and this headquarters can send a PO to any affiliate that could get merchandise from a store specified by the headquarters. All communication is only between the headquarters and the affiliate, so the headquarters schema looks up the database by passing the StoreID from which the affiliate will collect the merchandise to a Database Lookup functoid, and then extracts data to be sent to the affiliate. We will start using the Value Extractor functoid to extract a specific field value from the returned recordset, and for handling unexpected errors like syntax and connection failures or timeout, we'll add an Error Return functoid that will write the exception details to the destination schema.
For the sake of simplicity, we'll use the Pubs database and we will lookup the Stores table.
You will need to create two schemas: Headquarter.xsd and Affiliate.xsd. You will need to create a new map and call it Affiliate.btm; details for the schemas and the map are shown in the following screen:
Fig. 02: Affiliate Map, source is the Headquarters schema and destination is the Affiliate schema
Now you should configure the functoids added to the map. For this example, we'll configure the database lookup functoid. You can double click it, or select the input parameters from the Properties window. You will get the modal dialog box Configure Functoid Inputs, and you will get a good description about the number of parameters to be added. If you try to add more, you will not be able to do so, as once you reach the number of allowed input parameters, the new input parameter button gets disabled.
Fig. 03: Configuration of input parameters for the Database Lookup Functoid
The first parameter is the expression to extract the lookup value from the source schema and it is built by the Mapper. The second is the database connection string, and it's strongly recommend to use Windows Integrated Security. The third is the table name, and fourth is the column name. You can configure the rest of the functoids easily. The Value Extractor functoid will take only the column name, it will extract the value of one column from the database lookup functoid.
After building schemas, maps, you need to build an input file to provide to the Affiliate map to be tested. We'll build a simple XML file and call it Headquarter.xml, and these are the contents of the file (included in code download):
<Store StoreID="StoreID_0" />
Now we need to provide this file to the map to let the map use it while testing. Right click Affiliate.btm from Solution Explorer and select Properties. This will open the map property pages; select TestMap Input File and browse to Headquarter.xml.
Now you are ready to test your map. Right click Affiliate.btm from Solution Explorer and select Test Map. After a few seconds, you will get some output messages at the output window, and you will get a link to the input and output files generated after testing the map; if you open the output file, it should be like this:
<Store StoreName="" Zip="">
As you can see, the output is empty since we don't have in the stores table a store ID value "StoreID_0". Now we will change the StoreID value in the source schema to "6380"; this is a value that exists in the Stores table. Now we can test again; this time you will get the details of the store ID 6380.
<Store StoreName="Eric the Read Books" Zip="98056">
<StoreAddress>788 Catamaugus Ave.</StoreAddress>
As you can see, the Database Lookup Functoid extracted all the fields for the input store ID; in case the recordset includes more than one record, the first matching record is the one that's used.
Now we'll force the Database Lookup functoid to throw an exception. Simply go to the configuration of the Database Lookup functoid and append 'X' to the database table name. Now the database table name is 'storesX'. Try to test the map again, and let's see what will happen. Open the output file and you will get this XML output:
<Store StoreName="" Zip="">
<ErrorMessage>Invalid object name 'storesX'.</ErrorMessage>
Since the table name is invalid, you will not get any data into the destination schema, and you will get a meaningful error message that explains the reason behind getting no data, so it's advisable to use the Error Return Functoid!
Custom Functoids Architecture
We are done with the standard functoids, and we will move to another stage of functoids: building a custom functoid. Basically, you opt for this solution when you don't find a standard functoid that covers your needs.
The functoid that we are going to build is a Currency Converter Functoid, and here we should first see some what the functoid should and shouldn't do, and when to use it and not use it. Actually, this has been an architectural argument among technology specialists. Some say maps are just about mapping and no business processing should be done inside maps, and all data processing should be done at orchestration, while others encourage and support processing data at map level, and their evidence is the standard Database functoids included in the functoids group. However, you should decide when to use a custom functoid and whether it will affect performance heavily. It's simply a matter of design and performance too, but with this Currency Converter functoid, I think it's helpful and it's right to encapsulate this functionality inside A reusable functoid. For the above example, we can send the store currency to the Affiliate and also the rate of the local currency in USD, supposing stores are distributed around the globe. Here is where the importance of our custom Currency Converter functoid comes to light! Actually, I have seen a lot of EAI solutions that require currency conversions.
Functoids are just .NET compiled code, included, of course, inside one or more assemblies. A custom functoid should inherit from a
BaseFunctoid class that's contained in the
Microsoft.BizTalk.BaseFunctoids namespace. This namespace maps to the assembly Microsoft.BizTalk.BaseFunctoids.dll, located in the Developer Tools folder under your installation folder. To create a custom functoid, we'll override some overridable methods in the base class.
Custom Functoids Design
Let's examine what we've done in the above Database Lookup Functoid. To know what we'll need to create our Currency Converter Custom Functoid, before you drag a functoid to a design surface, you first spot the category under which your functoid is located, then you find your functoid name, preceded with a 16x16 Bitmap. Once you move your mouse over it, you get a tooltip. You will need to supply a Description to your functoid. You also need to supply an ID, greater than 6000 as recommended by Microsoft. The description is so important and I strongly recommend that you elaborate as much as you can. I have tried to provide a description similar to standard functoid descriptions.
Once you place the functoid into the design surface, the user will start to provide Input Links, so we need to define the Minimum and Maximum number of input parameters. Functoids accept specified types of input parameters and outputs to specified types of schema elements, so we also need to define the acceptable Input Connection Types and Output Connection Types. At runtime, the mapper calls your functoid so you should provide a name to the mapper that identifies your functoid, and this is mandatory for functoids that are planned to be deployed into the GAC. You assign this name by calling
SetExternalFunctoinName, and this sets an External Name for your custom functoid.
That was for the class that will encapsulate the functoid as a component, but for the implementation, we will need to refer to the Web Service that will provide the currency conversion. Also, we will need to build a resource file to hold the strings and bitmaps required for the functoid.
You can check the Web Service at CurrencyConverter. We are going to add a web reference to this Web Service. The WSDL contract is available at the Currency Converter WSDL. As we'll deploy the functoid into the GAC, we need to provide a strong name key file to it, or to digitally sign our assembly, we'll need to create an SNK file, which we will discuss in the following section.
Currency Converter Functoid Development
In this section, we will build our functoid, we will write code, draw a bitmap, build a resource file, and create a new SNK file; this section is kind of a walkthrough.
Create a new Class Library project, and name it BusinessFunctoids. Rename the default class file to CurrencyConverter.cs. First, we will add all the normal web references we need to develop our functoid.
Adding the Required References
As mentioned earlier, our class will inherit from the
BaseFunctoid class. This class belongs to the Microsoft.BizTalk.BaseFunctoids.dll assembly. You can locate it at C:\Program Files\Microsoft BizTalk Server 2006\Developer Tools, or it should be under the Developer Tools installation folder. You can add a reference to this assembly by right clicking References from Solution Explorer and adding a reference to this assembly so you will be able to use the
To consume the CurrencyConverter Web Service, you will need to add a web reference to your project; you can do that by right clicking your BusinessFunctoids class library project and selecting Add Web Reference. This will bring the Add Web Reference window; you should enter the URI of the CurrencyConverter Web Service, which is http://www.webservicex.net/CurrencyConvertor.asmx?WSDL. Now you are ready to start developing your custom functoid.
Creating the Strong Name Key File
To deploy our Currency Converter Functoid into GAC, we need to digitally sign the assembly to expose it to the entire system; however, in VS.NET 2003, you have to run the Visual Studio Command Prompt from Visual Studio .NET Tools, and you could use the sn command; this still works in VS.NET 2005. To create a new snk file, open the VS.NET command prompt and type:
sn -k BusinessFunctoids.snk
This will create the snk file at the location where you run the command from; if you are at C:\ and you run your command, the file will be written to the C drive. Now you need to expose your snk file to the class library project; right click the BusinessFunctoids project from Solution Explorer, select Properties, select Signing Tab from the left panel, check Sign the assembly; from the dropdown list, you can browse and select the file you've created using the above command, or you can select New to create a new SNK file right from VS.NET 2005 and directly associate it to the project - the latter way is more elegant.
Building the Assembly Resource File
To provide resources to the Currency Converter functoid, we need to create a new resource file that will hold all the strings needed for the Currency Converter Functoid, and also it will hold a 16x16 bitmap icon for the functoid. One odd thing about the VS.NET resource designer is that it only supports string manipulations, and if you want to add a picture to the resource file, it will not allow you to do so, so I often use an external tool to add a bitmap to a resx file, like Resource Editor.NET, another stunning tool provided by a CodeProject community member! Open the resx file and supply these values, then add currencyicon.bmp to the resx file. The description tells everything about the functoid, how many parameters are required, and an explanation about the parameters.
|Use the Currency Converter Functoid to convert a field value from a source currency to another foreign currency. There are three input parameters: fromCurrency, toCurrency, input field; input currencies should be in ISO code format.
|Select currencyicon.bmp from Resource Editor.NET
CurrencyConverter Functoid Implementation
In this section, we'll write the code for our functoid. Our code is divided into two sections: first is the functoid constructor that calls the base constructor, and its objective is to define the functoid to the mapper toolbox. Let's examine the constructor code:
1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using Microsoft.BizTalk.BaseFunctoids;
5 using System.Reflection;
6 using System.Globalization;
7 using BusinessFunctoids.net.webservicex.www;
9 namespace BusinessFunctoids
11 12 13 14 publicclassCurrencyConverter : BaseFunctoid
16 17 18 19 public CurrencyConverter()
20 : base()
22 this.ID = 6500;
23 24 25 SetupResourceAssembly("BusinessFunctoids.BusinessFunctoidResources",
27 28 SetTooltip("IDS_CURRENCYFUNCTOID_TOOLTIP");
33 34 this.SetMinParams(3);
37 38 39 SetExternalFunctionName(GetType().Assembly.FullName,
41 42 this.Category = FunctoidCategory.Conversion;
44 45 this.OutputConnectionType = ConnectionType.AllExceptRecord;
The code is self-explanatory. In line 22, you supply the ID to our functoid. Code from 28-31 sets the resources required for the toolbox. 34-35 decide how many parameters are valid for the functoid. 39 sets the external function name, 42 assigns the category under which the functoid will appear, and 45-46 set the output and input connections types.
Line 25 calls the
SetupResourceAssembly base method that takes two parameters: the resource file name and the assembly.
The following section demonstrates the concrete functoid logic that converts an input field from one currency to another using the CurrencyConverter Web Service:
50 public string ConvertCurrencyField(string fromCurrency,
string toCurrency, string field)
52 decimal numericField,result = 0m;
53 double rate;
55 if (IsNumeric(field))
59 numericField =
60 BusinessFunctoids.net.webservicex.www.CurrencyConvertor ws_Converter;
61 ws_Converter =
63 rate = ws_Converter.ConversionRate(
68 result = Convert.ToDecimal(rate) * numericField;
71 catch (Exception ex)
73 throw ex;
76 return result.ToString();
The above method is the core method; it's the functoid that will process everything. It's designed to take three parameters: fromCurrency, toCurrenty, and field. The field is the amount or number to be converted, and you should consider that everything is a string in terms of the mapper. That's why we pass strings to our functoid. The mapper doesn't manipulate data types conversion; moreover, the arguments should be strings, and the return as well. The function should be public to let the mapper call it; from inside, you should check for the data type of the input arguments, it's sort of more data type verification because the schema validates the input values based on the schema definition.
The code is self-explanatory and you should know from the WSDL and the provided examples in the Web Service's website that it expects currencies in ISO format, and there is a currency enumerator that holds all the supported currencies. It's better to hold the code between a
try-catch block since we're calling an external Web Service. Lines 63-66 make a call to the Web Service passing the enumerated currency codes. Then we have the rate, which is the most important value we need. Lines 68-69 calculate the field in the destination currency as per the rate value, then we return the value after casting it to a string.
Congrats! You've finished developing your Currency Converter Functoid, and the remaining sections are focused on deployment and testing of the functoid.
Adding the Currency Converter Functoid to the Toolbox
When the mapper starts, it checks a specific folder to and loads any functoid assemblies inside this folder. This folder is the Mapper Extensions folder under the Developer Tools folder. You need to copy your functoid assembly BusinessFunctoids.dll to this folder. Now you can add a Currency Converter functoid to the VS.NET functoids toolbox. Open a map in VS.NET to get the Functoids toolbox, then from the Tools menu, select Choose Toolbox Items, select the Functoids tab, then browse to the Mapper Extensions folder in which you copied BusinessFunctoids.dll before. Select BusinessFunctoids and press OK. You will get the icon of the CurrencyConverter functoid in the lower part of the window, click OK, and focus will go to the Currency Converter functoid under the Conversion category.
Registering the Currency Converter Functoid in GAC
Currency Converter is a global functoid that will be called by the mapper, so we need to deploy it to the Global Assembly Cache using the VS.NET 2005 command prompt. Open the VS.NET 2005 command prompt and browse to the Mapper Extensions folder and enter the following to register the Currency Converter functoid into the Global Assembly Cache:
gacutil /if BusinessFunctoids.dll
If the process goes smoothly, you will get Assembly Successfully added to the cache; if you want to assure that the assembly is added to the GAC, browse to the Windows installation directory and then to the assembly directory, and try to spot the BusinessFunctoids assembly; if it's there, everything is fine, and the assembly is physically installed in the GAC.
Currency Converter Functoid Consumption
Now is the time to use the Currency Converter functoid. We'll create two schemas and one map. Suppose that an exporter is selling products to foreign importers, and the exporter is supposed to send the prices in the importer foreign currency. For this purpose, you will need to create a new empty BizTalk Server project, call it CurrencyFunctoid, and add a new schema file, and call it ExporterSchema.xsd, and rename the root node to ExporterOrder. Right click this node and select Insert Schema Node -> Child Record. Rename it to Order and set Min Occurs and Max Occurs properties to 1. Right click the Order node and select Insert Schema Node -> Child Record, rename it to Item and set Min Occurs to 1 and Max Occurs to * (unbounded), then add the following fields to the Item record:
Add a new schema file to the project, call it ImporterSchema.xsd, rename the root node to Order, right click this node, and select Insert Schema Node -> Child Record, rename it to Item, and set Min Occurs to 1 and Max Occurs to * (unbounded). Add the following fields to the Item record:
Create a new map to the project and name it CurrencyConversion.btm. Set the source schema to ExporterSchema and the destination to ImporterSchema; make a direct link from productCode to PId, and from quantity to quantity, then drag the Currency Converter functoid from the toolbox, it should be under the Conversion category. Once you place it on the design surface, you can get Configure Functoid Inputs either by double clicking it or by clicking the ellipsis button next to Functoid Inputs in the Properties window. In the configuration window, you will only be able to enter three parameters. Also, you will get the description you've entered before in the resource file in the Functoid description label. Close the window without adding any parameters, and drag the currency, foreignCurrency, and price nodes into the Curreny Converter Functoid, respectively. These will serve as the input parameters to the Currency Converter Functoid. Double click the functoid and it should look like this:
Fig. 04: Configuration of input parameters for the Currency Converter Functoid
Go to the design surface and drag a link from the Currency Converter functoid to the price element in the destination ImporterSchema; this will write the result into this element. After creating the map, it's ready to be tested. For testing, we'll use Map_test_Input.xml available in the code download. This is the content of the input XML message.
<Item currency="USD" foreignCurrency="EGP">
The input currency is USD and the destination currency is EGP (Egyptian pound). Suppose the exporter is in United States and the importer in Egypt; to provide this input test file to the map, right click CurrencyConversion.btm from Solution Explorer, select Properties, select TestMap Input Instance, and click the ellipsis button and browse to Map_test_Input.xml. Click Open and then OK. Right click the CurrencyConversion.btm map from Solution Explorer and select Test Map. The output window will show, and after testing the map, you will get a link to an output file in the output window. Press CTRL+Click to open it in VS.NET. You will get the following output message:
Congrats, the price is listed in foreign currency; if you divide 59.7272 by 10.4, you will get 5.743 which is the rate of USD against Egyptian pound.
Using the Code
The code download contains a solution CurrencyFunctoid, and three projects: the Class Library BusinessFunctoids that contains the CurrencyConverter functoid, CurrencyFunctoid that serves as a test application for the custom CurrencyConverter functoid, and FunctoidConfigurations which is the Database Lookup functoid example we saw above.
Points of Interest
For our Currency Converter functoid, we only considered a GAC functoid that should be deployed to the GAC; however, we have another option to build a functoid called Inline Script Functoid. In this approach, you expose your functoid code, that's .NET code, to the mapper, and during runtime, the Mapper embeds the functoid code into the map. While this has the advantage of being independent in the GAC assembly, it has the disadvantage that you need to embed the code into the map.
The Currency Converter Functoid depends on the Currency Converter Web Service; if the interface changes, which is rare, you should take care about that.
In this article, you learnt how to use standard functoids, and we went through an example about consuming the Database Lookup functoid. Also, we built a GAC Currency Converter custom functoid that consumes a Currency Converter Web Service.