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:

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:

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. View all posts by Sandro Pereira

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…

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 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.

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:

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:

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. View all posts by Sandro Pereira

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

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?

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. View all posts by Sandro Pereira

Where to download?

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. View all posts by Sandro Pereira

There are many things to consider when planning this type of installation; often the network infrastructure already exists, and BizTalk Server must coexist with other network applications. This guide describes some of the considerations that apply to the various parts of a BizTalk Server installation in a basic multi-computer environment. This information will help you plan the installation and configuration of BizTalk Server 2016, applications and components on which it depends.

But this step-by-step guide is not only about installing BizTalk Server 2016, but it will also help you configure and optimize your environment.

What to expect about Installing BizTalk Server 2016 in a Basic Multi-Computer Environment whitepaper

This whitepaper will give you a detailed understanding of the following:

BizTalk Server Installation scenario

The need for a Domain Controller

Preparing Computers for Installation

Preparing and Install SQL Server 2016 machine

Prepare and install prerequisites on BizTalk Server 2016 machine

Testing environment connectivity

Install and configure BizTalk Server 2016 machine

Optimize the BizTalk Server 2016 environment.

Where I can download it

You can download the whitepaper here:

Once again, I would like to take this opportunity also to say thanks to Lex Hegt for helping me reviewing this whitepaper especially for the incredible work of him correcting my horrible English and in this way make the document and my thoughts being readable.

I hope you enjoy reading this paper and any comments or suggestions are welcome.

You can also find, download and enjoy several other free whitepapers of my own here:

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. View all posts by Sandro Pereira

Definition of DTD schemas

For who doesn’t know what DTD is, a DTD is a Document Type Definition that defines the structure and the legal elements and attributes of an XML document… So basically, it is the equivalent of what BizTalk Server uses to define how an XML document can be structured: XSD Schema. Nevertheless, there are many differences between DTD (Document Type Definition) and XSD (XML Schema Definition). In short, DTD provides less control on XML structure whereas XSD (XML schema) provides more control.

Some of you may wonder, who cares? No one uses DTD anymore, so why bother?

Yes, indeed DTD is not used very regularly nowadays, and it is very probable that you will never use it… unless… for example, you are working with RosettaNet or in the future, you will have the need to work with RosettaNet. Why? Because most of the RosettaNet Standards – PIP messages – are defined in the format of DTD format!

As I mentioned in my previous blog, there are several ways we can create an XML Schemas in BizTalk Server:

Manually from the scratch

From XDR Schema instance

From a DTD instance

From a well-formed XML instance

Import them from a WCF Service or Web Service

Or automatically generated them from LOB systems (from the adapters)

Generating schemas based on a DTD instance

Today we will be talking about automatically generating XML Schemas from a DTD instance.

To accomplish this we need to:

In Solution Explorer, right-click a BizTalk project, point to Add, and then click Add Generated Items…

One of the problems, especially in brand new installations, is that sometimes this feature was not installed, and you will see DTD (Not Loaded) in the drop-down list. To install and use this feature you need to:

And execute the InstallDTD.vbs script by double-clicking. This will install the “Microsoft.BizTalk.DTDToXSDGenerator.dll” DLL on the correct place. This basically means that it will copy this DLL to the “Developer ToolsSchema Editor Extensions” folder present in the BizTalk Server installation folder

Close the Generate Schemas dialog box and do the previous steps again. Now you will be able to see that you can use the option DTD in the drop-down list

In the Generate Schemas dialog box, click Browse, locate the file you want to import, click Open and then click OK

A new schema, or sometimes at least two schemas are generated from the specified file, using the same name as that file with the .xsd extension, and opened in BizTalk Editor

Quick, simple and practical.

Stay tuned for new Tips and Tricks!

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. View all posts by Sandro Pereira

BizTalk Pipeline Components Extensions Utility Pack community project for BizTalk Server 2016, once again, got a new update and it now has a new component that you can use in your custom BizTalk Server pipelines: Unzip File Pipeline Component.

Unzip File Pipeline Component

The Unzip File Pipeline Component for BizTalk Server can be used in a Received pipeline (Disassemble stage), and it allows you to receive a compress (zip/gzip) file and extract its contents into different XML messages.

The capabilities are like those available in any compression software such as WinZip or 7-zip:

This component doesn’t require any configurations.

This was an old component that I had that I now updated and improved to BizTalk Server 2016 with the help with my internal team at DevScope, in particular, Pedro Almeida.

You do not need to add a custom pipeline component to be used by the BizTalk Runtime to the Global Assembly Cache (GAC).

What is BizTalk Pipeline Components Extensions Utility Pack?

BizTalk Pipeline Components Extensions Utility Pack 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.

Where to download it?

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. View all posts by Sandro Pereira

