BizTalk SQL Adapter

Problem:
How to execute and get response from SQL stored procedure using BizTalk SQL adapter and orchestration

Scenario Details:
We have a stored procedure that accepts customer-id as input parameter and returns customer record from Customer table based upon input customer-id. We want to execute this stored procedure inside an orchestration and get customer record back using BizTalk SQL adapter.

Solution:
For demo purpose we will first create Customer table in db followed by stored procedure creation using following sql:

Table SQL script:

Stored Procedure SQL Script:

Note:
Xmldata keyword (encircled in red color) is added to sql select statement in order to enable BizTalk “Generate Adapter Metadata” wizard generate required scripts, schemas and orchestration so that we can execute this stored procedure within the orchestration and get response back via SQL adapter.

Now create new empty BizTalk project, name it “SQLAdapterTesting”. Right click the project and click on Add -> Add Generated Items… option

On Add Generated Item screen, click on “Add Adapter Metadata” and click “Add” button

On Add Adapter Wizard, select “SQL” under the list of registered adapters, specify SQL Server name and select “BizTalkMgmtDB” as database

On next screen, set connection string by specifying SQL Server name, login type (Integrated security or username & password) and then select the database which contains the stored procedure in question

On next screen, specify “Target namespace” and select port type “Receive port” if you want to receive response (only) from SQL server or select port type “Send port” if you want to send request to SQL server and receive back response

On next screen, select statement type “Stored Procedure”

On next screen, select the specific stored procedure in dropdown list and click on “Generate” button

On next screen, click on “Finish” button to complete wizard

This will result in following two new artifacts added to same BizTalk solution:

1. BizTalk Orchestration.odx
2. SQLService.xsd

SQLService.xsd schema will contain schema for a Multipart message that will be used to send request to SQL adapter and receive response back

BizTalk Orchestration.odx orchestration contains required port types and multi part message types, needed to communicate with SQL adapter and send/receive data

Now go back to SQL server and modify the stored procedure created above by removing “xmldata” keyword from the select statement as it is no longer required after completion of Adapter Metadata wizard.

Now Add new schema (CustomerInquirySchema.xsd) to current project. This schema will be the schema of input message/request with CustomerId in it against which record from the Customer table will be retrieved

Add new schema (CustomerInquiryResponseSchema.xsd) to current project. This schema will represent the final message that will be output to FileSystem

Now create new messages in orchestration as following:

InputMsg

OutputMsg

sqlRequestMsg

sqlResponseMsg

Now add new map (InputMsgToSQLInputMsgMap.btm) to the project with mapping as following:

Above map will be added to the Transform shape in orchestration to convert input message to SQL adapter request message

Add another map file (SQLRespToFileSystem.btm) to the project with mapping as following:

Above map will be added to the transform shape in orchestration to transform SQL adapter response to final output message

Now add following ports to the orchestration:

•  Receive port (to receive input message)
• Two-way SQL Adapter based port (to send request to SQL Adapter and receive response back)
• One way Send port to output receive response (from SQL) to local file system.

After adding above mentioned ports and two transform shapes (for two maps mentioned above respectively), final and complete orchestration should look like:

Now strongly sign project and build + deploy project

Open deployed orchestration in BizTalk and bind send/receive ports. While configuring SQL adapter based send-receive (two way) port, select “PassThruTransmit” as Send Pipeline & “XMLReceive” as Receive Pipeline and finally specify document target namespace and Response document root element node as following:

Note:
Make sure that the document Target Namespace specified in SQL adapter port configuration matches 100% with the Target namespace that was specified in Adapter Metadata Wizard configurations (at the start of this article) and there should be no discrepancy of any letter including / or _ etc

Now Start newly deployed orchestration (after completing port and host bindings) and test it with following sample input message:

Sample Input Message:

Upon successful execution, the orchestration will output following XML response to specified filesystem  path:

Final Output Response:

Conclusion:
BizTalk uses adapters to communicate with outside world. BizTalk SQL Adapter fells under BizTalk DB adapters category. Using SQL adapter to execute stored procedure and get result back has certain advantages over using a component to execute sql stored procedure. Advantages include but not limited to retry attempts option, reliable delivery, success/failure delivery notifications, comprehensive adapter port tracking in BizTalk  and the option to specify alternative delivery mechanism in case of failure/error

Invalid Project Location – Unknown error 0x80005000 – BizTalk

Problem:

BizTalk web services publishing wizard generating error Invalid Project location / Unknown error 0x80005000 when trying to publish BizTalk orchestration as a WCF service

Solution:

Go to Turn Windows features on or off and install / enable “IIS 6 Management Compatibility” (and its sub-components) under Web Management Tools

Restart IIS and BizTalk service.

 

XML validation in BizTalk

Problem:

