Host Integration Server: String was not recognized as a valid DateTime while open HIS Configuration Console

Host Integration Server: String was not recognized as a valid DateTime while open HIS Configuration Console

This week I installed on a BizTalk Server 2016 environment Host Integration Server 2016 (HIS), and to my surprise, I was receiving this quite unusual and annoying error: String was not recognized as a valid DateTime.

Host Integration Server (HIS) Configuration Console: String was not recognized as a valid DateTime

Each time I try to open the HIS Configuration Console.

Nevertheless, this error did not occur the first time I ran the configuration console, in other words, after I install the HIS, the first time I executed the Configuration console everything worked ok. Even, if I close and open again, everything was still working fine. The problem only occurred once I actually configure something.

This error only occurred If:

  • I configure let’s say all the HIS features;
  • Close the HIS Configuration console
  • And then try to open the Configuration console again

Cause

As far as I know, there is no other reason, this is a bug!

Solution

The solution is very simple:

  • Install Host Integration Server Cumulative Update 1

But in fact, there is already Host Integration Server Cumulative Update 2 available so I will recommend you installing this one instead:

After installing HIS CU2, everything worked as expected.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Guest blog by Peter Vervoorn – Recipe: Calling multiple Disassemblers in a Receive pipeline

Guest blog by Peter Vervoorn – Recipe: Calling multiple Disassemblers in a Receive pipeline

Happy to introduce my first guest blog author: Peter Vervoorn from Virtual Green. Peter is a very experienced integration specialist. He has been working in ICT since 1995 and was a co-founder of Axon Olympus (now part of the Codit Group). Until his sabbatical in 2014 he headed the team of consultants at Axon Olympus as Technical Director. During his sabbatical in Thailand, he lost over 50 kilos and currently, he divides his time between Thailand and The Netherlands. He is a specialist with (Microsoft) Integration Tools and Technologies like BizTalk Server, Windows 10 IoT, Node-RED and is also involved in developing industrial automation prototypes.

Peter reach me with this funny, a bit unusual but quite interesting scenario: Calling multiple Disassemblers in a Receive pipeline, and I challenge him to be my first guest blog author on my blog. Challenge that he gladly accepted.

Situation

You receive a zip file, containing several files to extract. The extracted files should be disassembled too. (Possibly because they are in flat file format, or you want to call the XmlDisassembler to set the message type.)

Problem

Although the disassemble stage in the receive pipeline can contain multiple components, only the first component (matching the message) will be executed.

Solution

Create a new disassembler component, which will handle calling the sequential disassembler components.

The implementation of the Disassemble method is very easy, just call the initial component in the sequence.

The GetNext method is where it becomes a bit more interesting. Here we would have to extract all the messages from the first stage and feed them to the second stage. Note that it is not possible to create a single instance for the second stage and keep feeding it messages; each message requires its own instance of the component.

To do this, we have to create a new instance of the second stage component. Then we set the required properties (e.g., with values from the property bag.) Then we call the Disassemble method on the component. Next, we call GetNext to retrieve all messages and queue them.

The remainder of the implementation is for dequeening the messages.

Code

In the sample code below, all the usual methods (Load, Save, Validate, etc.) are not shown. Only the two relevant methods are shown. Also, all the plumbing has been removed for brevity/clarity.

[ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
[ComponentCategory(CategoryTypes.CATID_DisassemblingParser)]
[System.Runtime.InteropServices.Guid("YOUR-GUID-HERE")]
public class MultiDisassembler : IBaseComponent, IPersistPropertyBag, IComponentUI, IDisassemblerComponent
{
    private ExtractorComp extractPC = new ExtractorComp();
    private Queue<IBaseMessage> messages = null;

    public void Disassemble(IPipelineContext pContext, IBaseMessage pInMsg)
    { extractPC.Disassemble(pContext, pInMsg); }

    public IBaseMessage GetNext(IPipelineContext pContext)
    {
        if (messages == null)
        {
            messages = new Queue<IBaseMessage>();
            IBaseMessage msgS1 = null;
            while ((msgS1 = extractPC.GetNext(pContext)) != null)
            {
                XmlDasmComp  xmlDasmPC = NewXmlDasmWithPropertiesSet();
                xmlDasmPC.Disassemble(pContext, msgS1);
                IBaseMessage msgS2 = null;
                while ((msgS2 = xmlDasmPC.GetNext(pContext)) != null)
                { messages.Enqueue(msgS2); }
            }
        }

        if (messages.Count > 0)
        { return messages.Dequeue(); }
        return null;
    }
    //...
    //Missing Code
}

Tips

  • You can add more stages, as required;
  • If necessary, you can inspect a message and choose to either enqueue it directly or use a different component to disassemble that message.
    (An example would be a zip file containing a mix of XML files and nested zip files)
  • In the above sample, all messages are queued when the GetNext method is called the first time. Another possibility is getting the next message in a just-in-time fashion. This requires a bit of additional work as you have to do additional housekeeping and handle possible empty messages.
  • Often you see a disassembler also implementing IComponent. This is not required if only the DisassemblingParser attribute is set.

THIS SAMPLE CODE  IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

BizTalk Server Recipe: Calling multiple Disassemblers in a Receive pipelineBizTalk Server Recipe: Calling multiple Disassemblers in a Receive pipeline (2 KB)
Microsoft | TechNet Gallery

BizTalk Pipeline Components Extensions Utility Pack

The pipeline component is available on BizTalk Pipeline Components Extensions Utility Pack project, that is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity), and everyone can contribute with new pipeline components that can be extended or improve the existing BizTalk Server capabilities.

