This series of post will explain in detail – a step-by-step guide – how to install and configure Microsoft BizTalk 2013 R2 Accelerator for RosettaNet (BTARN) on a standalone environment running Windows Server 2012 R2 and BizTalk Server 2013 R2.

RosettaNet is a business protocol that enables enterprises to conduct business over the Internet. The RosettaNet Consortium (http://www.rosettanet.org) is an independent, nonprofit consortium of major information technology, electronic component, and semiconductor manufacturing companies working to create and implement industry-wide, open process standards. These processes are designed to standardize the electronic business interfaces used between participating supply chain partners. The RosettaNet Implementation Framework (RNIF) specification is a guideline for applications that implement RosettaNet Partner Interface Processes (PIPs). These PIPs are standardized electronic business processes used between trading partners. You can find all the Framework specification and the list of PIPs available, as well the PIPs contract (DTD and documentation) in the RosettaNet Consortium website: http://www.rosettanet.org.

BTARN will extend the existing BizTalk Server capabilities allowing you to run RosettaNet Partner Interface Processes (PIPs) and by doing that, this will allow you to exchange RosettaNet documents with your business partners, by simple providing a set of orchestrations, schemas, tools and related helper assemblies. BTARN supports the RosettaNet Implementation Framework (RNIF) versions 1.1 and 2.0.01. However installing the accelerator can be difficult if you do not plan correctly for it.

This guide will provide you comprehensive guidelines that will help you plan the installation and configuration of BTARN.

Assumptions and out of scope

It will be assumed that all the BTARN software requirements already have been installed. This guide will use BizTalk Server 2013 R2 running over Windows Server 2012 R2 with all the latest critical Windows updates from Microsoft and latest Cumulative Updates for BizTalk Server installed.

Important considerations before you install the RosettaNet Accelerator

There are some important considerations or suggestions, since some of these operations are not mandatory, that we can and should set before starting the installation and configuration Microsoft BizTalk 2013 R2 Accelerator for RosettaNet (BTARN).

Software Requirements

The following table lists the software that BizTalk 2013 R2 Accelerator for RosettaNet (BTARN) requires to run correctly. BTARN has the same software and hardware requirements as BizTalk Server 2013 R2.

Software Required Description Required for/Information
Microsoft Windows · Windows Server 2012 R2

· Windows Server 2012

· Windows 8.1

· Windows 7 SP1

Microsoft BizTalk Server 2013 R2 · Enterprise Edition

· Standard Edition

· Developer Edition

Internet Information Services (IIS) The version that comes with the operating system.

· IIS version 8.0 and 7.5 are supported.

Microsoft Office Optional

Microsoft Office Excel 2013 or 2010.

BizTalk Server 2013 R2 supports only 32-bit version of Microsoft Office.

(installed on client computers when using BAM)

Required by Business Activity Monitoring (BAM) to display a real-time view of business processes
Microsoft SQL Server · Microsoft SQL Server 2014

· Microsoft SQL Server 2012 SP1

This is required for BTARN Runtime, BizTalk Server Runtime, EDI, and BAM
SQL Server Analysis Services if using BAM aggregations
SQLXML 4.0 with Service Pack 1 SQLXML enables XML support for your SQL Server Database. It allows developers to bridge the gap between XML and relational data. You can create XML View of your existing relational data and work with it as if it was an XML file.

Note: You don’t need to worry about installing this because Redistributable CAB File will install this for you if necessary.

This is required for BizTalk Server Runtime, Administrative Tools, and BAM.
Windows Identity Foundation Optional

The official documentation mention that Microsoft Windows SharePoint Services is a requirement.

However I think this is not correct/necessary. Until this date I don’t see any connection between the accelerator and the SharePoint Service.

SharePoint Services adapter or SharePoint Services Online when used with SharePoint Services Client Side Object Model (CSOM). It is not needed when using the SharePoint Services Web Service, which is deprecated. Or when you don’t want to use this feature.
Microsoft Visual Studio Optional

Visual Studio 2013

Provides a development environment for building BizTalk Server applications. Ultimate Edition is recommended, but Premium and Professional are also supported. This is required for BizTalk Server Developer Tools and SDK component
Things to be taken care

When you are planning to install Microsoft BizTalk 2013 R2 Accelerator for RosettaNet (BTARN), you need to be very careful with certain, documented and non-documented, notes or necessary configuration in your existing BizTalk environment. These are the notes in the official documentation:

  • Both BTARN and BizTalk Server 2013 R2 require Microsoft .NET Framework 4.5 as software pre-requisite.
    • If you have multiple versions of .NET Framework installed on your computer, make sure that the BtarnAPP Web application is referencing .NET Framework 2.0. You can configure this by using the Internet Information Services (IIS) Manager.
    • Personal note: Despite the official documentation saying that the BtarnAPP Web application need to refer .NET Framework 2.0, what I noticed is that it will work properly in .NET 4.0.
  • The BizTalk Host Instance Account and the BizTalk Isolated Host Instance Account should be the same. Otherwise, BTARN will not function correctly.
  • BTARN allows you to add only individual service accounts, and not groups, to the BizTalk Server Administrators group or the BizTalk Application Users group.
  • You need to create a WebService extension for BTSHTTPReceive.dll, configuring the IIS isolation mode.
  • Add your server (http://<server name>) to the Local Internet zone in the Internet Explorer security options.
  • If a remote SQL instance using non default port is used for configuring BTARN, then the SQL Server Client Tools must be installed locally.
  • A separate group must be used for role – BizTalk Administrator, BizTalk Host Users, and BizTalk Isolated Host Users during the configuration of BizTalk Server.
  • BTARN does not support the use of alias created for SQL instance to configure the BTARN database.

Unfortunately for us, there are still plenty of notes or the necessity to perform additional settings in our environment so that the minimal conditions are guaranteed for BTARN be able to run properly:

  • Installation
    • RosettaNet requires the IIS_WPG group (group provided by IIS 6.0 that provides the minimum set of user rights and permissions required to run an application) unfortunately for us this group doesn’t exist anymore, so you need to create it manually. Otherwise, BTARN installation will fail.
  • Host Instances
    • The accelerator requires both in-process and isolated host to be marked as “Authentication Trusted” and “32-bit only“. Otherwise, BTARN installation will fail.
      • Very important: The accelerator requires both in-process and isolated host to be marked as “Authentication Trusted” (this setting is off by default when you create a new host) and “32-bit only“.
        • And the reason why in-process need to be also trusted is that the BizTalk Host Instance Account and the BizTalk Isolated Host Instance Account should be the same and you cannot use the same account for trusted and untrusted hosts. Otherwise it was only necessary to mark the isolated host as trusted.
    • The BizTalk Host Instance Account and the BizTalk Isolated Host Instance Account should be the same. Otherwise, BTARN will not work correctly.
      • If the service account set for the BTARN application pools is different from the Isolated Host account, BTARN will not be able to process incoming messages correctly. When the receive “.aspx” page calls the pipeline, the pipeline will not have access to the appropriate certificates. Therefore, it will not be able to decrypt the incoming message or validate the signature. It will also not be able to access the MessageBox database.
    • Do never use Full Qualify domain name in the Logon credentials. Otherwise, BTARN will not work correctly.
    • Make sure the BTARN in-process host is the default send handler of HTTP Adapter and the general Default Host in the group
      • Important: Make sure the BTARN in-process host is the default send handler of the HTTP Adapter. When you create a Partner it will create two send ports and it will use the default send handler for the HTTP Adapter: If the BTARN in-process host is not the default handler, the engine will use the default one instead and then you need to stop the BTARN process, unbinding the send ports, reconfigure the send handler and only then start everything again, so this will induce a small shutting down in our environment.
      • When installing the accelerator, it will install all the assemblies into the default BizTalk Application, and will attempt to configure all orchestrations and ports to run in the first host it finds that is marked as “Authentication Trusted”.  To ensure everything is installed to the host instances you need to:
        • Temporarily make the trusted host instance as the default instance (not recommended).
        • Or if you want the assemblies to be in a different application than the default, create a new application in the BizTalk Administration Console and set it as the default during the installation.
  • BAM
    • Microsoft provide a Tracking file with the activity definitions. However: you need to create your custom BAM views
      • BTARN supports enhanced tracking using BizTalk Activity Monitoring (BAM).
        • Right-click the BizTalk Accelerator for RosettaNet node and then click Properties.
        • In the Global Properties dialog box, select Enable BAM Tracking to enable tracking, or clear this option to disable it.
      • Microsoft provide a Tracking file with the activity definitions however:
        • The tracking points are not customizable;
        • Do not change activity definitions.
        • You only can Manage BAM views and deployment.
  • Databases Maintenance
    • BTARN database are not backuped by default
      • Fortunately for us, Microsoft provides two SQL Scripts that you need to run against this databases in other to extend the standard backup mechanisms but you also need to modify the adm_OtherBackupDatabases table to include a row for each of your custom databases.
        • C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Schema
          • Backup_Setup_All_Procs.sql
          • Backup_Setup_All_Tables.sql
        • Also you need to modify the adm_OtherBackupDatabases table to include a row for each of your custom databases
    • There are no maintenance processes to clean up BTARN databases so you need to create these maintenance processes according to the legal requirements of your company/organization, be aware that:
      • BTARN databases can grow quickly because they contain the PIP XML that you are processing
      • They can affect the performance of your environment
      • They are important

 

Related links:

  • How to Install and Configure Microsoft BizTalk 2013 R2 Accelerator for RosettaNet: Preparing your BizTalk Server 2013 R2 environment for BTARN (Part 2)
  • How to Install and Configure Microsoft BizTalk 2013 R2 Accelerator for RosettaNet: Install and configure Microsoft BizTalk 2013 R2 Accelerator for RosettaNet (BTARN) (Part 3)
  • How to Install and Configure Microsoft BizTalk 2013 R2 Accelerator for RosettaNet: Additional Configurations of BizTalk 2013 R2 Accelerator for RosettaNet (Part 4)
  • How to Install and Configure Microsoft BizTalk 2013 R2 Accelerator for RosettaNet: Troubleshooting Your Installation (Part 5)

I realized that I forgot to add some shapes, or I hadn’t addressed certain features/functionalities, so I decided to make an upgrade to my Microsoft Integration Stencils for Visio 2013 resource, that I published 10 days ago, adding this time 43 new shapes – in a total of 301 symbols/icons that will help you to visually represent Integration architectures and solutions diagrams.

Last shapes added:

  • Transformation patterns (Direct Translation Pattern, Data Translation Pattern, Content Enricher Pattern, Aggregator Pattern, Content Filter Pattern, Splitter Pattern, Grouping Pattern, Sorting Pattern, Conditional Pattern, Looping Pattern, Canonical Data Model Pattern, Name-Value Transformation Pattern, …)
  • Components and features (3rd Party components, Azure Connectors, Debatching, batching, Archiving, share folders, download, upload, RFID, ..)
  • Database and systems (Database Partition, Database Mirror, SCOM, Management Role, Health Monitoring, ..)
  • Others (PowerShell, PowerBI, C#, workstations. Business User, Administrator, Role, User Accounts, Walkthrough, ..)

new-Integration-stencils-visio-2013-07

That you can use and resize without losing quality, in particular the new shapes.

Again, I didn’t create all of this shapes, only a few of them, the rest it was one the work of gathering some resources and combine them together. There are still many points that could be improved as well as adding new stencils.

You can download Microsoft Integration Stencils for Visio 2013 from:

Collection of Microsoft Integration Stencils for Visio 2013 (1,9 MB)
Microsoft | TechNet Gallery

Is very common in integration scenarios to see messages being archived locally into the hard drive – either by using a pipeline component like BizTalk Archiving – SQL and File or by simple using the default functionalities in BizTalk like filters:

This can happen for several reasons, for example:

  • The messages needs to be archived for legal reasons (normally (recommended) we use an “external” repository – not a local hard drive in the server)
  • or because it is practical and useful to have the file so it can easily be reprocessed in case of failure

The common problem with some of these approach is that normally we tend to implement the archive mechanism and we forget to implement a cleaning mechanism and we end up with several problems after a while:

  • The most critical –> disk full, causing processes to fail or other problems even more critical
  • Many files in a folder, causing performance problems when we are trying to perform operations on it à According to some articles, the lookup time of a directory increases proportional to the square of the number of entries and performance seems to drop between 1000 and 3000 files (the important is not the exact number itself but knowing that a high number may cause problems).

And if you have an integration process that processes hundreds of messages, you can quickly get this kind of problems.

In my demo scenario we have a common Message Archive folder:

  • D:\BizTalkApplications\Archive

This folder is organized by sub-folders describing the processes and the communication, for example:

  • System A
    • Inbound
    • Outbound
  • System B
    • Inbound
    • Errors
  • Canonical Messages
    • Inbounded
    • Archived

And so on… that I need to monitor and constant clean it.

So how can PowerShell help us?

With this script you can be able to automatically monitoring these folders on the servers using PowerShell.

This script allows you to set:

  • The numbers of hours, minutes or days that you want to archive the files, leaving only the recent ones – if the creation time of the file is older than this parameter then it will be archived and deleted from the folder
$Hours = "12"
$LastWrite = $Now.AddHours(-$Days)
  • The directory you want to monitor
$Directories = Get-ChildItem "D:\BizTalkApplications\Archive" -Recurse | ?{ $_.PSIsContainer } | Select-Object FullName
  • And for each subdirectory it will check if exist files to be archived
    • if so, it will create a zip file, using the 7-zip tool, in a different location “BackupFileWarehouse”
    • Otherwise will not create any file and move to the next directory
foreach ($directory in $Directories)
{
    $zipFile = "E:\BackupFileWarehouse\" + $directory.FullName.Substring($directory.FullName.LastIndexOf(":")+2).Replace("\","_") + "_" +  (get-date -f yyyyMMdd_hhss).ToString() + ".zip"

    $filelist = get-childitem $directory.FullName |
                                where-object {$_.LastWriteTime -le (get-date).AddHours(-$Hours)} |
                                where-object {-not $_.PSIsContainer}

    if($filelist.Count -gt 0)
    {
        $filelist | format-table -hideTableHeaders FullName | out-file -encoding utf8 -filepath lastmonthsfiles.txt
                    & 'C:\Program Files (x86)\7-Zip\7z.exe' a $zipFile `@lastmonthsfiles.txt

        $filelist | %{Remove-Item $_.FullName }
        rm lastmonthsfiles.txt
    }
}

Of course if this script will not move/create the archived files in a shared location or something similar, then you will need an additional mechanism to maintain and delete this zip files

Requirements:

The script can be found and download on Microsoft TechNet Gallery:
BizTalk DevOps: Manage messages being archived locally into the hard drive (1.0 KB)
Microsoft TechNet Gallery

Every time I try to configure the BizTalk Accelerator for RosettaNet in BizTalk Server 2013 or BizTalk Server 2013 R2 of course using Windows Server 2012 or Windows Server 2012 R2 I encounter this error in the configuration logfile

[2015-05-14 16:37:40:0043 Error ConfigHelper] Failed to add member ‘user’ to NT Group IIS_WPG Error code: -2147022676
[2015-05-14 16:37:40:0043 Error ConfigHelper] The group name could not be found

BTARN-The-group-name-could-not-be-found

CAUSE

When, basically this is a bug that has not been fixed yet. BizTalk Accelerator for RosettaNet was introduced in BizTalk Server 2004 and since there only little or no changes were made.

BTARN requires the IIS_WPG group (group provided by IIS 6.0 that provides the minimum set of user rights and permissions required to run an application) but unfortunately for us this group doesn’t exist anymore…

SOLUTION

The solution is very simple… you just need to create it manually.

To accomplish that you need to:

  • Open Computer Management and in the console tree, expand “System Tools à Local Users and Groups” and then right-click Groups and select the option New Group .
  • In Group name
    • Type: IIS_WPG.
  • In Description
    • Type: RosettaNet IIS group
  • And then click Create, and then click Close

I really hope that Microsoft provide a hotfix for this or at least fix this problem in the next releases of the product

I’m proud and thrilled to announce that I will be for the first time in Belgium to present a session in the Integration Day 2015 on June 4th, 2015 about BizTalk Server: “BizTalk Server Deep Dive Tips & Tricks for Developers and Admins” that is being organized by the Belgian BizTalk User group (BTUG.be).

The event will be held in June 4th, 2015 at MOONBEAT Music Media Theater Studio and will be the first full day event organized by BTUG.be – I hope the first of many. Please visit this link for registration information.

My topic is once again about “BizTalk Server tips and tricks for developers and admins” but this time a more deep dive session. Don’t expect the same session that happen in London, we’ll go into more detail on some topics… we will see the missed tips… and I’m still working in new tips!

So, my goal is for you to expect a similar funny session, full of useful information that you can use in your daily work but still surprise you…

BizTalk-Summit-London-2015

You can read the details below.

Session Abstract

It’s critical to use good tools and techniques to produce working solutions as quickly as possible and at the same time, given the increase the requirements and number of applications organizations develop today. But at the same time, it’s also critical to maintain the health of the entire platform.

In this session, which I’ll try to be a very interactive session (so be prepare to participate), I’ll address and share some useful BizTalk Server Tips and Tricks (and Workarounds) both for developers and administrators. Covering some topics like RosettaNet, SAP, database maintenance, debatching, out-of-the-box pipelines vs custom pipelines and many more.

I hope that, like me, you find it an interesting topic! See you there!

But is not only me that will be there, plus 4 MVP (Nino Crudele, Sam Vanhoutte, Steef-Jan Wiggers and Saravana Kumar), 2 Microsoft employees (Jan Tielens and Tord Glad Nordahl) and multiple partners (Andrew Slivker and Marius W Storsten) will complete the line-up with a set of great session.

PS: Notice that all BizTalkCrew members will be there…

What are you waiting for? Let’s make this the biggest Integration event in Belgium! So please register quickly here: http://btugbelgium.azurewebsites.net/integration-day-2015 because seating is limited, and let your friends and colleagues know about this event.

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);

In my two previous editions (here and here) I published some shapes to represent BizTalk Server physical architectures, Integration architectures (Cloud or Hybrid scenarios) or solutions.

In this resource I change the name of my Visio 2013 Stencils resource, now is called “Microsoft Integration Stencils for Visio 2013”, to include almost the Microsoft Integration Stack.

This package contains a set of symbols/icons (258 shapes) to that will help you visually represent Integration architectures (On-premise, Cloud or Hybrid scenarios) and solutions diagrams in Visio 2013. It will provide symbols/icons to visually represent features, systems, processes and architectures that use BizTalk Server, Microsoft Azure and related technologies.

  • BizTalk Server
  • Microsoft Azure
    • BizTalk Services
    • Azure App Service
    • Event Hubs
    • and so on
  • PowerBI and devices
  • Infrastructure
  • And many more…
BizTalk Server

BizTalk-Server-Stencils-Visio-2013-01

BizTalk Services

BizTalk-Services-Stencils-Visio-2013-02

Azure App Service

Azure-App-Service-Stencils-Visio-2013-02

Azure

Azure-Stencils-Visio-2013-04

Infrastructure

Infraestructure-Stencils-Visio-2013-05

PowerBI and Devices

PowerBI-Devices-Stencils-Visio-2013-06

That you can use and resize without losing quality, in particular the new shapes.

I didn’t create all of this shapes, only a few of them, the rest it was one the work of gathering some resources and combine them together. There are still many points that could be improved as well as adding new stencil, but it is a good start.

Instructions:

  • Download the file and copy to the folder “C:\Users\your_user\Documents\My Shapes” (that is the default folder for the Visio custom shapes)
  • To access the shapes in Visio, select from the Shapes Windows: “More Shapes à My Shapes à Microsoft Integration Stencils For Visio 2013”

Add-BizTalk-Server-Stencils-For-Visio-2013-to-toolbox

You can download Microsoft Integration Stencils for Visio 2013 from:

Collection of Microsoft Integration Stencils for Visio 2013 (1,6 MB)
Microsoft | TechNet Gallery

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

For the third year BizTalk Summit 2015 London was I huge success, for that we need to thanks BizTalk360 team and Saravana Kumar for being able to assemble this great event.

My session

This year I only had 30 minutes to speak, so I had to improvise and try to create a lightweight, funny but useful session in our daily work. My session was about “BizTalk Server tips and tricks for developers and admins”.

Abstract: It’s critical to use good tools and techniques to produce working solutions as quickly as possible and at the same time, given the increase the requirements and number of applications organizations develop today. But at the same time, it’s also critical to maintain the health of the entire platform. In this session, which I’ll try to be a very interactive session (so be prepare to participate), I’ll address and share some useful BizTalk Server Tips and Tricks (and Workarounds) both for developers and administrators. Covering some topics like RosettaNet, SAP, database maintenance, debatching, out-of-the-box pipelines vs custom pipelines and many more.

And I was very glad to notice that people liked it and that they were gave me a great feedback during the breaks… for that: thank you!

You can download the PowerPoint presentation here

And see the video session online here

 

Hope you enjoy and you can also see the rest of the session online here: http://www.biztalk360.com/Events/BizTalk-Summit-2015-London/#sessions

In the sequence of my last post, and following the result obtained while trying to solve the problems that had been originated by the SAP system migration to a newer version, I end up receiving another error. This time the connectivity problem with SAP was overcome, however without any reason I started to receive an error saying that the schema was not known

I got the following error:

“There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Receive Port: "IN_IDOC_PORT" URI: "sap://CLIENT=[SAPClientID];LANG=[LANG];@A/[SAPServer]/[SystemID]?ListenerGwServ=[GWServer]&ListenerGwHost=[GwHost]&ListenerProgramId=[ProgID]&RfcSdkTrace=True&AbapDebug=False" Reason: Finding the document specification by message type "http://Microsoft.LobServices.Sap/2007/03/Idoc/3/IDOC_NAME//740/Receive#Receive&quot; failed. Verify the schema deployed properly.”

It was strange to receive this kind of error because the process was in testing stage, for about a month, and working properly.

CAUSE

After the SAP System migration to a newer version the process started also to use a newer version of the IDOC Schema. I really don’t know if the SAP team change anything in the SAP side or it had to do with the migration itself, however when I checked the source code I noticed that we were using the 711 version of the schema:

And now the SAP system process was using and sending the 740 version of the schema:

SOLUTION

The solution is very simple, you just need to generate and import the newest version of IDOC schema (740) to your BizTalk Solution, make all the necessary changes and redeploy all the required artifacts (schemas, orchestrations, maps, and so on)