You want to enable XML message strict validation (i.e. validation of xml message beyond checking for well-formed XML) in BizTalk.

Solution:

In order to implement strict validation of XML messages in BizTalk server you will have to implement and use XML validation pipeline.

The default XMLReceive receive pipeline or XMLTransmit send pipeline can do XML message validation but that validation is confined to checking only whether given xml message is well-formed XML or not. It can’t do strict validation (i.e. validation of data types in XML message against specified XML schema or validation of restriction values like length, enumerations, patterns etc).

In order to enable strict validation, you can create and use validation pipeline in BizTalk and add an XML validator component in its validation stage. If no validation schema is specified in custom validator component settings then BizTalk will try to select appropriate schema on its own depending upon XML message type (i.e. combination of namespace and root node). If no schema found for validation or if document validation fails then pipeline will generate error

Exception occurred when persisting state to the database – BizTalk Error

Problem:

BizTalk generated error message ‘Exception occurred when persisting state to the database’

Resolution:

There are multiple possible scenarios in which BizTalk server may generate this exception/error.

Two different scenarios that I came across are as following:

1) Missing Receive Port i.e. An orchestration is trying to “Send” a message to a direct bound Send port but there is no direct bound Receive port to receive (or subscribe) the message being sent.

In this case, we can fix this error by creating or enabling a direct bound Receive port that can subscribe to the actual message being sent/published.

2) Missing Subscription i.e. You have recently added a new adapter to BizTalk server and you are trying to send out message using a Dynamic send port (that was enlisted & started before you added new adapter) and you get “Exception occurred when persisting state to the database” error in BizTalk

In this case, we can fix this error by unenlisting the Dynamic Port in question and then enlist and start it again. This will allow Dynamic Send port to refresh its subscription by taking into account any newly added adapter(s) and it will fix error.

 

 

BizTalk Positional Schema With Multiple Records

Problem:
How to map multiple record XML file to a multiple record positional file in BizTalk

Scenario Details:
Consider a scenario where we have an XML source file comprising of data of multiple employees and we need to convert this XML source file into a positional file such that each employee’s record in source XML become a record/row in output positional file.

Sample source XML file looks like following:

Solution:
Steps to perform are as following:

1. Create new BizTalk project in visual studio

2. Create an XML schema corresponding to the source xml file and add to the project. Sample XML schema given below :

3. Create Flat File schema corresponding to output file and add to the project. Sample Flat File schema given below:

4. Click on “Root” element in Flat File schema and set its Structure property to “Delimited” and then set its Child Delimiter Type, Child Order and Child Delimiter properties as show below:

Note: “0x0A” is hexadecimal representation of new line character.

5. Click on “Row” element in Flat File schema and set its Max Occurs property to “unbounded”, Structure property to “Delimited” and Child Delimiter Type property to “None”

6. Click on “EmployeeData” element in Flat File schema and set its Structure property to “Positional”

7. Click on each element under EmployeeData record in Flat File schema and specify appropriate Positional Length & Positional Offset values

8. Add new map to the project and select XML schema as source schema and FF schema as destination schema in newly created BizTalk map file

9. Map fields in source and destination schemas as shown in below screen shot:

Note the use of looping function to enable repetition of ‘Row’ in FF schema for each ‘Employee’ in source XML schema

10. Now go the properties of the newly created map and specify input sample xml file instance path and output file instance path and finally click on Test Map option to actually execute map and generate FF output file. Sample output flat file will look like as following:

BizTalk WCF Custom Behaviors

Problem:
How to implement custom behaviors (for WCF adapter) in BizTalk

Scenario Details:
In one of our BizTalk based solutions, there was a requirement to enable TLS 1.2 at send port using WCF adapters. Default configurations of BizTalk WCF adapters does not have any option to enable specific version of TLS/SSL at send port level. We were able to meet this requirement by developing WCF custom behavior with required functionality and then using that custom behavior inside WCF custom adapter configurations of the send port.

Resolution:
Following are the steps to develop custom behavior:

1. Create new C# class library project

2. Create a new class file to implement custom behavior logic/code. Make sure class implements IEndpointBehavior interface. Modify ApplyClientBehavior function implementation to enable TLS 1.2 based transport/communication:

Sample code as following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net;

namespace WCFCustomBehaviorForTLS
{
    public class WCFCustomBehavior : System.ServiceModel.Description.IEndpointBehavior
    {

        void System.ServiceModel.Description.IEndpointBehavior.AddBindingParameters(System.ServiceModel.Description.ServiceEndpoint endpoint, System.ServiceModel.Channels.BindingParameterCollection bindingParameters)
        {
            throw new NotImplementedException();
        }