BizTalk Pipeline Components Extensions Utility Pack: Unzip File Pipeline Component

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here:
BizTalk Pipeline Components Extensions Utility Pack
GitHub

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

A fish out of water: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created

A fish out of water: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created

I was thinking in calling these post series “Note to myself”, only not to be always looking for these solutions whenever these problems not related to BizTalk Server or other integration products or technologies occur. But I decided to call it “A fish out of water” that basically is what I feel sometimes. And to start this post series I choose an occurrent “issue” that happens a lot when I’m trying to do something whit in SQL Server Management Console: Saving changes is not permitted.

The full warning message would be:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

SQL Server Management Console: Saving changes is not permitted

This behavior is very simple to solve and is well documented here: Error message when you try to save a table in SQL Server: “Saving changes is not permitted”. Once again, this is just for helping me not always to look at this “problem” again. My Blog is the first place I look, it is my personal notes.

Cause

This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:

  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.

Solution

To change the Prevent saving changes that require the table re-creation option, follow these steps:

  • Open SQL Server Management Studio (SSMS).
  • On the Tools menu, click Options.
  • In the navigation pane of the Options window, click Designers.
  • Uncheck (clear) the Prevent saving changes that require the table re-creation check box, and then click OK.

SQL Server Management Console: Saving changes is not permitted fixed

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Server WCF-SAP Adapter: System.ArgumentException: An item with the same key has already been added

BizTalk Server WCF-SAP Adapter: System.ArgumentException: An item with the same key has already been added

Following my last post and continuing on the pain points of BizTalk Server infrastructure tasks migration, I end up founding another interesting error: WCF-Custom Reason: System.ArgumentException: An item with the same key has already been added.

Again, we indeed did more than a simple migration, we take this opportunity to improve our solutions and/or BizTalk Server environment to update our applications to the new the new available features. In terms of WCF-SAP Adapter, we are no longer using traditional RFC and we will be starting using SAP .NET Connector (NCo). Despite WCF-SAP adapter will continue to support both the RFC SDK and the SAP .NET Connector, SAP has announced deprecation of its classic RFC SDK (no longer be supported after March 31, 2016) and because of that, it should be used anymore.

While trying to connect our BizTalk Server Receive Location to SAP in order to listen to incoming messages, we initially got this error message:

The Messaging Engine failed to add a receive location “WcfReceiveLocation_SAPBinding_IdocDELVRY05V3R700_Custom” with URL “sap://CLIENT=400;LANG=EN;@a/SERVERNAME/00?ListenerGwServ=sapgw00&ListenerGwHost=LISTENERHOST&ListenerProgramId=BTS_LIST&RfcSdkTrace=False&AbapDebug=False ” to the adapter “WCF-Custom”. Reason: “System.ArgumentException: An item with the same key has already been added.

at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)

at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)

at Microsoft.Adapters.SAP.NCo.RfcServerConnection.Open(RfcClientConnection conn)

at Microsoft.Adapters.SAP.SAPInboundContract.InitializeRfcServerConnectionNCo(String connectionArguments)

at Microsoft.Adapters.SAP.SAPInboundContract.StartListener(String[] listenerActions, TimeSpan timeout)

at Microsoft.ServiceModel.Channels.Common.Channels.AdapterChannelListener`1.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.Dispatcher.ChannelDispatcher.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint.Enable()

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint..ctor(BizTalkEndpointContext endpointContext, IBTTransportProxy transportProxy, ControlledTermination control)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiver`2.AddReceiveEndpoint(String url, IPropertyBag adapterConfig, IPropertyBag bizTalkConfig)”.

