Posts Tagged ‘Tips and Tricks’

If you are following Logic Apps since its beginning, you must love the new Logic Apps Designer! That is a little closer to his on premise brother (BizTalk Orchestration Designer). I am saying this because now, finally, the new designer uses a top-down approach instead of a left-right approach that existed in the previous version of Logic Apps.

01-Logic-Apps-Designer-left-right-approach

Besides this big improvement in the Visual Flow (top-down approach instead of a left-right approach), the designer is being constantly improved with a several new capabilities such as:

  • Managed Connectors, search capabilities within the designer
    • Adding API Apps to a logic app is now more intuitive
  • We now also have the ability to collapse and expand the tiles/shapes
  • We have a first class experience with the HTTP + Swagger connector within the designer
  • If-else conditions within the designer itself. No need to open up the code view to look into the conditions box.
  • Call nested Logic Apps within the designer
  • And many more, the list go on and on!

02-Logic-Apps-Designer-top-down-approach

In this post we will provide some tips and tricks in working within the new Logic Apps Designer, in terms of actions that we can make on it, and describe some of its behavior

Adding actions (tiles or shapes) …

Adding actions in the middle of the existing flow is not allowed, at least for now. We are only able to add actions on the bottom of our Logic App flow or at the bottom of each condition branches.

To add an action or condition in the Logic App flow: we need to click on the plus sign, and then choose “Add a condition” or “Add an action

09-Logic-Apps-Designer-add-action-or-condition

To add an action inside condition branches, we need to click in “Add an action” button that is present on the top right corner of the condition branch

10-Logic-Apps-Designer-add-action-condition

  • We can add several actions inside a branch;
  • Nested conditions, at least for now, are not allowed;
  • And we can only add actions on the “If no, do nothing” branch, if we have at least one action in the “If yes” branch;

Deleting actions (tiles or shapes) …

If we take in consideration the flow in the picture above, you will notice 4 actions will have “…” (three dots) option in the tile. This means that we can take an action on them, in this case the “Delete” option:

03-Logic-Apps-Designer-Delete-option-action

However, in this sample the other two tiles/shapes available, “Search tweet” and “Condition” doesn’t have this option and we cannot easily delete this action or step.

The reason is because:

  • Their outputs are being used by other actions
    • In this case the “Search tweet” output is being used as input on the “Condition”, “Create File” and “Send approval email”
  • or they contain child action
    • The “Condition” tile as one “child” action in each branch;

04-Logic-Apps-Designer-Cannot-Delete-action

If we remove the “Search tweet” output being used as input on the “Condition”, “Create File” and “Send approval email” or remove these actions, we will now be able to delete the “Search tweet” action

05-Logic-Apps-Designer-Cannot-Delete-action-fixed

Moving actions (tiles or shapes) …

Noticed that you make a mistake and forgot to add an action between previous ones! Well, adding actions in the middle of the existing flow is not allowed, at least for now. However, we have the possibility to add and action in the end and drag and drop to another position inside your flow (arranging/modifying shape dispositions) … within certain conditions…

Again, if we take in consideration the Logic App that we are working above. We have several shapes, “Condition”, “Create file” and “Send approval email” that is consuming the output of a previous action, the “Search tweet

06-Logic-Apps-Designer-movind-Dependecies

If we try to move the “Condition” shape on top of the “Search tweet” shape:

11-Logic-Apps-Designer-moving-condition

We can’t move because it depends on the “Search tweet” action. However, if we try to do the same for the “Dynamic CRM Online – Create a new record”, that is still to be configured, i.e., it doesn’t have any dependency from previous ones

07-Logic-Apps-Designer-movind-no-Dependecies

We will then be able to drag and drop to any place of our flow

08-Logic-Apps-Designer-movind-no-Dependecies-1

08-Logic-Apps-Designer-movind-no-Dependecies-2

The only exception is that you cannot put on top of the trigger, in this case, the “Recurrence” shape.

In summary, you can move shapes only to:

  • Underneath the Logic Apps trigger;
  • Underneath their dependencies;
Moving actions inside conditions

Yes, we can move actions into the conditions branches, between them or remove them from within the conditions branches… again, within certain conditions regarding mainly the “If yes” branch:

  • If we have only one action inside the “If yes” branch
    • We cannot remove this action to outside the “If yes” branch, if the “If no” branch contains any action;
    • We need to delete or remove the shapes inside the “If no” branch to be able to move/remove the action inside the “If yes”;

The reason that this happens is because, the design doesn’t allow you to add action in the “If no” branch until you have an action inside the “If yes” branch

  • If we have several actions inside the “If yes” branch
    • We cannot remove the first action to outside the “If yes” branch, if the “If no” branch contains any action;
    • We need to delete or remove the shapes inside the “If no” branch to be able to remove the action inside the “If yes”;
    • Or we need to move the first action to another position inside the “If yes” branch and then move it to another position inside your flow (outside the “If yes”);