        void System.ServiceModel.Description.IEndpointBehavior.ApplyClientBehavior(System.ServiceModel.Description.ServiceEndpoint endpoint, System.ServiceModel.Dispatcher.ClientRuntime clientRuntime)
        {
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
        }

        void System.ServiceModel.Description.IEndpointBehavior.ApplyDispatchBehavior(System.ServiceModel.Description.ServiceEndpoint endpoint, System.ServiceModel.Dispatcher.EndpointDispatcher endpointDispatcher)
        {
            throw new NotImplementedException();
        }

        void System.ServiceModel.Description.IEndpointBehavior.Validate(System.ServiceModel.Description.ServiceEndpoint endpoint)
        {
            throw new NotImplementedException();
        }
    }
}

3. Next step is to define behavior extension element. To do so, create new class file that will  inherit BehaviorExtensionElement class and override BehaviorType & CreateBehavior methods of the base class in its implementation

Sample code as following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WCFCustomBehaviorForTLS
{
    class CustomBehaviorExtensionElement : System.ServiceModel.Configuration.BehaviorExtensionElement
    {

        public override Type BehaviorType
        {
            get
            {
                return typeof(WCFCustomBehavior);
            }
        }

        protected override object CreateBehavior()
        {
            return new WCFCustomBehavior();
        }
    }
}

4. Sign & build class library and GAC the dll

5. Add appropriate entry in machine.config file (both 32 bit & 64 bit machine.config). Sample entry as following:

6. Restart BizTalk host instances and Administration Console

7. Go to send port, select “WCF-Custom” adapter under transport type, click on “Configure” button and then click on “Behavior” tab

8. Right Click “EndPointBehavior” and click on “Add Extension” option and you should be able to see and select your custom behavior name in the appearing list of available behavior extensions

Address should be unique for Receive Location

Problem:

Getting error message “Address should be unique for Receive Location” when trying to create more than one receive locations (that are using SQL adapter) in BizTalk and pointing to same database.

Scenario Details:

Getting error message “Address should be unique for Receive Location” in BizTalk server while working on a project/scenario where multiple receive locations need to be created in BizTalk server such that all those receive locations will be using SQL adapter and will be pointing to same DB server & same database.

All those receive locations will be executing different stored procedures to pull out data from the DB but those stored procedures will be in same database on same DB server.

We were getting this error because receive location URI was same in all receive locations (due to the fact that all receive locations were pointing to same DB server and same DB) and BizTalk doesn’t allow creating more than one receive locations with same URI regardless of the adapter being used in receive locations.

Resolution:

A workaround to this problem is to manually edit or modify the receive location URI by appending some query parameters to the URI (regardless of whether actual stored procedure accepts any input parameters or not).  The query parameters should be unique for reach receive locations so that complete URI for reach receive location become unique/different.

Example – suppose you want to create three different receive locations that will point to some DB Server (e.g. DBServerABC) and database (e.g. databaseXYZ) then you can do it by appending some parameter e.g. MyParam (or any other name that you deem appropriate) to the receive location URI where each parameter has a different value. Example URI are as following:

mssql://DBServerABC/databaseXYZ?MyParam=1
mssql://DBServerABC/databaseXYZ?MyParam=2
mssql://DBServerABC/databaseXYZ?MyParam=3

BizTalk BAM (Business Activity Monitoring)

BAM
BizTalk BAM (Business Activity Monitoring) is a real-time data tracking solution/mechanism. It provides a mechanism to capture real-time or run-time data and have it stored in separate database (and tables) for later access or retrieval.

BAM enables business (or non-technical) users to have visibility to desired data in a BizTalk based solution. BAM data can later be used for different tracking and/or reporting purposes.

When BAM is enabled/configured then required data is moved from BizTalk Message Box to BAMPrimaryImport database on-the-fly. It is the job of TDDS (Tracking Data Decode Service) to move required data from MsgBox to BAMPrimaryImport database.

BAM Activities
An activity in BAM represents a business process such as CreateCustomer, DepositPayment or AddAccount etc.

BAM activity consists of Business data and Milestones where Business Data can be a specific Business/Process related data or value or key (e.g. CustomerNumber, RequestID, Price, AccountNumber, ItemStatus etc) and the Milestone is date/time specific data about the business process  (e.g. StartTime, EndTime, ReceieveTime, SendTime, FailureDateTime etc).

Milestone items have datetime data type and Business Data have Business Data-Text/Integer/Float data type.

BAM Views
Views in BAM is a collection of activities that can return/show user’s required information. You can create different BAM views to show different data to different users. To define BAM view you need to have at least one BAM activity already created/defined.

BAM Implementation
At a higher level, BAM implementation involves following steps:

1. Define BAM activities & views
2. Deploy BAM Definition
3. Create TPP file using TPE (Tracking Profile Editor)