BizTalk Server WCF-SAP Adapter: System.ArgumentException: An item with the same key has already been added

Cause

What I found out was that this error was somehow related to the error reported in my previous post: BizTalk Server WCF-SAP Adapter: SAP.Middleware.Connector.RfcCommunicationException: LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode, ERROR service ‘?’ unknown.

The reason why I’m saying that is that each time I restarted the BizTalk Server Host Instances, the error that I got the first time I try to enable the port was this service ‘?’ unknown error. Afterward, whenever I tried to enable this port, I would receive this new error:

  • System.ArgumentException: An item with the same key has already been added

Why this is happening for me is a mix of a small bug and consistency, let me explain better:

  • Why consistency? This because in fact, you cannot have two SAP Receive Locations listening to the same SAP endpoint/SAP Listener Program Id.
    • Actually, this should be when this error would normally occur.
  • Why a small bug? Because in fact there isn’t any Receive location enable for that specific SAP endpoint/SAP Listener Program Id. What is happening is that despite the error and the receive location became automatically disable it somehow stores in memory that SAP endpoint/SAP Listener Program Id

Solution

The solution is very simple and stupid:

  • Restart the host instance that is running that Receive Location

After that, this problem goes away, but make sure you solve all the problems, or this may happen again.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Server WCF-SAP Adapter: SAP.Middleware.Connector.RfcCommunicationException: LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode, ERROR service ‘?’ unknown

BizTalk Server WCF-SAP Adapter: SAP.Middleware.Connector.RfcCommunicationException: LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode, ERROR service ‘?’ unknown

I’m currently migrating a BizTalk Server infrastructure from BizTalk Server 2013 R2 to BizTalk Server 2016, this is a simple task to do in terms of BizTalk Server Applications, or BizTalk Server solutions. However, migrating or in fact, creating a new environment always brings challenges in terms of connectivity and/or proper configurations. SAP.Middleware.Connector.RfcCommunicationException: LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode, ERROR service ‘?’ unknown error is one of this cases!

We indeed did more than a simple migration, we toked this opportunity to improve our solutions and/or BizTalk Server environment to update our applications to the new the new available features. In terms of WCF-SAP Adapter, we are no longer using traditional RFC and we will be starting using SAP .NET Connector (NCo). Despite WCF-SAP adapter will continue to support both the RFC SDK and the SAP .NET Connector, SAP has announced deprecation of its classic RFC SDK (no longer be supported after March 31, 2016) and because of that, it shouldn’t be used anymore.

While trying to connect our BizTalk Server Receive Location to SAP in order to listen to incoming messages, we initial got this error message:

The Messaging Engine failed to add a receive location “IN_WCF_SAP_LISTENER_BTS” with URL “sap://CLIENT=400;LANG=EN;@a/SERVERNAME/00?ListenerGwServ=sapgw00&ListenerGwHost=LISTENERHOST&ListenerProgramId=BTS_LIST&RfcSdkTrace=False&AbapDebug=False” to the adapter “WCF-Custom”. Reason: “SAP.Middleware.Connector.RfcCommunicationException:

LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode

ERROR service ‘?’ unknown

TIME Fri Nov 23 13:47:22 2018

RELEASE 721

COMPONENT NI (network interface)

VERSION 40

RC -3

DETAIL NiErrSet

COUNTER 3

at SAP.Middleware.Connector.RfcServer.DoStart(Boolean logErrors)

at SAP.Middleware.Connector.RfcServer.Start()

at Microsoft.Adapters.SAP.NCo.RfcServerConnection.Open(RfcClientConnection conn)

at Microsoft.Adapters.SAP.SAPInboundContract.InitializeRfcServerConnectionNCo(String connectionArguments)

at Microsoft.Adapters.SAP.SAPInboundContract.StartListener(String[] listenerActions, TimeSpan timeout)

at Microsoft.ServiceModel.Channels.Common.Channels.AdapterChannelListener`1.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.Dispatcher.ChannelDispatcher.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)

at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint.Enable()

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint..ctor(BizTalkEndpointContext endpointContext, IBTTransportProxy transportProxy, ControlledTermination control)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiver`2.AddReceiveEndpoint(String url, IPropertyBag adapterConfig, IPropertyBag bizTalkConfig)”.