One of the problems, especially in brand new installations, is that sometimes this feature was not installed, and you will see Well-Formed XML (Not Loaded) in the drop-down list. To install and use this feature you need to:

Start a Windows Explorer and navigate to the “SDKUtilitiesSchema Generator” folder present in the BizTalk Server installation folder;

Close the Generate Schemas dialog box and do the previous steps again, and now you will be able to see that you already can use the option Well-Formed XML in the drop-down list.

In the Generate Schemas dialog box, click Browse, locate the file you want to import, click Open and then click OK

A new schema, or sometimes at least two schemas are generated from the specified file, using the same name as that file with the .xsd extension, and opened in BizTalk Editor

TIP: Before you generate the schemas, rename the XML instance file that you will be using to the proper name you want to give to the Schemas, this will save you time because the Generator Wizard will:

Give the same name of the specified file with the .xsd extension

Or will add a sequence to the same name of the specified file with the .xsd extension

In the end, as a best practice, you should rectify or rename the schemas generated to something with context and that will be easy to identify:

If you want it to be perfect, then for each schema, you should change not only the name of the file, but also the Type Name property of each schema.

Initially, this value was set as “myfilename_0”.

The problem of renaming the filename and/or the Type Name property is that once you try to compile it will give you errors because some schemas will be referring files that no longer exist (were renamed):

To solve this, we need to:

Open the Schema and select the Schema node at the top of the schema tree view

In the Properties window, in the Advanced category, in the value portion of the Imports property, click the ellipsis (…) button

In the Imports dialog box, in the Import Schema list, delete the XSD Import line, import the correct one and then click OK

Alternatively, open the schema file in Notepad (Notepad ++ or other text editors) and rectify the path and filename

Quick, simple and practical!

Stay tuned for new tips and tricks!

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. View all posts by Sandro Pereira

Happy to announce that today I decided to publish another release on my Mapper Extensions UtilityPack project. This time I decided to release 2 new BizTalk Database Functoids:

Adv Database Lookup Functoid: Use the Adv Database Lookup functoid to extract information from a database and store it as a Microsoft ActiveX Data Objects (ADO) recordset. This functoid requires the following 3 inputs: a database connection string, table name and a WHERE clause.

Adv Value Extractor: Use the Adv Value Extrator functoid to extract the appropriate column value from a recordset returned by the Adv Database Lookup functoid. This functoid requires two inputs parameters: a link to the Database Lookup functoid and a column name.

Parameter 2: The name of the table in the database in which to search;

tblSalesPersons

Parameter 3: WHERE clause (without the WHERE)

SourceSystemID like ‘A’ AND SSSalesPersonID = 1

The output of the functoid will be an ADO recordset.

Adv Value Extractor Functoid

Use the Adv Value Extrator functoid to extract the appropriate column value from a recordset returned by the Adv Database Lookup functoid. This functoid requires two inputs parameters: a link to the Database Lookup functoid and a column name.

Parameters

The functoid takes two mandatory input parameters:

Parameter 1: An ADO recordset, which is the output of the Database Lookup functoid. This recordset never contains more than one database row.

Parameter 2: The name of a column from which to extract a value for output.

TargetSalesPersonID

The output of the functoid will be the value of that column on the database table.

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?

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. View all posts by Sandro Pereira

And yes, this is just another “Login failed for user” SQL Server WCF-Adapter related error. In the past I wrote about a similar topic BizTalk WCF-SQL Error:

This time the error message, the cause, and the solution are slightly different. This time while trying to communicate to a brand-new SQL Server server/database to insert date on a table thru BizTalk WCF-SQL adapter I got the following error:

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.SendMessage(IBaseMessage bizTalkMessage)

MessageId: {84B22A22-13F7-47C7-91B5-A863E64E268E}

Cause

Once again, sometimes is not quite true, the cause of the problem is simple to diagnose, and the error message gives a very good intel in the cause of the problem.

This problem occurs because the user account that you used to access the database, in my case the BizTalk Host Instance Account, don’t have permissions to connect… the SQL Server or SQL Server instance.

Just to be clear, this is not having permission to insert, read or event full permission to do operation on a specific database, that is completely different – I check all of that and the user have the correct access/permission. What I forget was to give access to connect to the SQL Server/SQL Server Instance.

Solution

To solve this issue, you must give access to the user, in my case BizTalk Host Instance Account to connect to the SQL Server and for that, you must:

Open SQL Server Management Studio and connect to your server.

In the Object Explorer, expand the “Security” folder under the server.

Right click on the “Logins” folder and choose “New Login…”

Add the username or group in the format “DomainUserNameOrGroup”

Choose the “Securables” tab and make sure that you grant “Connect SQL” permission to the SQL Server/SQL Server instance

Click “OK” and your user will be created and have access to connect to your SQL Server.

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. View all posts by Sandro Pereira