The reason that this happens is because, the first action inside the “If yes” is the “trigger” that allows you add actions in the “If no” branch… so it will be deep tied to the “If yes” branch and the design will not allow it to be moved

  • If we don’t have any action inside the “If yes” branch
    • We cannot move or add an action to the “If no, do nothing” branch;
    • We need to first add an action in the “If yes” branch;

The reason that this happens is because we need to have at least one action inside the If yes” branch to be able to add or move actions to the “If no, do nothing” branch – this is a default behavior of the design. The first action inside the “If yes” is the “trigger” that allows you add or move actions in the “If no, do nothing” branch.

It’s always good to analyze code performed by others, please do not consider this a criticism, it is not my intention. Doing that you will, compare technics, learn new things, noted and we become aware of how some transformation rules are made inside the maps… and sometimes, most often when things are done in the wrong way, or not quite correct, we gain inspiration or idea to talk about it. And this is one of them, that in fact, I have seen often happening: Applying conditions (if-then-else) using sometimes complex Functoid chain.

So far nothing new, this is a trivial operation that we normally, or often, do. The thing is that often we do it wrong or not quite well.

I have already addressed the processing model of BizTalk maps on my BizTalk Mapping Patterns and Best Practices book but it is always useful to refer again. BizTalk maps follows the model below:

  • The BizTalk mapping engine traverses the destination schema from beginning to end;
  • The mapping rules are constructed and executed as links are encountered in the destination schema;
  • The information is extracted from the source when a link is encountered in the destination schema.

But you need to be extremely aware, and this is important, that when BizTalk mapper engine reach to a condition rules to construct, all the operation that are present downstream of the condition needs to be translated (executed) before the condition rule itself.

Let’s look to the following example:

Complex-Functoid-Chain-Condition-wrong

Where the basic rule that we want to implement is:

  • If the operation is equal to “insert”
    • Then we need to set, in the Cross Referencing tables, and return an identifier (Set Common ID Functoid), based on some content from the source schema and in some values present or not in the Cross Referencing tables (Get Common ID Functoid), and mapped to the “CommonId” element in the destination schema
  • If the operation is different of “insert”
    • Then we need to retrieve an identifier from the Cross Referencing tables (Get Common ID Functoid), again based on some content from the source schema, and mapped the result to the “CommonId” element in the destination schema

So, to be simple and clear, what the BizTalk mapper engine is doing in the picture above is:

  • It found 2 rules to translate, because we have two links connected with the “CommonId” element
  • In the first rule it will: If the operation is equal to “insert”
    • Get one element from the “keys” record in the source schema and put it into a variable
    • Try’s to retrieve two identifiers’, based on some content from the source schema or static data.
    • From the identifiers’ retrieve earlier, it applies some transformation rule (not important for this demo)
    • Set and return an identifier, based on the content of:
      • one element from the “keys”
      • and the transformation rule, associated to the identifiers’ retrieve earlier
    • And finally, will check if the operation is equal to “insert”
      • Then map the value of the identifier created in the previous step to the element “CommonId”
  • In the second rule it will: If the operation is different of “insert”
    • Try’s to retrieve two identifiers’, based on some content from the source schema or static data.
    • From the identifiers’ retrieve earlier, it applies some transformation rule (not important for this demo)
    • And finally, will check if the operation is different to “insert”
      • Then map the value of the identifier created in the previous step to the element “CommonId”

Are you already seeing the problem with this sample?

Let’s analyze the generated XSLT code and try to see if it makes more sense and detect the problem in a clear way:

<xsl:variable name="var:v14" select="userCSharp:LogicalNe(&quot;insert&quot; , string(@operation))" />
<xsl:variable name="var:v23" select="userCSharp:LogicalEq(&quot;insert&quot; , $var:v22)" />
<xsl:variable name="var:v18" select="ScriptNS1:GetCommonID(&quot;INPUT1&quot; , &quot;INPUT2&quot; , string($var:v17))" />
<xsl:variable name="var:v19" select="ScriptNS1:GetCommonID(&quot;INPUT1&quot; , &quot;INPUT3&quot; , string(keys/myelement/text()))" />
<xsl:variable name="var:v15" select="ScriptNS0:GetValue()" />
<xsl:variable name="var:v20" select="ScriptNS2:GetValue(string($var:v15) , string($var:v18) , string($var:v19))" />

<xsl:if test="string($var:v14)='true'">
     <xsl:variable name="var:v21" select="string($var:v20)" />
     <btsCommonId>
            <xsl:value-of select="$var:v21" />
     </btsCommonId>
</xsl:if>

<xsl:variable name="var:v25" select="ScriptNS1:SetCommonID(&quot;FamilyMemberVehicle&quot; , &quot;CRM&quot; , $var:v24 , string($var:v20))" />

<xsl:if test="string($var:v23)='true'">
     <xsl:variable name="var:v26" select="string($var:v25)" />
     <btsCommonId>
            <xsl:value-of select="$var:v26" />
     </btsCommonId>
</xsl:if>

And now, do you see the problem with this sample?