The receive location “IN_WCF_SAP_LISTENER_BTS_ DV” with URL “sap://CLIENT=400;LANG=EN;@a/SERVERNAME/00?ListenerGwServ=sapgw00&ListenerGwHost=LISTENERHOST&ListenerProgramId=BTS_LIST&RfcSdkTrace=False&AbapDebug=False” is shutting down. Details:”The Messaging Engine failed while notifying an adapter of its configuration. “.

SAP.Middleware.Connector.RfcCommunicationException: LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode, ERROR service '?' unknown

Cause

The first thing you need to know is that this error is not related to the fact that we are now using the SAP .NET Connector (NCo). This error will still occur if you change back to the classic RFC.

The basic steps if of course to see if we have connectivity and proper access to SAP, and because we were in DEV environment a good way to test this is to Add WCF-SAP Adapter Metadata to a BizTalk Project inside Visual Studio

  • In your Visual Studio BizTalk project, in Solution Explorer, right-click your project, click Add, and then click Add Generated Items.

You will have the same look and fill as creating a Receive Port on the Administrative Console with the advantage of being able to navigate the under the SAP object structure. This way you can test connectivity and access to the proper resources.

In our case, everything worked well. We were able to connect and generate the schemas.

However, all of this is not enough. For the WCF-SAP adapter to work properly in runtime you also need to configure, somewhere in the BizTalk Server machine the TCP port where the SAP adapter will be looking for these connections, because this property is not exposed through the BizTalk Server WCF-SAP Adapter GUI port (send or receive) configuration.

SAP.Middleware.Connector.RfcCommunicationException: LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode, ERROR service '?' unknown: WCF-SAP Port Configuration

In our case, the .NET Connector need to know the port of the message server. This is also very common to happen in load balancing situations.

Solution

In order to specify this TCP port, you need to modify the ‘services’ file in each BizTalk Server machine, normally in:

  • C:Windowssystem32driversetcservices

To include the following entry:

MSHOST 1234/tcp # SAP ENV System Message Server Port

Where:

  • MSHOST is your Gateway server, in our case sapgw00
  • And 1234 is your message server port, in our case: 3300

SAP.Middleware.Connector.RfcCommunicationException: LOCATION CPIC (TCP/IP) on local host BTSSERVERNAME with Unicode, ERROR service '?' unknown: solution

Just for curiosity, BizTalk isn’t the only that will use these values and this ‘services’ file. SAPGUI will also use these same values when connecting to SAP systems.

After we did this configuration, we were able to connect and start receiving messages from our SAP system.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Custom Functoid: Extension function parameters or return values which have Clr type Char are not supported

BizTalk Custom Functoid: Extension function parameters or return values which have Clr type Char are not supported

Back to Errors and Warnings, Causes and solutions, this time with a surprising error that I got while I was testing a custom Functoid that I had developer: Extension function parameters or return values which have Clr type ‘Char’ are not supported

The full error description was:

Invoking component…

C:DevelopmentBizTalk Server Project1BizTalk Server Project1Map1.btm: warning btm1028: The required field “VarValue” has no incoming link, constant value, or default value.

TestMap used the following file: <file:///C:DevelopmentBizTalk Server Project1Schema1_output.xml> as input to the map.

C:DevelopmentBizTalk Server Project1BizTalk Server Project1Map1.btm: error btm1050: XSL transform error: Unable to write output instance to the following <file:///C:UsersAdministratorAppDataLocalTemp2_MapDataBizTalkServerProject1Map1_output.xml>. Extension function parameters or return values which have Clr type ‘Char’ are not supported.

Test Map failure for map file <file:///C:DevelopmentBizTalk Server Project1BizTalk Server Project1Map1.btm>. The output is stored in the following file: <file:///C:UsersAdministratorAppDataLocalTemp2_MapDataBizTalkServerProject1Map1_output.xml>

Component invocation succeeded.

BizTalk-Custom-Functoid-type-Char-are-not-supported

Again, I catch this error while I was trying to test my custom functoid inside Visual Studio.

The Custom Functoid was invoking the following code:

private string GetCSharpBuffer()
{
    StringBuilder builder = new StringBuilder();
    builder.Append("public string SplitText(string element, int pos, char splitter)n");
    builder.Append("{n");
    builder.Append("tstring[] listValues = element.Split(splitter);n");
    builder.Append("tif (listValues != null)n");
    builder.Append("ttif (listValues.Length > pos)n");
    builder.Append("tttreturn listValues[pos];n");
    builder.Append("treturn "";n");
    builder.Append("}n");
    return builder.ToString();
}

