Installing BizTalk ESB Toolkit 2.0

Microsoft BizTalk ESB Toolkit 2.0 (formerly known as ESB Guidance 2.0) is out. I installed it, together with the ESB Management Portal, today on a Windows Server 2008 32-bit virtual machine using the following procedure:

  1. Install BizTalk ESB Toolkit 2.0-<CPU>.msi.
  2. Import C:Program FilesMicrosoft BizTalk ESB Toolkit 2.0/Microsoft.Practices.ESB.CORE.msi into BizTalk Server using the BizTalk Server 2009 Administration Console and install the .msi file.
  3. Now, before you install the ESB Management Portal, you should follow the steps in Installing the BizTalk ESB Toolkit –> Configuring Services and Components in the help.
  4. Extract C:Program FilesMicrosoft BizTalk ESB Toolkit 2.0/ESBSource.zip to C:ProjectsMicrosoft.Practices.ESB.
  5. Navigate to C:ProjectsMicrosoft.Practices.ESBSamplesManagement PortalInstallScripts, and then start ManagementInstall.cmd.

When browsing the portal (http://localhost/ESB.Portal/Home/HomePage.aspx), I first got an exception. From the event log:

Event Type:    Warning
Event Source:    ASP.NET 2.0.50727.0
Event Category:    (3)
Event ID:    1309
Date:        2009-06-22
Time:        09:04:23
User:        N/A
Computer:    BTS2009.europe.corp.microsoft.com
Description:
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 2009-06-22 09:04:23
Event time (UTC): 2009-06-22 07:04:23
Event ID: 6a2cd3b312364acf8bda81406418cb91
Event sequence: 211
Event occurrence: 8
Event detail code: 0
Application information:
    Application domain: /LM/W3SVC/1/ROOT/ESB.Portal-1-128901266654457632
    Trust level: Full
    Application Virtual Path: /ESB.Portal
    Application Path: C:ProjectsMicrosoft.Practices.ESBSourceSamplesManagement PortalESB.Portal
    Machine name: BTS2009
Process information:
    Process ID: 4816
    Process name: w3wp.exe
    Account name: NT AUTHORITYNETWORK SERVICE
Exception information:
    Exception type: SqlException
    Exception message: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)
Request information:
    Request URL: http://localhost/ESB.Portal/Lists/RegistrySummaryContainer.aspx
    Request path: /ESB.Portal/Lists/RegistrySummaryContainer.aspx
    User host address: ::1
    User: BTS2009Administrator
    Is authenticated: True
    Authentication Type: Negotiate
    Thread account name: NT AUTHORITYNETWORK SERVICE
Thread information:
    Thread ID: 5
    Thread account name: NT AUTHORITYNETWORK SERVICE
    Is impersonating: False

I searched the web and found this blog post that provided the solution:

  1. Use SQL Server Configuration Manager to enable TCP/IP.
  2. Restart SQL Server.
  3. Restart IIS (by typing IISReset at a command prompt).
Advertisements

Connection String for DB2 on Windows

I had a lot of trouble with connecting to DB2 on Windows. I thought it was hard to figure out the right connection string, and there is also a bug that led me on the wrong track.

I had DB2 installed on the same virtual server as BizTalk, and used this connection string:

Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=localhost;Network Port=50000;Initial Catalog=SAMPLE;Package Collection=db2admin;User ID=db2admin;Password=pass@word1;

I got the following error message:

Message: The network connection was terminated because the host failed to send any data.
Native error: –605
Source: Microsoft DB2 OLE DB Provider
SQLState: 08S01

This was regardless if I used the Data Access Tool or .net code. Using IBM’s OLE DB provider from .net code worked well:

Provider=IBMDADB2;Database=SAMPLE;Hostname=localhost;Protocol=TCPIP; Port=50000;Uid=db2admin;Pwd=pass@word1;

It turned out that you cannot use localhost with the Microsoft DB2 OLE DB provider! When I changed to 127.0.0.1 it worked. The real host name works as well.

Next problem was the parameters in the connection string. I wanted to use the sample database that I created using IBM’s setup program. I was logged in as Administrator during this installation, so the sample database objects were created in the Administrator schema. This configuration worked:

image

Note that you should specify the database in “Initial catalog” on in “Database name”.

Host Integration Server 2009 vs. BizTalk Server 2009 Adapters for Host Systems 2.0

I recently did a proof of concept where one part was connectivity with DB2. I had BizTalk Server 2009 developer edition installed, and thought that I would get the DB2 adapter by installing Host Integration Server 2009. Wrong! After a couple of reinstalls and troubleshooting I realized that HIS 2009 does not think BizTalk 2009 developer edition is BizTalk, and therefore the setup program hides the DB2 adapter selection. The developer version is not called Host Integration Server, but BizTalk Server 2009 Adapters for Host Systems 2.0!

From https://connect.microsoft.com/site/sitehome.aspx?SiteID=66&wa=wsignin1.0:

Microsoft BizTalk Adapters for Host System 2.0 Developer edition is available as supplemental software to the Microsoft BizTalk Server 2009 Developer edition. See the Microsoft BizTalk Server 2009 web site for information on purchasing Microsoft BizTalk Server 2009 Developer edition. Microsoft does not offer a Microsoft Host Integration Server 2009 Developer edition. Please utilize the BAHS 2.0 Developer edition.

On MSDN subscriber downloads, you find BizTalk Adapters for Host Systems 2.0 under BizTalk Server 2009.

XPath sum in BRE becomes type double

Using the Business Rules Engine (BRE), you can use XPath functions like sum in vocabulary definitions. For example, I had a vocabulary definition where I wanted to calculate the total tax from an EDIFACT invoice. I had the following definition:

XPath Selector: /*[local-name()=’EFACT_D03A_INVOIC’ and namespace-uri()=’http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006′%5D

XPath Field: sum(./*[local-name()=’TAXLoop5′ and namespace-uri()=’http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006′]/*[local-name()=’MOA_14′ and namespace-uri()=’http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006′]/*[local-name()=’C516_14′ and namespace-uri()=’http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006′][C51601=’124′]/*[local-name()=’C51602′ and namespace-uri()=”])

I noticed that this returned a double, so you should use that type in the definition, otherwise you might get conversion errors.

Schema validation inside orchestration

BizTalk can do XML schema validation using the XML disassembler pipeline component. But I wanted to the same thing in orchestration to have better control of the error handling and produce better error messages. My solution was to write a .net component that uses the the BtsCatalogExplorer to retrieve the schema from a map name (which I already used to do dynamic mapping).

It is generally not recommended to use BtsCatalogExplorer inside integration solutions – it is intended for administration – but this solution didn’t have to handle high load, and I used caching to avoid populating the object structure over and over again. Here is the code:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.XLANGs.BaseTypes;
using System.Xml.Schema;
using Microsoft.BizTalk.ExplorerOM;
using System.IO;
using System.Xml;
using System.Diagnostics;
using Microsoft.Win32;

namespace SchemaValidatorExample
{
    public class SchemaValidator
    {
        public enum SourceOrTarget { Source, Target };

        private static BtsCatalogExplorer explorer;
        private static Dictionary<string, XmlSchemaSet> schemaCache;

        static SchemaValidator()
        {
            explorer = new BtsCatalogExplorer();
            explorer.ConnectionString = GetMgmtDbConnectionString();
            schemaCache = new Dictionary<string, XmlSchemaSet>();
        }

        private ValidationResult validationResult = new ValidationResult();

        public static ValidationResult Validate(XLANGMessage message, string mapName, SourceOrTarget sourceOrTarget)
        {
            if (string.IsNullOrEmpty(mapName))
                return new ValidationResult();
            Debug.WriteLine("Validating message " + message.Name + " against its schema.", typeof(SchemaValidator).FullName);
            if (message.Count <= 0)
                throw new ApplicationException("Message " + message.Name + " has no parts.");
            // Create an instance of this class to have a ValidationResult instance to hold the validation result.
            // There can be multiple orchestration instances validating documents simultaneously.
            SchemaValidator schemaValidator = new SchemaValidator();
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.ValidationType = ValidationType.Schema;
            // Cannot get schema from message part, because it is untyped.
            settings.Schemas = GetSchemaSet(mapName, sourceOrTarget);
            settings.ValidationEventHandler += new ValidationEventHandler(schemaValidator.DocumentValidationEventHandler);
            XmlReader xmlReader = XmlReader.Create((XmlReader)message[0].RetrieveAs(typeof(XmlReader)), settings);
            //XmlReader xmlReader = XmlReader.Create(message, settings);
            while (xmlReader.Read()) { }
            xmlReader.Close();
            return schemaValidator.validationResult;
        }

        private static XmlSchemaSet GetSchemaSet(string mapName, SourceOrTarget sourceOrTarget)
        {
            XmlSchemaSet xmlSchemaSet = new XmlSchemaSet();
            // Synchronize access to the shared member.
            lock (schemaCache)
            {
                // Schemaset already in cache?
                if (!schemaCache.TryGetValue(mapName, out xmlSchemaSet))
                {
                    // Not in cache – get from BizTalk and add to the cache.
                    List<string> schemaNames = new List<string>();
                    xmlSchemaSet = GetXmlSchemaSetFromBizTalk(GetBtsSchemaFromMapName(mapName, sourceOrTarget), ref schemaNames);
                    schemaCache.Add(mapName, xmlSchemaSet);
                }
            }
            return xmlSchemaSet;
        }

        /// <summary>
        /// Returns an XmlSchemaSet containing the XmlSchema corresponding to the BizTalk schema given as input,
        /// plus all imported schemas (recursively).
        /// </summary>
        /// <param name="btsSchema"></param>
        /// <returns></returns>
        private static XmlSchemaSet GetXmlSchemaSetFromBizTalk(Schema btsSchema, ref List<string> alreadyAdded)
        {
            Debug.WriteLine(string.Format("Reading schema with FullName={0}, TargetNamespace={1}, RootName={2}", btsSchema.FullName, btsSchema.TargetNameSpace, btsSchema.RootName), typeof(SchemaValidator).FullName);
            XmlSchemaSet xmlSchemaSet = new XmlSchemaSet();
            XmlSchema xmlSchema;
            using (TextReader textReader = new StringReader(btsSchema.XmlContent))
            {
                xmlSchema = XmlSchema.Read(textReader, SchemaValidationEventHandler);
            }
            xmlSchemaSet.Add(xmlSchema);
            alreadyAdded.Add(btsSchema.FullName);
            // Add imported schemas
            foreach (XmlSchemaObject include in xmlSchema.Includes)
            {
                if (include is XmlSchemaExternal)
                {
                    XmlSchemaExternal schemaExternal = (XmlSchemaExternal)include;
                    string fullName = schemaExternal.SchemaLocation;
                    if (!alreadyAdded.Contains(fullName))
                        xmlSchemaSet.Add(GetXmlSchemaSetFromBizTalk(explorer.Schemas[fullName], ref alreadyAdded));
                }
            }
            return xmlSchemaSet;
        }

        private static Schema GetBtsSchemaFromMapName(string mapName, SourceOrTarget sourceOrTarget)
        {
            Debug.WriteLine("Getting " + sourceOrTarget + " schema for map " + mapName, typeof(SchemaValidator).FullName);
            int i = mapName.IndexOf(‘,’);
            string transformName = mapName.Substring(0, i).Trim();
            string assemblyName = mapName.Substring(i + 1).Trim();
            Transform transform = explorer.Transforms[transformName, assemblyName];
            if (sourceOrTarget == SourceOrTarget.Source)
                return transform.SourceSchema;
            else
                return transform.TargetSchema;
        }

        /// <summary>
        /// Called when there is a validation error.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        public void DocumentValidationEventHandler(object sender, ValidationEventArgs args)
        {
            validationResult.AddError(args.Message, true);
            Debug.WriteLine(args.Message, GetType().FullName);
        }

        /// <summary>
        /// Called when there is an error loading the XmlSchema itself. That has never happened to me.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        public static void SchemaValidationEventHandler(object sender, ValidationEventArgs args)
        {
            Debug.WriteLine(args.Message, typeof(SchemaValidator).FullName);
            throw new ApplicationException(args.Message);
        }

        private static string GetMgmtDbConnectionString()
        {
            const string regPath = @"SOFTWAREMicrosoftBizTalk Server3.0Administration";
            RegistryKey regKey = Registry.LocalMachine.OpenSubKey(regPath);
            if (regKey == null)
                throw new ApplicationException(string.Format("Registry key {0} not found.", regPath));
            string mgmtDBServer = (string)regKey.GetValue("MgmtDBServer", "(local)");
            string mgmtDBName = (string)regKey.GetValue("MgmtDBName", "BizTalkMgmtDb");
            return string.Format("Server={0};Initial Catalog={1};Integrated Security=SSPI", mgmtDBServer, mgmtDBName);
        }
    }
}

Internal Error – cannot open database “BAMStarSchema” when trying to deploy a BAM activity

When I tried to install ESB Guidance 2.0 CTP2 exception handling, I got the following error from the BAM deployment part of the installation script:

Deploying Activity… Done.
Deploying View… ERROR: The BAM deployment failed.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Server: The operation has been cancelled.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of ‘bam_ExcByApplication’, Name of ‘bam_ExcB
yApplication’.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of ‘ExcByApplication_ExcDatetime’, Name of ‘ExcByApplication_ExcDatetime’
was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Month’ attribute
of the ‘ExcByApplication_ExcDatetime’ dimension from the ‘BAMAnalysis’ database
was being processed.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of ‘bam_ExcByApplication’, Name of ‘bam_ExcB
yApplication’.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of ‘ExcByApplication_ExcDatetime’, Name of ‘ExcByApplication_ExcDatetime’
was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Day’ attribute o
f the ‘ExcByApplication_ExcDatetime’ dimension from the ‘BAMAnalysis’ database w
as being processed.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of ‘bam_ExcByApplication’, Name of ‘bam_ExcB
yApplication’.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of ‘ExcByApplication_ExcDatetime’, Name of ‘ExcByApplication_ExcDatetime’
was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Year’ attribute
of the ‘ExcByApplication_ExcDatetime’ dimension from the ‘BAMAnalysis’ database
was being processed.

It turned out that the SQL Server service account (“SQLServer” in my case) didn’t have read access to BAMStarSchema. Here is a script that fixed it:

USE BAMStarSchema
GO
CREATE USER SQLServer FOR LOGIN [BTS2009SQLServer]
GO
EXEC sp_addrolemember ‘db_datareader’, ‘SQLServer’
GO