The problem is that independent of the type of operation, if is an insert or other operation, it will always set an identifier in the Cross Referencing tables! (which by the way in my scenario, will induce problems – violate key). And this happens because the condition was defined at the end of the rule (in the right corner)

Complex-Functoid-Chain-Condition-problem

How you should read the rules

Important Note: basically, there are some exceptions, you always need to read the rules:

  • from top to bottom in the order that they happier in the destination schema
  • and from the left to the right (from the source schema to the destination schema) for a specific link connected to an element, field or record in the destination schema;

So, in this case the solution here is very simple, we need to move the condition to a position further downstream, which will allow to be executed sooner, especially the condition is equal to “insert”, because the second part (get two identifiers’ from the Cross Referencing tables) is common for both rules (then and else).

In this case we need to place the condition before we execute the set and return an identifier from the Cross Referencing tables operation, as the picture bellow shows:

Complex-Functoid-Chain-Condition-right

If we check the XSLT code once again, now we will notice that:

  • We are doing all the common operations before the condition rule to e executed
  • And for each particular scenario we will map the result (not equal to “insert”) or apply more operations and map the result (equal to “insert”)
<xsl:variable name="var:v14" select="userCSharp:LogicalNe(&quot;insert&quot; , string(@operation))" />
<xsl:variable name="var:v18" select="ScriptNS1:GetCommonID(&quot;INPUT1&quot; , &quot;INPUT2&quot; , string($var:v17))" />
<xsl:variable name="var:v19" select="ScriptNS1:GetCommonID(&quot;INPUT1&quot; , &quot;INPUT3&quot; , string(keys/myelement/text()))" />
<xsl:variable name="var:v15" select="ScriptNS0:GetValue()" />
<xsl:variable name="var:v20" select="ScriptNS2:GetValue(string($var:v15) , string($var:v18) , string($var:v19))" />
<xsl:variable name="var:v23" select="userCSharp:LogicalEq(&quot;insert&quot; , $var:v22)" />

<xsl:if test="string($var:v14)='true'">
     <xsl:variable name="var:v21" select="string($var:v20)" />
     <btsCommonId>
            <xsl:value-of select="$var:v21" />
     </btsCommonId>
</xsl:if>

<xsl:if test="string($var:v23)='true'">
     <xsl:variable name="var:v24" select="string($var:v20)" />
     <xsl:variable name="var:v25" select="string(keys/siva_viaturaagregadoid/text())" />
     <xsl:variable name="var:v26" select="ScriptNS1:SetCommonID(&quot;FamilyMemberVehicle&quot; , &quot;CRM&quot; , $var:v25 , string($var:v24))" />
     <btsCommonId>
            <xsl:value-of select="$var:v26" />
     </btsCommonId>
</xsl:if>

Like this scenario there are several, more or less complex, more or less critical. Some of them we don’t “even notice” because everything works well but if we check careful, sometimes will make several unnecessary operations that can induce, or may induce, performance problems in our transformations.

Good practices

As a reference and good practice, when you are implement conditions using Functoids chains, you should make the conditions as early as possible, i.e., put the conditions (if-then-else) as possible as you can in the left side of the Functoid chain.

This will allow you:

  • To group and execute certain task only on the context of the condition (inside the <xsl:if> statement)
  • And better performance because we are reducing the “noise” produced in the XSLT generated code by the compiler.

Easy, isn’t it?
Hope you enjoy it.

In the last days I’ve been migrating old maps from BizTalk Server 2004 to the last versions of BizTalk Server: 2013 and 2013 R2 and I have seen several “styles/approaches” to address mappings, some of them great, some of them not so much and other completely crazy.

Today I will address a topic that I, unfortunately, have seen constantly: Reusing Scripting Functoids with Inline C# inside the same map. This is also something that I address in my book: “BizTalk Mapping Patterns & Best Practices

Inline scripts are convenient for custom code that you are unlikely to use elsewhere in your application or other maps. In addition to being convenient for one-time scripts, inline scripts are also useful for declaring global variables for use among a number of scripts and to use several times inside the same map.

In general, some of the main reasons to use Scripting Functoid are:

  • Perform functions otherwise not available with built-in Functoids
  • It also allows you to perform complex logic transformations that are impossible to make with built-in Functoids.
  • To simplify the map, making it sometimes more easy to read, instead of using a complex Functoid chain
  • Or simply to optimize the transformation rules

However, you need to have some precautions when using, or reusing the same, Custom Inline C# scripts inside the Scripting Functoid:

  • Function Names Limitation
  • Compiler limitations
  • Reusability
Function Names Limitation

For each function/method inside the Scripting Functoid you need to give different Method statements (name of the method + method parameters). If you have the same Method statement with different behaviors inside, for example:

  • In the first Scripting Functoid:
public string FormatDate(string inputDate, string inputFormat, string outputFormat)
{
    System.DateTime date;
    if (System.DateTime.TryParseExact(inputDate, inputFormat, 
        System.Globalization.CultureInfo.InvariantCulture, 
        System.Globalization.DateTimeStyles.AssumeLocal, out date))
    {
        return date.ToString(outputFormat);
    }
    return "";
}
  • And in the second Scripting Functoid:
public string FormatDate(string inputDate, string inputFormat, string outputFormat)
{
    if (String.IsNullOrEmpty(inputDate))
        return System.DateTime.Now.ToString(outputFormat);
    return inputDate;
}

Different-Scripting-Functoid-With-The-Same-Name-Not-Ok

Because both methods have the name and the same number of parameters, the mapper will interpret has the same, regardless if the code inside the functions are the same of not. In this case the first mapping rules to be reached is the first scripting functoid, which means that the second code inside the second functoid will be ignored and instead will be executed the exact same code that the first one, which is not what intended to do.

You can validate this map behavior if you validate the map and see the XSL produced:

<msxsl:script language="C#" implements-prefix="userCSharp"><![CDATA[ 
public string FormatDate(string inputDate, string inputFormat, string outputFormat) 
{
 System.DateTime date;
 if (System.DateTime.TryParseExact(inputDate, inputFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AssumeLocal, out date))
 {
    return date.ToString(outputFormat);
 }
 return "";
} 
]]></msxsl:script>

To fixed this we need to give a different name to the second method, for example:

public string ValidateAndFormatDate(string inputDate, string inputFormat, string outputFormat)
{
    if (String.IsNullOrEmpty(inputDate))
        return System.DateTime.Now.ToString(outputFormat);
    return inputDate;
}

Nevertheless, you can give the same method name, if the method has a different number of inputs, for example this case is a valid example:

  • In the first Scripting Functoid:
public string FormatDate(string inputDate, string inputFormat, string outputFormat)
{
    System.DateTime date;
    if (System.DateTime.TryParseExact(inputDate, inputFormat, 
        System.Globalization.CultureInfo.InvariantCulture, 
        System.Globalization.DateTimeStyles.AssumeLocal, out date))
    {
        return date.ToString(outputFormat);
    }
    return "";
}
  • And in the second Scripting Functoid:
public string FormatDate()
{
    return System.DateTime.Now.ToString();
}

Different-Scripting-Functoid-With-The-Same-Name-Ok

Once again if you validate the map to see the XSL produced, you can see that in this case both functions are being generated:

 <msxsl:script language="C#" implements-prefix="userCSharp"><![CDATA[ 
public string FormatDate(string inputDate, string inputFormat, string outputFormat) 
{
 System.DateTime date;
 if (System.DateTime.TryParseExact(inputDate, inputFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AssumeLocal, out date))
 {
   return date.ToString(outputFormat);
 }
 return "";
} 
public string FormatDate() 
{
 return System.DateTime.Now.ToString(); 
} 
]]></msxsl:script> 
Compiler limitations

When you create a script, for example a C# function, remember to give it a correct name because all scripts with the same name are treated as one by the compiler regardless of whether the code is equal or not.