Or, to be easier to read in a clean C# code that would be like this:

public string SplitText(string element, int pos, char splitter)
{
   string[] listValues = element.Split(splitter);

   if (listValues != null)
      if (listValues.Length > pos)
         return listValues[pos];
   return "";
}

Cause

Unfortunately, this is a BizTalk mapper engine limitation. The type Char is not supported at least on built-in code inside the XSLT, in other words by using the Functoid SetScriptBuffer functionality:

SetScriptBuffer(ScriptType.CSharp, this.GetCSharpBuffer());

I didn’t test but it may work if you replace this functionality with the SetExternalFunctionName functionality:

SetExternalFunctionName(GetType().Assembly.FullName, GetType().FullName, "SplitText");

Note: This means that the code will not be inside the XSLT but instead you will be calling an external assembly that needs to be on GAC.

Solution

The simple solution, or let’s call I workaround, is to use strings instead, either on inputs as outputs parameters of the Functoid. For that we just need to replace the above code to this:

private string GetCSharpBuffer()
{
    StringBuilder builder = new StringBuilder();
    builder.Append("public string SplitText(string element, int pos, string splitter)n");
    builder.Append("{n");
    builder.Append("tstring[] listValues = element.Split(Convert.ToChar(splitter));n");
    builder.Append("tif (listValues != null)n");
    builder.Append("ttif (listValues.Length > pos)n");
    builder.Append("tttreturn listValues[pos];n");
    builder.Append("treturn "";n");
    builder.Append("}n");
    return builder.ToString();
}

Or again, in a simple C# form:

public string SplitText(string element, int pos, string splitter)
{
    string[] listValues = element.Split(Convert.ToChar(splitter));

    if (listValues != null)
       if (listValues.Length > pos)
          return listValues[pos];
    return "";
}

By doing that you will be able to successfully test the Functoid.

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk360: How to monitor BizTalk Server BRE Policies Pending to be Deployed

BizTalk360: How to monitor BizTalk Server BRE Policies Pending to be Deployed

A few days ago, I helped a fellow “BizTalker” on a PowerShell script implementation that would monitor all your BizTalk Server BRE Policies, highest versions, that aren’t in the deployed state. You can know more about this on my blog: BizTalk DevOps: Monitor your BizTalk environment using PowerShell – Monitoring BRE Policies Pending to be Deployed.

However, my monitor script is, in fact, a combination of:

  • A piece of PowerShell that invokes a SQL Query and notifies by email if any non-compliance occurs
  • A SQL Server query that will have the ability to check what rules are in a non-compliance state; this is where the magic happens

I did that because out-of-the-box BizTalk360 doesn’t have the capability to monitor BRE Policies that aren’t in the deployed state. Despite that BizTalk360 allows you to create “Secure SQL Queries” that will enable us to:

  • Write optimized SQL queries and store them with friendly names in BizTalk360
  • Assign who will have permissions to run the queries
  • Run the queries, of course

This will allow us to quickly check if any policy is in a non-compliance state.

01-BizTalk360-Secure-SQL-Queries

You can see and download the SQL script here: BizTalk360 Secure SQL Server Query to check BRE that are not in a Deployed state.

Nevertheless, you cannot implement monitoring capabilities on these Secure SQL Queries. This feature was made for allowing support persons to safely execute custom queries without impacting the environment.

So, I wanted to go a little further, and to do that I asked my great friend Lex Hegt (Technical Lead at BizTalk360) to help me to implement a monitoring mechanism for this strategy, using the existing features in BizTalk360 and… we did it!

02-BizTalk360-Database-queries-to-monitor-BRE

How to monitor BRE using BizTalk360

The easy way for you is to download and import the BRE monitor alarm that I, along with Lex, developed here: BizTalk360 Monitor BRE Undeployed policies alarm.

To do that, after you download the zip file containing alarm information, you should:

  • Access to BizTalk360 and click ‘Settings‘ button on the upper right corner

01.3-BizTalk360-Import-Alarm-Settings

  • Select the ‘Import and Export‘ option from the left tree menu and select ‘Import

01.4-BizTalk360-Import-Alarm-import

  • Import operation is a 5-step process, as shown below:
    • Step 1 – Select configuration file (zip file containing alarm information): This is very straightforward, you just click on the ‘Select Files‘ button to choose the configuration file, or you can drag and drop the configuration file into the user interface as shown below.