Defining BAM Activities & Views
BAM Activities & Views can be defined using XLS. BAM activities & views are defined using BAM Add-on in XLS. Once the the view & activity are defined, they can easily be exported to an XML file as BAM definition with the help of same XLS BAM add-on.

To find out how to define BAM activities & views using XLS please refer to Microsoft document defining-business-activities-and-views-in-excel

Deploy BAM Definition
Once the BAM definition has been created, it needs to be deployed to target server. To find out how to deploy BAM definition please refer to Microsoft document how-to-deploy-bam-definitions

Configure Tracking Profile Editor file
Use TPE (Tracking Profile Editor) to bind deployed BAM definition with BizTalk artifacts such ports, schema or orchestration to initiate realtime collection of required data.

TPE is a graphical user interface that can be used to connect BAM activity with data source (orchestration or port). To find out how to use or configure TPE please refer to Microsoft document using-the-tpe

BizTalk Convoys

Convoys
Convoys in biztalk are used when multiple messages needs to be correlated to one particular instance of orchestration and a race condition might arise as the messages might arrive at the same time and only one of them can initialize the correlation set and the rest of the messages needs to follow the correlation

Types of Convoys in BizTalk:

• Sequential Convoys
  
• Parallel Convoys

In case of sequential convoys, messages are correlated (to single instance of orchestration) and processed sequentially.

Parallel convoys are used where you need to correlate a known number of messages to single instance of orchestration however the orders in which messages will arrive is not known.

Sequential Convoy Example:
Sequential convoys are implemented using Loop shape inside orchestration where the loop terminates after specific iterations or after specific time interval has lapsed.

Most common application of sequential convoy is aggregator pattern where you might want to aggregate multiple messages into a single message or batch.

Let’s have a look at a very simple sequential convoy example. Consider a scenario where we will be receiving multiple invoices from backend ERP and we want to aggregate/batch received invoices based upon Bill-To Party name in invoices so that we can send a whole batch/envelope of invoices to each Bill-To party instead of sending several individual invoices.

First we need to create/define schemas to be used in sequential convoys. This example will use schemas as shown below:

INVOICE – Schema:

BillToParty element in above schema is a promoted property and will be used in defining correlation type and correlation sets in sequential convoy.

INVOICE ENVELOPE – Schema:

Make sure you set “Envelope” property of above schema to “Yes” in biztalk to mark it as an envelop schema so that messages (invoices) are grouped in this schema

Make sure you set “Body XPath” property of Company element to an appropriate URI (as visible in above screen shot’s XSD also)

The “CompanyName” element in above schema is a distinguished field

Property Schema:

We need to define a custom send pipeline with only one component (XML Assembler) in it as shown below:

Make sure you assign Invoice schema to the “Document Schemas” property of XML assembler in the custom send pipeline

Make sure you assign Invoice Envelope schema to the “Envelope Schemas” property of XML assembler in the custom send pipeline

Create new orchestration as shown below:

Define correlation type as shown below:

Define correlation set:

Define messages in the orchestration as following:

Define variables in orchestration as following:

The first receive shape in the orchestration is Activation receive shape and will initialize correlation set and start orchestration instance upon receiving first invoice.

In next expression shape first received message will be added to SendPipelineInputMessage collection

Next receive shape inside the orchestration will follow the correlation set and will keep adding received messages to SendPipelineInputMessage collection

After loop iterations are over, aggregated output message will be constructed by executing custom send pipeline the project as shown below:

If there is any exception during pipeline execution then the error message will be logged into the event log and orchestration will be terminated otherwise the aggregated messaged (batch of invoices for a particular BillTo party) will be output by the send port

Screen shot of sample input (XML invoice instance) is as following:

Screen shot of output aggregated XML message is as following:

Failed to add resource(s). Resource is already in store and is either associated with another application or with another type – BizTalk Error

Problem:

BizTalk solution re-deployment attempts fails with error message “Failed to add resource(s). Resource (-Type=”System.BizTalk:BizTalkAssembly” -Luid=”*******.**********, Version=1.0.0.0, Culture=neutral, PublicKeyToken=30876c1f77331db8″)  is already in store and is either associated with another application or with another type”.

Visual studio screen shot showing error is as following:

Resolution:

After little bit troubleshooting, we figured out that the error surfaced because the target application name in the deployment tab of one of the biztalk projects in the solution was mistakenly left empty and hence the resource (assembly) was deployed to default application “BizTalk Application 1”.

This resulted in getting above mentioned error in all subsequent deployment attempts. We were able to fix this issue as following:

• Ensure that valid target application name is specified in deployment tab of all projects in biztalk solution
• Delete/remove previously deployed application in BizTalk administration console
• Individually select and then delete/remove the assembly file from application BizTalk Application 1
• Redeploy solution to BizTalk