You also have to take in consideration that BizTalk Mapper engine will save the inline scripts in the Extensible Stylesheet Language Transformations (XSLT) Stylesheet defining the map in the within the msxsl:script element. The following namespaces are supported by default: System, System.Collection, System.Text, System.Text.RegularExpressions, System.Xml, System.Xml.Xsl, System.Xml.Xpath, Microsoft.VisualBasic. You can add support for additional namespaces outside the listed using the namespace attribute of the child element, <msxsl:using>, of the <msxsl:script> element, but this will force us to use an External Custom XSLT file to accomplish the transformation. See more here: XSLT Stylesheet Scripting Using <msxsl:script> (http://msdn.microsoft.com/en-us/library/533texsx%28v=vs.110%29.aspx)

Reusability

Now let go to the main topic of this post Reusability of Scripting Functoids with Inline C# inside the same map.

What I normally see is people doing two things:

  • Copy and paste the entire method to all the Scripting Functoids – I’m calling that: Reusing in a Bad Way
    • In this case both scripting functoids have the exact same code
public string FormatDate(string inputDate, string inputFormat, string outputFormat)
{
    System.DateTime date;
    if (System.DateTime.TryParseExact(inputDate, inputFormat, 
        System.Globalization.CultureInfo.InvariantCulture, 
        System.Globalization.DateTimeStyles.AssumeLocal, out date))
    {
        return date.ToString(outputFormat);
    }
    return "";
}

Reuse-Scripting-Functoid-Bad-Way

This is a very simple case, now imagine a map with several grid pages and several functoids. The problem is this approach, as we explained earlier, is that if we for some reason change the code of one the scripting functoids, that is not the first to be translated by the mapper engine, the changes will not take effect!!!

  • Copy and paste the entire method to all the Scripting Functoids and give it a different name! – I’m calling that: Reusing in a Crazy Way
    • In this case the first scripting functoids have:
public string FormatDate(string inputDate, string inputFormat, string outputFormat)
{
    System.DateTime date;
    if (System.DateTime.TryParseExact(inputDate, inputFormat, 
        System.Globalization.CultureInfo.InvariantCulture, 
        System.Globalization.DateTimeStyles.AssumeLocal, out date))
    {
        return date.ToString(outputFormat);
    }
    return "";
}
  • And the second one have:
public string FormatDate2(string inputDate, string inputFormat, string outputFormat)
{
    System.DateTime date;
    if (System.DateTime.TryParseExact(inputDate, inputFormat, 
        System.Globalization.CultureInfo.InvariantCulture, 
        System.Globalization.DateTimeStyles.AssumeLocal, out date))
    {
        return date.ToString(outputFormat);
    }
    return "";
}

The one difference is that the first one is call FormatDate and the second FormatDate2. I’m not joking, I saw this approach being used nearly 8 times (Func1, Func2, Func3, Func4, … Func8) in a map! Of course this solve the problem of the first approach but now you have another! The artifact size deployed will be larger, not critical, but if you can avoid is better. This will happen because in this case all method will be embed in the XSL file that will be deployed to your environment.

  <msxsl:script language="C#" implements-prefix="userCSharp"><![CDATA[ 
public string FormatDate(string inputDate, string inputFormat, string outputFormat) 
{
 System.DateTime date;
 if (System.DateTime.TryParseExact(inputDate, inputFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AssumeLocal, out date))
 {
    return date.ToString(outputFormat);
 }
 return ""; 
} 
public string FormatDate2(string inputDate, string inputFormat, string outputFormat) 
{
 System.DateTime date;
 if (System.DateTime.TryParseExact(inputDate, inputFormat, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.AssumeLocal, out date))
 {
    return date.ToString(outputFormat);
 }
 return ""; 
} 
]]></msxsl:script>

Another problem, even worse, of this approach is that, if you need to make some changes in the method… you need to do the same changes in every single functoid! If you want them to work in a coherent and transparent way.

So what is the best way to reuse Scripting Functoids with inline C#?

Easy! You need to remember that when you have two or more Custom Inline C# scripts with the same Method statement (name of the method + method parameters), the compile will only take in consideration the first one linked in the map, even if the others have a different code inside they are ignored by the compiler.

Reuse-Scripting-Functoid-Good-Way

When we use Custom Inline C# scripts the best way to implement reusability is to specify the body function in only one Scripting Functoid and the remaining ones specify only the function declaration, in this sample in the first Scripting Functoid will have the following code:

public string FormatDate(string inputDate, string inputFormat, string outputFormat)
{
    System.DateTime date;
    if (System.DateTime.TryParseExact(inputDate, inputFormat, 
        System.Globalization.CultureInfo.InvariantCulture, 
        System.Globalization.DateTimeStyles.AssumeLocal, out date))
    {
        return date.ToString(outputFormat);
    }
    return "";
}

And the second one only the following declaration:

public string FormatDate(string inputDate, string inputFormat, string outputFormat)

Easy, isn’t it?
Hope you enjoy it.

You can download this tool here:

BizTalk Mapper Tips and Tricks: How To Reuse Scripting Functoids with Inline C# (18,6 KB)
Microsoft | Code Gallery

Have you had the time to think about what is some features like RosettaNet, ESB or UDDI have in common?

Well, all of them have custom databases and all of them are optional features.

But the most important question here, because they have custom databases, is: Do you think that these databases are being backedup? And the data inside are saved?

And the response is NO… Because these “custom” databases are not installed with BizTalk Server, they are not included in the default list of databases to be marked and backed up by the Backup BizTalk Server job. So if you want the Backup BizTalk Server job to back up RosettaNet, ESB or UDDI custom databases, you must manually add the databases to the Backup BizTalk Server job.

BizTalk-Server-Default-Database-backups

Fortunately for us, Microsoft provides two SQL Scripts:

  • Backup_Setup_All_Procs.sql
  • Backup_Setup_All_Tables.sql

That can be found in the Schema folder inside the BizTalk installation folder: “C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Schema”, that you need to run against these databases in other to extend the standard backup mechanisms. However you also need to modify the adm_OtherBackupDatabases table (present in the BizTalk Management (BizTalkMgmtDb) database) to include a row for each these custom databases.

How to Back Up Custom Databases (RosettaNet, ESB or UDDI databases)

Note: I will not advise you to backup any custom application databases (database used to support BizTalk Application processes or others) with the Backup BizTalk Server job, with the exception of custom BizTalk database, like RosettaNet, UDDI or ESB.

To accomplish that you need to:

  • Browse to the “C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Schema” directory, and then run against the RosettaNet, ESB or UDDI (custom) databases the following SQL scripts.
    • Backup_Setup_All_Procs.sql
    • and Backup_Setup_All_Tables.sql

BizTalk-Server-run-SQL-scripts-to-backup-custom-databases

  • So in this case we need to run these two SQL scripts against the following databases
    • RosettaNet
      • BTARNARCHIVE
      • BTARNCONFIG
      • BTARNDATA
    • ESB Toolkit
      • EsbExceptionDb
      • EsbItineraryDb
    • UDDI
      • UDDI3

Note: This creates the necessary procedures, table, and role and assigns permissions to the stored procedures.

  • Modify the adm_OtherBackupDatabases table, present in the in the BizTalk Management (BizTalkMgmtDb) database, to include a row for each of your custom databases
    • Type the new server and database names in the corresponding columns
      • DefaultDatabaseName: The friendly name of your custom database.
      • DatabaseName: The name of your custom database.
      • ServerName: The name of the computer running SQL Server.
      • BTSServerName: The name of the BizTalk Server. This value is not used, but it must contain a value nonetheless.

BizTalk-Server-adm_OtherBackupDatabases-table-configuration

The next time you run the Backup BizTalk Server job, it will back up your custom databases.

BizTalk-Server-with-custom-Database-backups

See this and more tips here: BizTalk Server Tips & Tricks for Developers and Admins (Deep Dive)

Probably this was one of the most talked and funny tips and I completely forgot to publish in my blog despite the resources despite the resources are already available for download and referenced in the session slides that you can found here.

If you are familiarly with the BizTalk Innovation Day or BizTalk Summit’s, you will all remember that at some point my dear friend Tord Glad Nordahl complaining in his session about BizTalk Developers writing unnecessary information in the Application Event Log and for they do not use the Event Viewer. You can also check his post and his point of view about this topic: BizTalk + Event log = angry admins

My goal here is not to criticize those who use the event viewer or if there is better way to accomplish this task (monitor/logging BizTalk applications errors)… but I partially have to agree with Tord and say that… you shouldn’t write custom application errors, warnings or information in the Application Event Log.

Many times BizTalk developers like to write information’s that will help them tracking and debugging there orchestrations like:

  • Message received
  • Message successfully Transformed
  • Message sent to external system

custom-source-logs-logged-application-event-log

And for that they normally use the following code:

System.Diagnostics.EventLog.WriteEntry("AnnoyingTord",
               "Just an update Tord! Message successfully Transformed");

The problem using this code is that by default these information is being logged in the Application Event Log. You need to realize that Application Event Log holds almost all the important information related to different aspects in BizTalk – SQL, IIS, BizTalk infrastructure and runtime problems – it is one of the main places that admins used to monitor “applications” installed in your server/machine. And these is the information that is extremely important for BizTalk Administrator in order to monitor the wellbeing of the platform and to diagnose problems and you don’t want to infest this event log with irrelevant and unnecessary information at the point to be almost impossible to find real problems – instead you, or in this case the admins, should keep it clean.

So I told – “I partially have to agree…” – because I think that this are unnecessary information that are being logged in the Application Event Log that doesn’t provide any additional information to BizTalk Administrators but…

But I told – “I partially have to agree…” – because, instead, you can use a custom event log for logging that unnecessary information that doesn’t provide any additional information to BizTalk Administrators and in this case I really don’t care if you are using the Event Viewer to log BizTalk application errors or tracking or debugging information (despite I don’t agree this last part).

So you can use the Event viewer as long as you do not use the Application Event Log to write custom errors.

Building the Sample

In this sample you will find a simple orchestration that will receive any XML message and will log some traditional tracking information that developers normally do in their orchestrations… I call this trying to annoying Tord Glad Nordahl (my friend and one of the best BizTalk Administrator that I know):

Trying-to-Annoying-Tord

The source code can be found and download on MSDN Code Gallery:
BizTalk Server: Moving Event Source To Different Event Log (Administration) (152.2 KB)
MSDN Code Gallery

 

What the Admin does normally?

When facing this type of development, BizTalk Administrators normally ask the developer’s to change their code, to not write in the application log or to disable this type of logging/tracking. Code that already is deployed in all the environments.

However change is hard – Getting others to change can be impossible or a big challenge – Developers will try to find a thousand excuses for explaining why such information is important!

What the Admin should do?

My advice:

• Let the developer by happy by writing in the Event Viewer

But take back the control of your environment by easily creating or using PowerShell

With this script you can easily move an Event Source to a different or to a Custom Windows Event Log:

foreach ($LogSource in $LogSources) {
    Remove-EventLog -Source $LogSource
} 

$logFileExists = Get-EventLog -list | Where-Object {$_.logdisplayname -eq $LogName}
if (! $logFileExists) {
    $LogSources | %{
        New-EventLog -LogName $LogName -Source $_
    } 

    # Compose Key:
    $LogPath = 'HKLM:\SYSTEM\CurrentControlSet\services\eventlog\'+$LogName;
    if(Test-Path $LogPath)
    {
        $acl = Get-Acl $LogPath
        $GpOrUsers | %{
            $ace = New-Object System.Security.AccessControl.RegistryAccessRule $_,'WriteKey, ReadKey','allow'
            $acl.AddAccessRule($ace)
            #Set-Acl $LogPath $acl
        }
    }else{Write-Error "Cannot acesss log $LogName"}
}
else {
    $LogSources | %{
        New-EventLog -LogName $LogName -Source $_
    }
}

moving-source-logs-to-another-event-log

This way, you as an admin can take back the control of your environment and fix the blunders (or foolishness) of the developers – if you are a BizTalk developer, don’t be offended please, I’m also a BizTalk Developer.

The script can be found and download on Microsoft TechNet Gallery:
BizTalk DevOps: Moving an Event Source To a Different/Custom Windows Event Log (4.2 KB)
Microsoft TechNet Gallery

 

Again If you are a developer and you for some reason want to write “tracking” or error information in the Event Viewer, then you should start to optimize your code to write by default in a custom Event log. You can use for example a similar code:

string logName = “MyCustomEventLog”;
string logName = “MyProjectLogSource”;

if (!System.Diagnostics.EventLog.SourceExists(logName))
{
   System.Diagnostics.EventLog.CreateEventSource(projectName, logName);
}
System.Diagnostics.EventLog.WriteEntry(projectName, genericString.ToString(), logType);

As most of you already know, last month I presented a session at BizTalk Summit 2015 London event about “BizTalk Server tips and tricks for developers and admins” (You can check the video recording of my session here)

It was a lightweight session (30 minutes) about useful tips that we can use in our daily work, because it was a small session I didn’t had enough time to cover everything, so I end up creating a “Director’s cut…” session with additional tips that you can check in the presentation slides here.

Today, and because I already received some emails regarding this topic, I will address the “Database Lookup functoid

We can use the Database Lookup functoid to extract information from a database and store it as a Microsoft® ActiveX® Data Objects (ADO) recordset. This functoid requires four input parameters in the following order:

  • Parameter 1: A value for which to search in the specified database, table, and column.
  • Parameter 2: The full connection string for the database with a provider, machine name, database and authentication (an ActiveX Data Objects .NET (ADO.NET) connection string)
  • Parameter 3: The name of the table in the database in which to search.
  • Parameter 4: The name of the column in the table in which to search.

The functoid is actually quite simple to use, however, the main problem that developers face when they use it refers to the second parameter: the connection string. And why?

First: What is the correct value for the connection string?

I always find hard to remember the correct value for the connection string to be used inside this functoid.

Database-Lookup-Functoid-sample-configuration

The Easiest way to make sure we are using the correct connection string value and for not having to remember this by head is to create a simple Universal Data Link (.udl) File… set OLE DB provider connection parameters and test the connection to check if everything is correct.

To accomplish this we need to:

  • · Navigate to a folder in your system, can be in the desktop or preferably in a folder under your BizTalk visual studio solution, let’s call it “Resources”.
  • Create a text file and name it “ODBCConnectionTest.udl”
    • The name of the file is not important, the important part is the extension, it must be “.udl”
  • Double click the file you just created.

create-Universal-Data-Link-udl-File

  • On the Provider Tab, select the appropriate OLE DB provider for the type of data you want to access and then Next
    • In my case it is “Microsoft OLE DB Provider for SQL Server”

Universal-Data-Link-udl-Properties-Provider-tab

  • In the Connection tab, specify:
    • Where your data is located, typically the server and database name.
    • How to connect to it using an OLE DB provider: Use Windows NT integrated security or Use a specific user name and password
    • In this particular case, you need to provide the SQL Server, the database name we want to connect.

Universal-Data-Link-udl-Properties-Connection-tab

  • Then you can and should click on “Test Connection…” button to attempt a connection to the specified data source. If no connection is made, review the settings. Otherwise click “Ok”

Universal-Data-Link-udl-Properties-Test-Connnection

After data Open the “ODBCConnectionTest.udl” file in notepad and you will find the connection string value that you can copy and use it in the second parameter of the Database Lookup Functoid.

Open-Universal-Data-Link-udl-File-notepad

This will lead us to the second problem that you can face using this functoid: using the connection string statically inside the Database Lookup Functoid

Hard-coding the SQL connection strings might lead to maintenance overhead and serviceability issues.

Important considerations:

  • You shouldn’t Hard-coding this value directly in the functoid otherwise it will be a nightmare when you deploy this to a different environment.
  • You can and you should store this parameter in a different storage location (SSO, Registry or others) and get this value using a scripting Functoid or custom functoid which can then be linked to the Database Lookup Functoid, like the:
    • BTSNTSvc Config Get Functoid: This functoid allows you to get configuration parameters from BTSNTsvc.exe.config. If there is no section specified, the functoid reads from the AppSettings.
    • Windows Registry Config Get Functoid: This functoid allows you to get configuration parameters from Windows Registry.
    • SSO Config Get Functoid: This functoid allows you to get configuration parameters from SSO Database.
    • Rule Engine Config Get Functoid: This functoid allows you to obtain a definition value from a Vocabulary in the Business Rules Engine.

In my personal opinion I advice you to use SSO or BRE to store this configuration parameters. All of these custom functoids are available in the BizTalk Mapper Extensions UtilityPack.

TIP-10-Database-Lookup-functoid

You can found and download the Source Code on MSDN Code Gallery:

BizTalk Mapper: How to use Database Lookup Functoid
Microsoft | MSDN Code Gallery

Back again to one of my favorites topics: transformations. This is something that several people asked me for help in the past but I never found time to reply… until now.

A quite note: if you want to know more about BizTalk Maps transformation check out my free book available at BizTalk360 website: BizTalk Mapping Patterns & Best Practices

While migrating an older BizTalk Map transformation, from a custom schema to a SAP IDOC schema, from an entire custom XSLT file to BizTalk Mapper (don’t get me wrong, I only made this migration because the source schema changed so I had the need to change the entire XSLT code, otherwise I would have used the existing external file), I had the need to implement some part of the transformation in an Inline XSLT code (using a Scripting Functoid) and I only used inline XSLT because I was transforming a flat structure from the source schema to a recursive node in the target schema base on some conditions (otherwise I would have used the Table Lopping Functoid)

BizTalk-Mapper-inline-scriptin-functoid-with-prefix

<ns1:E2EDKA1003>
  <ns1:DATAHEADERCOLUMN_SEGNAM>E2EDKA1003</ns1:DATAHEADERCOLUMN_SEGNAM>
  <ns1:PARVW>LF</ns1:PARVW>
  <ns1:PARTN>0000100000</ns1:PARTN>
</ns1:E2EDKA1003>

<ns1:E2EDKA1003>
  <ns1:DATAHEADERCOLUMN_SEGNAM>E2EDKA1003</ns1:DATAHEADERCOLUMN_SEGNAM>
  <ns1:PARVW>ZC</ns1:PARVW>
  <ns1:PARTN>
    <xsl:choose>
      <xsl:when test="@Transportation='1'">
        <xsl:value-of select="'0000100001'" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="'0000100009'" />
      </xsl:otherwise>
    </xsl:choose>
  </ns1:PARTN>
</ns1:E2EDKA1003>

Note that I’m using the prefix “ns1” on the element because the schemas is expecting that, and it should work because both the schema and the transformation file (XSL) contains the namespace

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
     xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
     …
     xmlns:s1="http://unicer.pt/OrderUnigest01.xsd"
     xmlns:ns1="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/3/ORDERS01//700"
     xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp">

BizTalk-Mapper-xslt-prefix-namespace

You can validate that by:

However, when you either compile the map or validate it, we get the following error message:

Validate the map or compile it, I get the following error:

Exception Caught: ‘ns1’ is an undeclared prefix. Line …, position …

 

CAUSE

Well unfortunately BizTalk Mapper Editor doesn’t like prefixes in inline XSLT code, although they exist he is not able to understand them without additional information in the code, which makes our work a little more difficult or we need to use a different strategy in our XSLT code.

Luckily, we can easily fix this strange behavior of the editor.

SOLUTION

The cleanest way to solve this strange behavior is to use the <xsl:element> element or <xsl:attribute> element to create the records, elements and attributes required, instead of manually create them <ns1:E2EDKA1003></ns1:E2EDKA1003> without the <xsl:element> element:

<xsl:element name="ns1:E2EDKA1003">
  <xsl:element name="ns1:DATAHEADERCOLUMN_SEGNAM">E2EDKA1003</xsl:element>
  <xsl:element name="ns1:PARVW">LF</xsl:element>
  <xsl:element name="ns1:PARTN">0000100000</xsl:element>
</xsl:element>

<xsl:element name="ns1:E2EDKA1003">
  <xsl:element name="ns1:DATAHEADERCOLUMN_SEGNAM">E2EDKA1003</xsl:element>
  <xsl:element name="ns1:PARVW">ZC</xsl:element>
  <xsl:element name="ns1:PARTN">
    <xsl:choose>
      <xsl:when test="@Transportation='1'">
        <xsl:value-of select="'0000100001'" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="'0000100009'" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:element>
</xsl:element>

If you don’t want to use the <xsl:element> element or <xsl:attribute> element, then you need to declare the prefix namespace in the record:

<ns1:E2EDKA1003 xmlns:ns1="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/3/ORDERS01//700">
  <ns1:DATAHEADERCOLUMN_SEGNAM>E2EDKA1003</ns1:DATAHEADERCOLUMN_SEGNAM>
  <ns1:PARVW>LF</ns1:PARVW>
  <ns1:PARTN>0000100000</ns1:PARTN>
</ns1:E2EDKA1003>

<ns1:E2EDKA1003 xmlns:ns1="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/3/ORDERS01//700">
  <ns1:DATAHEADERCOLUMN_SEGNAM>E2EDKA1003</ns1:DATAHEADERCOLUMN_SEGNAM>
  <ns1:PARVW>ZC</ns1:PARVW>
  <ns1:PARTN>
    <xsl:choose>
      <xsl:when test="@Transportation='1'">
        <xsl:value-of select="'0000100001'" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="'0000100009'" />
      </xsl:otherwise>
    </xsl:choose>
  </ns1:PARTN>
</ns1:E2EDKA1003>

In any of the case that you decide to implement, if then you try to compile or validate the map, everything will work fine and the error will disappear.