01.5-BizTalk360-Import-Alarm-import-select-file

    • Step 2 – Choose the alarms you wanted to import: Once the configuration file is selected, the wizard will automatically move forward into step 2 and display all the alarms that are present in the configuration file. You can then either choose to import all of them or select only the specific alarms you wanted to import as shown below.
      • As you will see in the picture below, we also include the Secure SQL Queries, you should also select them;

01.6-BizTalk360-Import-Alarm-import-import-configurations

      • Note that by default all the alarms are kept in disabled status to avoid sending unnecessary notification after import, you may want to review all the configuration before enabling it
    • Step 3 – Map environment discrepancies: Once you have chosen the relevant alarms to import, the system will detect if there is any mapping required between the source system and destination system. The three common things that will require mapping are BizTalk Server, SQL Server, and SQL Server Instance names since they will, for sure, differ between environments. The third screen ‘Map Environment‘ will give the user the option to do this mapping as shown below.

01.7-BizTalk360-Import-Alarm-import-map-environment

    • Step 4 – Review the summary: On the fourth screen, based on the selection and mapping so far, will give a final summary report. It will also give bit more detail on each alarm, for example which artifacts are configured in each one of them (you need to expand the down arrow to view it). Once you are happy, you can just click on the ‘Import‘ button

01.8-BizTalk360-Import-Alarm-import-summary

    • Step 5 – Result summary and exception handling: Once the import button is clicked, all necessary backend calls will be made, and the result of the import process will be shown. You can view all the details like which artifacts are imported, what mapping applied etc. If there are any errors during the import process in specific alarms, those will be displayed as well
  • Once you close the import wizard, you can navigate to the ‘Manage Alarms‘ screen in the Monitoring section, and you can view all the imported alarms.

01.9-BizTalk360-Import-Alarm-manage-alarms

Of course, you can always create your alarm manually by:

  • Click ‘Monitoring‘ in the Navigation panel
  • Click ‘Manage Alarms‘ tab
  • Click ‘New Alarm
    • Enter a descriptive name for the Alarm, Email id (you can enter multiple email ids as comma separated values), and Alarm Description
  • Click ‘Next‘ to enter the Threshold Alarm page
    • Select the ‘Alert on threshold violation’ check box if you want to be alerted upon a threshold violation.
  • Click ‘Next‘ to enter the Health Monitoring Alert page
    • This step is optional, if you want to use the alarm for regular health/status check, you can select day(s)/time when you want to receive the status alert
  • Click ‘Next‘ to move to the screen to set up data monitoring alert.
    • Select the checkbox ‘Use this alarm for data Monitor Alerts’ if you wish to associate the current alarm with the data monitors.
    • Select the checkbox ‘Notify on Success as well’ to receive success email alerts for the configured data monitors. If you do not choose the second checkbox, you will not receive the success email alerts.
  • Click ‘Next‘ to move to the last section of adding the Advanced Settings information
    • All the settings in the Advanced settings page are optional. Set the phone number to receive the Notification SMS when the alert is triggered. As with the email ids, you can enter multiple phone numbers as comma separated values.
  • Click ‘OK‘ to create the alarm
    • The alarm will be created, and you will be redirected to the Manage Alarms page

But if you choose this approach you will them need to:

  • Select the ‘Manage Mapping‘ in the Navigation pane under ‘Monitoring‘ and then:
    • Select the alarm you just created above or that you already have in your environment
    • Select the tab option ‘Database Query
    • Finally, create two new queries, by selecting the button ‘New Query
      • One for monitoring BRE on Published state
      • Another for monitoring BRE on Saved state

01.10-BizTalk360-Import-Alarm-manage-mapping

    • After you press the ‘New Query‘ button you need:
      • Enter the Name for the Database Query
      • Add the SQL Instance name
      • And the SQL Database name
        • This need to be “BizTalkRuleEngineDb

01.11-BizTalk360-New-Alarm-New-Database-Query-Basic-Details

    • Click ‘Next‘ to add the Query and Threshold details
    • This will define when BizTalk360 must return an alert and notify us if any non-compliance occurs

01.12-BizTalk360-New-Alarm-New-Database-Query-Query-and-Threshold-details

01.13-BizTalk360-New-Alarm-BRE-Alarm

If you noticed, my queries have a red flag which means that I have non-compliance BRE and I will get an alert.

Now you have your favorite monitoring tool be able to monitor the state of your BRE!

Author: Sandro Pereira

Sandro Pereira is an Azure MVP and works as an Integration consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

BizTalk Assessment: PowerShell to check what BizTalk Server 2016 Feature Packs are installed in your Servers got updated

BizTalk Assessment: PowerShell to check what BizTalk Server 2016 Feature Packs are installed in your Servers got updated

In the past, I release this PowerShell in order that any BizTalk Administrator to check and control which BizTalk Server Feature Pack was installed in your BizTalk environment. However, recently Niclas Öberg, another fellow “BizTalker” that I meet in London Integrate event warn me that my script didn’t recognize the existence of FP3… and he was correct

An alternative was to use BHM but the current version of BHM is also not checking for FP3 (only 1 or 2 – this limitation for sure will be fixed in next versions of BHM), you may get a warning messages saying that no CU or FP is installed, or you may get an FP2 warning messages like me:

BizTalk Server 2016 Feature Packs: BHM
Nevertheless, I have installed FP3 in my environment.

Of course, once again you can easily check this manually in the “Control PanelProgramsPrograms and Features” and then view the “View Installed Updates”, although this can be a very annoying task.

So, I decided to fix my PowerShell script and release a new version that will solve this problem. Now, if you run the script you will be able to receive the following output describing what FP was or is installed in your environment:

Microsoft BizTalk Server 2016 Feature Pack 1 is installed
Microsoft BizTalk Server 2016 Feature Update 2 with CU3 [KB4054819]LDR is installed
Microsoft BizTalk Server 2016 Feature Update 2 with CU4 [KB4077058]LDR is installed
Microsoft BizTalk Server 2016 Feature Update 3 [KB4103503]LDR is installed

The beauty of this script is that now it is prepared for detecting any other future PF that may be released by Microsoft, without the need for you to change the script.

...
    foreach ($Property in $key.Property) 
    {
        if ($Property -like '*Microsoft BizTalk Server 2016 Feature Update *') 
        { 
            $findF2 = 1
            Write-Host "$($Property) is installed"
        }
    }
...

This script follows the sequence of scripts that I release in the past to check what Cumulative Updates where installed in the machines.

THIS POWERSHELL SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

Check which BizTalk Server 2016 Feature Packs installed with PowerShellCheck which BizTalk Server 2016 Feature Packs installed with PowerShell (2 KB)
Microsoft | TechNet Gallery

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk DevOps: Monitor your BizTalk environment using PowerShell – Monitoring BRE Policies Pending to be Deployed

BizTalk DevOps: Monitor your BizTalk environment using PowerShell – Monitoring BRE Policies Pending to be Deployed

After a tweet exchange with Mark Brimble a fellow “BizTalker” that I respect and admire, I realized that I had developed a PowerShell script some time ago to monitoring BRE Policies that could help him solve his problem. The initial question or need he was facing was:

  • How would you detect when there is no rules policy in a deployed state? I don’t know how many times I import a rule an forget to set it to deployed…

Monitoring BRE Policies Pending to be Deployed: problem

This is a common problem, and to be honest, I sometimes forget what is the correct state of a policy: Deployed or Published. (the correct answer is Deployed). And unfortunately, there isn’t a simple solution to solve this need and the solutions that I found was:

  • Using BizTalkFactory PowerShell Provider that nowadays come with BizTalk Server (“SDKUtilitiesPowerShell” folder)
  • Create my own monitor script – more work involved

Using BizTalkFactory PowerShell Provider is quite simple, but it has some limitations for what I would like to archive, for example, it only shows the policies that are bound to a particular BizTalk Application.

Monitoring BRE Policies Pending to be Deployed: BizTalkFactory PowerShell Provider

And I would like to know and have visibility to all of them because you don’t need to bind a policy to a BizTalk Application on the BizTalk Administration Console to use that policy.

And for that reason, I decide to create my own monitor script that I can easily change and optimize for my scenarios.

The problem I faced in the past was in fact quite similar to what Mark Brimble was describing, maybe with some small differences but the end goal is the same, so I decide to help him (at least try) and publish this PowerShell script.

The purpose of this PowerShell script is to:

  • Monitor BRE Policies and check if the highest version of a given policy is on Deployed state
    • If not notify someone (your BizTalk administration team);

So how can PowerShell help us?

With this script, you can be able to monitor your BizTalk Server BRE Policies, highest versions, that aren’t in the deployed state. Only if the script finds any non-compliance, an email notification will be sent.

Taking this sample:

Monitoring BRE Policies Pending to be Deployed: Policies Sample

The result will be a notification that includes two warnings:

  • Policy1 is in a non-compliance state because version 1.2 is not deployed
  • Policy2 is in a compliance state
  • Policy3 is in a non-compliance state because version 1.2 is neither published nor deployed (this is optional, but I choose to include in my monitoring script)

This script is a combination of a PowerShell script and a SQL Server Script that allows you to set:

  • Set your email notification settings:
#Set mail variables
[STRING]$PSEmailServer = "mySMTPServer" #SMTP Server.
[STRING]$SubjectPrefix = "MBV Notification Report -  "
[STRING]$From = "biztalksupport@mail.pt"
[array]$EmailTo = ("sandro.pereira@devscope.net")
  • And configure a SQL Server script, that in fact were the magic happens. The SQL Script will have the ability to check what rules are in a non-compliance state:
/****** Sandro Pereira & José Barbosa - DevScope  ******/
;with 
cteHist as (
        select h.* from [BizTalkRuleEngineDb].[dbo].[re_deployment_history] h
join (select strname, max(dttimestamp) as dttimestamp from [BizTalkRuleEngineDb].[dbo].[re_deployment_history] group by strname) q on h.strName=q.strName and h.dtTimeStamp=q.dttimestamp
),
ctetDeployed as (
        SELECT StrName, nMajor, nMinor, nStatus
                                                FROM   (
                                                   SELECT StrName, nMajor, nMinor, nStatus
                                                                , row_number() OVER(PARTITION BY StrName ORDER BY nMajor, nMinor DESC) AS rn
                                                   FROM   [BizTalkRuleEngineDb].[dbo].[re_ruleset]
                                                   ) sub
                                                WHERE  rn = 1
)
select * from ctetDeployed d
where nStatus = 0
or exists (select 1 from cteHist h  where h.strName=d.strname and bDeployedInd=0)

The following Windows PowerShell script is a fragment that will help us demonstrate the monitoring capabilities:

$mydata = invoke-sqlcmd -inputfile $sqlQuery -serverinstance $server

Foreach ($log in $mydata)
{
    #Create mail body content
    $mailBody += "..."
}

Here is example expected report output from running the Windows PowerShell script sample, if any of the BRE Policies are in an unwanted state.

Monitoring BRE Policies Pending to be Deployed: Report

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them. The script should also be adjusted to your needs.

THIS POWERSHELL & SQL SCRIPT ARE PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

Special thanks to my coworker José Barbosa for helping me optimize the SQL Server script!

The script can be found and download on Microsoft TechNet Gallery:
Monitoring BRE Policies in your BizTalk environment with PowerShell (18.0 KB)
Microsoft TechNet Gallery

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

BizTalk Mapper Extensions UtilityPack: String Splitter Functoid

BizTalk Mapper Extensions UtilityPack: String Splitter Functoid

I love this project! I have much fun doing it, and I use it a lot in my clients. So for me is what I call a win-win situation. So today I update BizTalk Mapper Extensions UtilityPack project with one new Functoid that you can use on your BizTalk Server maps Functoid: String Splitter Functoid.

Last week I had the needed to use a particular mapping functionality several times in different maps:

  • Extract one specific subset of a concatenated string;
    • For example “AA.123.SANDRO.312” and I need to extract the third position: “SANDRO”

My first strategy was to create a Scripting Functoid, but because I have to use it in different maps, I quickly change my approach and created this new Functoid that now you can use inside your BizTalk Server maps.

String Splitter Functoid

This functoid allows you to convert a string to a number (integer)

Parameters

This functoid split a string by a specific splitter character, and it accepts 3 inputs:

  • The first parameter is the input value to be split;
    • AA.123.SANDRO.312
    • AA-123-SANDRO-3122.
  • The second parameter is the position to be returned (the subset of the string to be retrieved);
  • The third parameter is the splitter character;

The output of the functoid will be a string present in the split array, example: SANDRO

BizTalk Mapper Extensions UtilityPack: String Splitter Functoid

In this In sample described in the above picture, we are receiving a string: “AA.123.SANDRO.312.1” and we want to retrieve the fourth subset of the concatenated string delimited by “.” character. So, in this case, the functoid will return: “1”.

BizTalk Mapper Extensions UtilityPack: Project Description

BizTalk Mapper Extensions UtilityPack is a set of libraries with several useful functoids to include and use it in a map, which will provide an extension of BizTalk Mapper capabilities.

Where to download?

You can download this functoid along with all the existing one on the BizTalk Mapper Extensions UtilityPack  here:
BizTalk Mapper Extensions UtilityPack GitHub RepositoryBizTalk Mapper Extensions UtilityPack
GitHub

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.