Matt Meleski's .Net Blog - The ABC's of .NET

Robust Error Handling for BizTalk Solutions Presentation

2007-11-07T01:01:00Z

I presented at the Microsoft SOA and Business Process Conference last week in Redmond. The title of the presentation was:

Robust Error Handling for BizTalk Solutions.

I did the presentation once on Thurs Nov 1 and again on Friday Nov 2. A number of people who attended the
presentation were asking for the demo code and powerpoint. The zipped code is here and the powerpoint from the presentation is here. Hopefully when I have more time, I will try to write a more formal blog entry describing some of the techniques for error handling in BizTalk. Also for anybody who attended the conference and missed my presentation, you can catch the recorded presentation on a post conference DVD, that should be mailed out to all attendees in the next month or so.

BizTalk -> Custom Pipeline Component for Processing DBF, Excel and other ODBC types

2007-06-15T12:27:00Z

I deleted the original post by mistake (woops!), so below is to replace the deleted one.

Last year a customer had a requirement to process DBF files in BizTalk. I created a custom pipeline component that saved the incoming binary stream to a physical file on the BizTalk machine and then used basic ADO.NET to parse the DBF File into an XML document. I then modified/extended this pipeline component to accept and parse other ODBC files to XML, such as:

DBF
Excel
FoxPro
Possibly others such as Access Files.

At this point in time, this custom pipeline component will only parse Excel and DBF files, but it is possible to modify the component to process other ODBC types.

By using this custom pipeline component in a BizTalk Receive Pipeline it will do the following:

Raw DBF, Excel messages are delivered to BizTalk by any transport such as:
File
FTP
MSMQ
etc. etc.

The raw message will be parsed to XML in a BizTalk Receive Pipeline with the parsed XML message published into the MsgBox.

This component requires no special APIs and uses basic ADO.NET code to parse the ODBC type files into XML.

You can download the full source code for the Custom Pipeline component at the end of this entry.

The component works as below:

1) The incoming file is saved to a temporary file on the BizTalk machine.
2) An OLEDB connection will be used to connect to the file from 1).
3) A Sql query is performed against the OLEDB datasource.
4) The results from the query are stored to an ADO.NET dataset/datatable.
5) The XML is extracted from the datatable and modified for a root node name and target namespace.
6) The temporary file from 1) is deleted
7) The XML from 5) is added back to the pipeline message stream.

The custom pipeline component was coded as a Decoder pipeline component, but it could be modified to implement a Disassembler pipeline component.

The Custom Pipeline Component exposes a number of properties for dynamic configuration.

The connection string and query differs slightly for an Excel and DBF file. Therefore the configuration for an Excel file and DBF file are discussed separately:

Excel

The incoming Excel file to be parsed looks as below:

The resultant parsed XML file will look as below:

Note: Only two Employee nodes are present in the XML file due to a filter condition in the configuration (see below).

The Configuration for this Pipeline is as below:

1) ConnectionString -> The OLEDB Connection string for the Excel file.
The following is set for the ConnectionString property:
Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;
But, the final Connection String that is produced by the code looks like below:
Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=C:\Temp\afgd1234.xls

This is because the code, dumps the Excel File to the TempDropFolderLocation and must dynamically add the Data Source section to the connection string.

Note : Other Connection Properties for an Excel File:

"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
(Above From: http://www.connectionstrings.com/ )

2) DataNodeName -> The XML Node name for the Data. In this case Employee

3) DeleteTempMessages -> If set to True, will delete the Excel file that is dropped to the TempDropFolderLocation after processing.

4) Filter -> Filter for the SqlStatement. In this case, will only Select LastNames Like %B%
Note: This is optional. If all data is to be returned, leave blank.

5) Namespace -> NameSpace for the resultant XML message.

6) RootNodeName -> Root Node Name for the resultant XML Message.

7) SqlStatement -> OLEDB Select Statement.
SQL syntax: SELECT * FROM [sheet1$] - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
(Above From: http://www.connectionstrings.com/ )

Note: The SqlStatement could also look as below:
Select FirstName,LastName FROM [sheet1$] (only bring back selected columns)
Select FirstName as FName, LastName as LName FROM [sheet1$] (rename the column Names in the resultant XML)

8) TypeToProcess -> In this case Excel File.

DBF

The incoming DBF file to be parsed looks as below:

The resultant parsed XML file will look as below:

Note: Only two Items nodes are present in the XML file due to a filter condition in the configuration (see below).

The Configuration for this Pipeline is as below:

Note: The above is an example of Per Instance Pipeline Configuration for BizTalk 2006.

1) ConnectionString -> The OLEDB Connection string for the DBF file.
The following is set for the ConnectionString property:
Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;
But, the final Connection String that is produced by the code looks like below:
Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;Data Source=C:\Temp\

This is because the code, dumps the DBF File to the TempDropFolderLocation and must dynamically add the Data Source section to the connection string.

2) DataNodeName -> The XML Node name for the Data. In this case Items

3) DeleteTempMessages -> If set to True, will delete the DBF file that is dropped to the TempDropFolderLocation after processing.

4) Filter -> Filter for the SqlStatement. In this case, will only Select PRICE >= 200 and PRICE <=500
Note: This is optional. If all data is to be returned, leave blank.

5) Namespace -> NameSpace for the resultant XML message.

6) RootNodeName -> Root Node Name for the resultant XML Message.

7) SqlStatement -> OLEDB Select Statement.

In this case only have the columns part of the Select Statement as below:
Select *

This is because the code dumps the DBF File to the TempDropFolderLocation and must dynamically add the FROM statement as below:
SELECT * FROM i0lb1gcr.dbf

Note: The SqlStatement could also look as below:
Select COD, PRICE (only bring back selected columns)
Select COD as Id, Price as Amount (rename the Node Names in the resultant XML)

8) TypeToProcess -> In this case DBF File.
Note: When configuring a Pipeline Component in the BizTalk Server 2006 Administration console,
for TypeToProcess :
0 -> Excel
1 -> DBF

You can download the code Here. Before installing, look at the Readme
Note: This code was written in VS2005. If you want to use it in VS2003, create a new Pipeline type of project in VS2003 and then just copy the code from the DecodeODBC.cs to the VS2003 class. Also thoroughly test the code before using.

Finally:

The not so good things about this Component are:

1) It has to write the ODBC file locally to disk before parsing. This will create
extra disk I/O. I did test it with multiple submissions of 1 MB DBF files. The performance still seemed
pretty good.

2) The types of Excel files it can process are flat. If you're Excel files to process are
complex, not sure how well this Component will parse to XML.

The good things about this component are:

1) The code to parse the ODBC files is dead simple, looks something like the below:

OleDbDataAdapter oCmd;
// Get the filter if there is one
string whereClause = " ";
if (Filter.Trim() != " ")
   whereClause = " Where " + Filter.Trim();
if (this.TypeToProcess == odbcType.Excel)
   oCmd = new OleDbDataAdapter(this.SqlStatement.Trim() + whereClause, oConn);
else // dbf
   oCmd = new OleDbDataAdapter(this.SqlStatement.Trim() + " From " + filename + whereClause, oConn);
oConn.Open();
// Perform the Select statement from above into a dataset, into a DataSet.
DataSet odbcDataSet = new DataSet();
oCmd.Fill(odbcDataSet, this.DataNodeName);
oConn.Close();
// Write the XML From this DataSet into a String Builder
System.Text.StringBuilder stringBuilder = new StringBuilder();
System.IO.StringWriter stringWriter = new System.IO.StringWriter(stringBuilder);
odbcDataSet.Tables[0].WriteXml(stringWriter);

2) This code can be modified to process other types of ODBC files. The modifications
may be minor.

3) You can filter the data in an incoming Excel or DBF file.

BizTalk R2 RFID and working with a real Reader Device (Phidget)

2007-06-09T18:07:00Z

This entry discusses using a real RFID reader (Phidget) with BizTalk 2006 R2 RFID Beta 2. There are a couple of RFID samples provided with the RFID install, but they involve simulators for RFID devices. If you want to try a real RFID reader sample with BizTalk 2006 R2 RFID, read below.

Jim Bowyer, a Senior Technical Specialist for Microsoft based in Calgary, sent out a short note about an inexpensive RFID reader that has a community DSPI (Device Service Provider Interface) for BizTalk R2 RFID.

So I ordered the RFID reader, downloaded the DSPI and then tried against my BizTalk 2006 R2 RFID Beta 2 installation.

If you want to try yourself, perform the following steps:

1) If not already done so, register for public BizTalk 2006 R2 Beta 2, then download.
Follow the instructions to install BizTalk RFID.

2) Order the PhidgetsRFID Reader (USB)

I opted to order the Phidget RFID Kit, that includes a set of read only tags.

I live in Toronto, Canada, so the total cost for the RFID kit was:

$79.99 (US) Kit
$49.74 (US) Shipping
$11.98 (CAN) Customs Fees
-------------------
$141.71

If you live in the States, you will probably get a cheaper total cost, for reduced shipping fees and no customs charges. I ordered the kit online on Friday, and received it the next Tuesday. So approximately 2 Business days for delivery.

Below is the image of what you get in the kit:

The left hand side of the picture contains the various RFID tags, and the right side is the actual RFID reader. A USB cable is also included, so you can connect the RFID reader to a free USB port on your computer.

3) Connect the Phidget RFID reader to your computer via the USB cable.

My host machine is Windows XP. The Phidget device was picked up immediately. No extra drivers etc. were needed.

As below, I have BizTalk 2006 R2 Beta 2 and BizTalk RFID installed on a Windows 2003 VMWare image.
So as below, the extra step in this case was to configure the VMWare image to pick up the Phidgets USB device.

4) Download and install the Phidget DSPI from Irving De la Cruz's Blog

http://blogs.msdn.com/irvingd/pages/biztalk-rfid-device-provider-dspi-for-phidget-devices.aspx

The instructions provided with the download are top notch and I had it up and running within a few minutes. To install the DSPI, Irving provides a script file
or a well documented manual process using the RFID Manager console. I used the manual process to install and had only a couple of minor problems as described below:

After installing the Phidget Device provider, it would not start (received RFID Manager Error). See below:

During the configuration of Phidget Device provider, an IIS virtual directory hosting a number of WCF services is created as below:

As below, when trying to browse to one of the services

A Service Unavailable error was reported (see below)

To fix:

On my Windows 2003 Server, WSS (Sharepoint) Services 2.0 was also present (from a previous install) along with RFID.
Therefore as below, I excluded the PhidgetProvider URL from the WSS managed paths.
Also for the PhidgetProvider Virtual Directory, I changed the Application Pool to one that runs under an Administrator account (just to get it to work). Once this was done, the PhidgetProvider would start in the RFID Manager.
To recap, if you are having problems starting the Phidget Provider, ensure that you can successfully browse without error to one of the
.svc services, before trying to start the Phidget Provider.

Another problem I had similar to the one above:
After configuring the Sql Sink Process to capture the RFID reads (using the BizTalk RFID Manager), the process would not start.
As below, another IIS virtual directory is created hosting a number of WCF services. As explained above with the PhidgetProvider service, errors were reported when trying to
browse to one of the TestProcess .svc services. Therefore as above with the PhidgetProvider URL, I excluded the TestProcess URL from the WSS managed paths
and fiddled with the permissions of the App Pool that the service was running under. When I could successfully browse to one of the .svc TestProcess
services, the TestProcess successfully started in RFID manager.

5) Test

After following the instructions in Irving's documentation, and the Phidget Device Provider, Phidget Device and Process to capture the reads are all enabled and have started successfully without errors (use the BizTalk RFID manager to check):
As below to test, place one of the tags within a few inches of the reader.

Then as below to see if it worked, using the RFID manager you can view the read tags with the View Tags dialog.

As above in the dialog, each tag has a unique Tag Id associated.

Final thoughts:

- Easy to set up.
- The RFID reader is inexpensive, shipping costs may be expensive though, depending on where you live.
- So far has been stable.
- A great way to prototype/experiment with BizTalk RFID and a real RFID device.

SqlBulkInsert Adapter for VS2003 and BizTalk 2004

2007-04-19T00:13:00Z

A few people have asked for the BizTalk 2004 version of this BizTalk SqlBulkInsert 2006 Adapter

Follow the below instructions to install on BizTalk 2004:

1) Download the zip file at the end of this blog entry.
2) When unzipping, unzip it a different folder such as: C:\temp
3) Copy the unzipped contents -> C:\Temp\BTSBulkLoad\SqlBulkInsertAdapter directory
to the C:\BTSBulkLoad\ directory that was created by the BizTalk 2006 zip file that you can find here:
(Look at the bottom of the above referenced blog entry for the download and Readme instructions).
Just replace the VS2005 C:\BTSBulkLoad\SqlBulkInsertAdapter folder contents with the VS2003 SqlBulkInsertAdapter directory contents.
4) Follow the below installation instructions for the BizTalk 2006 adapter to install the BTS 2004 adapter

Remember that this is just a prototype, so you will have to fix the code to get it to work properly. Peter (see comments from this blog entry) has stated that he has a BizTalk 2004 version of the adapter working.
Download the BizTalk 2004 adapter (Zip file) here

BizTalk Message Helper Methods

2007-04-16T23:21:00Z

This entry describes two helper methods to interact with messages in a BizTalk orchestration. The first helper methods will return the string representation of a BizTalk message. The methods are as below: /// <summary> /// Pass in a BizTalk Message will return a string /// </summary> /// <param name="btsMessage">The BizTalk message to get the string from</param> /// <returns>The string from the Body of the BTS Message</returns> public static string GetStringFromBTSMessageBody(XLANGMessage...(read more)

Turning an Immutable Message in BizTalk into a Mutable message

2007-04-09T22:19:00Z

One thing that you learn pretty fast in BizTalk is that messages in an orchestration are immutable/read only.

If you need to modify a message in a BizTalk orchestration, you are pretty well restricted to using a Construct shape with encapsulated Transform and/or Message Assignment shapes to create a modified version or a copy of the original message. Distinguished fields,xpath statements, BizTalk maps, custom .Net components, etc. can be used to modify the message.

Below is one simple technique that can be used to modify a message anywhere in an orchestration.
Helper class(s) are required, but in certain situations (explained below) this technique can be used to easily modify a message anywhere in an orchestration.

Below is an orchestration where this technique is used:

Very simply this orchestration subscribes to a PO xml message and then produces a
final Invoice XML message that is send out from the orchestration.

Below are the artifacts used in the solution:

The artifacts for the BizTalk project, BTS_Immutable_To_Mutable include:

1) ClassOrchestration.odx (Orchestration as above)
2) Invoice.xsd (schema for outgoing Invoice XML message)
3) PO.xsd (schema for incoming PO XML message).
4) InvoiceClassGen.bat and POClassGen.bat

Below is the InvoiceClassGen.bat file:

The above uses the .Net xsd.exe utility to generate an Invoice.cs class from the Invoice.xsd schema.
This Invoice.cs class is used in the Class_Immutable_To_Mutable project as below.

The artifacts for the Class Library project, Class_Immutable_To_Mutable include:

1) Helper.cs (Helper Class to populate the some of the fields of the Invoice)
2) Invoice.cs (Invoice class for variable in the orchestration)
3) PO.cs (PO class for variable in the orchestration)

This orchestration will:

1) Accept a PO xml message

2) As below, in an expression shape, assign the BTS PO message to a BTS variable message of type PO.cs

// Set the BTS Variable PO to the incoming BTS Message PO
varPO = msgPO;

3) As below, in an expression shape, populate some of the Invoice fields from the PO fields:

// Populate some of the fields in the BTS Invoice Variable,
// from the BTS PO variable fields.
varInvoice.TotalAmt = varPO.Amt;
varInvoice.TotalCount = varPO.Qty;

4) As below, in an expression shape, call a helper class to populate and return the Invoice Items class:

varInvoice.Items = Class_Immutable_To_Mutable.Helper.mapPOItemsToInvoiceItems(varPO);

5) As below, in an expression shape, call a helper class to return and assign the description for the invoice Description field.

// Set the BTS Variable Description field
varInvoice.Description = Class_Immutable_To_Mutable.Helper.GetInvoiceDesc(varInvoice);

6) As below, in an expression shape, call a helper class to return and assign the version for the invoice Version field:

// Set the BTS Message Invoice Version field
varInvoice.Version = Class_Immutable_To_Mutable.Helper.GetInvoiceVersion();

7) Finally at the end, in a Construct/Message Assignment shape, construct the the outgoing BTS Invoice message:

// Create the BTS Invoice message from the Variable Invoice message
msgInvoice = varInvoice;

8) Send out the Final Invoice XML message

So after all of this, could a BizTalk map been used to create the Invoice message from the PO message. The answer is yes or no depending on the mapping logic that is needed.

This leads to when use this method:
Creation of a message requires multiple calls to Helper components/Business Rules to create the message.

Some of the upsides to using this approach are:
1) Using the above technique takes away the restriction of the immutable message and working with a mutable variable in the orchestration.
2) Intellisense is available on the variables inside of the orchestration.
3) The variable can be modified directly in an expression shape inside of the orchestration, without the use of distinguished fields or xpath statements.

Some of the downsides to using this approach are:
1) The overhead of deserialization and serialization from Message to Variable and visa versa.
2) Creating and maintaining the Helper classes (in this case PO.cs and Invoice.cs)

You can download the above example HERE (Zip File).
Read the Readme before installing and running the example.

For a similar example, goto Here
and download the Using a Custom .NET Type for a Message in Orchestrations example.

Follow up to Fundamentals of WF Presentation and Developer Resources for WF

2007-03-24T18:59:00Z

A few people asked for the demos from this presentation.

Links for the presentation are at the bottom of the post

If you have not already set up your development environment for Windows Workflow Foundation (WF) Development, follow the below steps:

Prerequisites:

a) Windows XP, Windows 2003, Windows Vista
b) Visual Studio 2005

Install the following:

1) .Net Framework 3.0 Redistributable
2) Visual Studio 2005 extensions for .NET Framework 3.0 (Windows Workflow Foundation)
3) You can also optionally download and install:
Microsoft® Windows® Software Development Kit for Windows Vista™ and .NET Framework 3.0 Runtime Components

Before installing the above read the provided instructions.

Below are some resources for using/learning/developing with WF:

MSDN - Windows Workflow Foundation
MSDN - Windows Workflow Foundation Tutorials
MSDN - Windows Workflow Foundation General Reference
Getting Started with Windows Workflow Foundation Server Virtual Lab
Hands-on Labs for Windows® Workflow Foundation
Clinic 5136: Introduction to Developing with Windows® Workflow Foundation and Visual Studio® 2005
Windows Workflow Foundation Developer Centre

Also please read Paul Andrews blog (Windows Workflow Foundation Technical Product Manager at Microsoft) for the latest and greatest on WF.

Download the presentation demos here.
Please read this before trying to run.

MVP Summit 2007

2007-03-24T01:54:00Z

There was a delay in posting this, but just wanted to write a quick note on the MVP Summit that I attended last week (March 12 - March 15)

Overall this was an awesome event, well organized, great sessions.

Thanks to Sasha Krsmanovic (MVP Lead - Canada) who provided all the Canadian MVP's with Red Olympic Hockey Jerseys worn on the 2nd day of the summit. This really pumped up the Canadian MVPs at Bill Gate's key note that carried on during the rest of the Summit. I received quite of few comments from other non Canadian MVPs about the jersey even when I wasn't wearing the jersey on day 3 of the summit

First couple of days at the summit I was hanging out with fellow MVP Objectsharpees
(past/present and future?):
Barry Gervin
Bruce Johnson
Rob Winsdor
Matt Cassel
Jean-Luc David
Justin Lee

Next couple of days of the summit, I was at the BizTalk/ Connected Systems Division specific sessions.

Below are some of the BizTalk MVPs that attended the summit. Sorry if I did not list everyone.

Tomas Restrepo
Brian Loesgen
Scott Cairney
Ibrahim Gokalp
Stephen Thomas
Alan Smith
Romualdas Stonkus
Jon Flanders
Jesus Rodriguez
Mick Badran
Scott Colestock
Jon Fancey
Jeff Juday
Charles Young

Here are a few posts on the content of the BizTalk/Connected System MVP sessions.

Day3
Day3
Day4

Finally a big thanks to Marjan Kalantar (Microsoft's Connected Systems Division Community
Lead), who put together and organized an eclectic spread of talks that covered the Connected
Systems Division and the informal sessions with the Product groups.

Fundamentals of WF

2007-03-19T22:59:00Z

Tomorrow (March 20) I am doing a presentation at the Metro Toronto .Net User Group entitled:
Fundamentals of Windows Workflow Foundation (WF).

Most of it will be demos introducing WF. Of course, I will also include BizTalk in the presentation, discussing the differences and similarities between WF and BizTalk and when it is appropriate to use either technology.

The demos will include:

Creating a Sequential Workflow
Communicating with the Host
Logging Workflows
Persisting Workflows
Creating Custom Activities
Creating a State Machine Workflow
Using WF and BizTalk together

You can sign up for the presentation here:
http://www.metrotorontoug.com/User+Group+Events/379.aspx

Failed Message Routing and Failed Orchestration Routing in BizTalk 2006

2006-11-01T20:51:00Z

This post discusses Failed Message Routing and Failed Orchestration Routing in BizTalk 2006

Failed Message Routing

Failed Message Routing is a new feature of BizTalk 2006.
You can read about it here: Using Failed Message Routing

Below is an excerpt from the above help topic:

What Does Failed Message Routing Consist Of?

When failed message routing is enabled, BizTalk Server does not suspend the message—it routes the message instead. Failed message routing can be enabled on both receive and send ports, with the following results:
If failed message routing is enabled on a receive port and a message fails in the receive pipeline or in routing, a failed message is generated. In the case where an error occurs in or before the disassembly phase, the error message is a clone of the original interchange. If failed message routing is enabled on a send port and the message fails in the send pipeline, a failed message is generated. When a failed message is generated, BizTalk Server promotes error-report-related message context properties and demotes regular message context properties before publishing the failed message. Compare this to the default behavior when failed message routing is not enabled: Messages that fail are suspended.

There is much more information in the above article. I would highly recommend reading it. One of the properties promoted on a failed message is:

ErrorReport.ErrorMessage = "FailedMessage"

You can then subscribe to a failed message using a Send Port or Orchestration by filtering on the above property.

Failed Orchestrations Routing

There is no such thing. If an orchestration suspends, the orchestration and its contained messages will become suspended. If you have an orchestration or send port with a filter such as:
ErrorReport.ErrorMessage = "FailedMessage"

It will not subscribe to the failed orchestration. No failed message is automatically generated that can be subscribed to on orchestration failure. But, if you have set up a subscriber that is set up for Failed Message Routing and would like a message generated by the Failed Orchestration to be routed to the same subscriber, you can do the below:

1) As below, add a scope shape and exception block in the orchestration to catch exceptions in the orchestration:

2) In the exception block construct a new message in the orchestration. While constructing the new message, promote the following property:

msgError(ErrorReport.ErrorType) = "FailedMessage";

See below for an example:

3) As below, use the orchestration view to create the following Correlation Type.

4) As below, use the orchestration view to create a Correlation Set that derives from the Correlation Type.

5) As below add a Send shape to send out the newly constructed message. For the properties of the Send Shape, initialize the correlation set from 4).

6) As below, link the send shape to a Logical Send port in the orchestration that will do a direct send into the messagebox.

7) The ErrorReport.ErrorType is now promoted on the error message and you can now subscribe to it, using a filter condition as:

ErrorReport.ErrorType = FailedMessage
(if using a send port)
Or:
ErrorReport.ErrorType = "FailedMessage"
(For a Receive Shape in an orchestration.)

Note: You can also promote your own custom context property as described Here
There is an example that you can download Here. Read the Readme before installing.

Dynamically Configuring Pipeline Component Properties in a BizTalk Orchestration

2006-10-09T20:04:00Z

This entry discusses the use of the following to programmatically alter a receive or send pipeline component(s) properties:

BTS.SendPipelineConfig
BTS.SendPipelineResponseConfig
BTS.ReceivePipelineConfig
BTS.ReceivePipelineResponseConfig

There is a link at the end of this entry to download a couple of examples.

Before discussing the above properties, two BizTalk 2006 pipeline enhancements are:

1) The execution of send and receive pipelines in orchestrations. This feature allows a receive or send pipeline to be executed without the use of a physical receive or send port.
2) Per Instance Pipeline Configuration using the BizTalk 2006 Administration Console. This allows changing the properties of receive and send pipeline components that
are set in the various stages of receive and send pipelines. The receive and send pipelines are configured to be invoked in physical receive and send ports. For example, as below the MIME/SMIME decoder pipeline component configuration of a receive pipeline can be altered on a per instance basis using the BizTalk 2006 Administration tool.

Both 1) and 2) are extremely useful features new for BizTalk 2006.

An additional feature that would be useful (in some cases) would be to alter a pipelines component property values in an orchestration.

As below the following properties may be set for a message in an orchestration.

Property Type of Port

BTS.SendPipelineConfig Send Pipeline Configuration for a Two Way or One Way Send Port
BTS.SendPipelineResponseConfig Receive Pipeline Configuration for a Two Way Send Port

BTS.ReceivePipelineConfig Receive Pipeline For a Two or One Way Receive Port
BTS.ReceivePipelineResponseConfig Send Pipeline for a Two way Receive Port

For the below receive pipeline configured with a MIME/SMIME decoder:

The below XML is used internally by BizTalk to store the above configuration:

The above XML configuration can then be modified using a statement as below in a message assignment shape in an orchestration:

When the above altered message is sent through a physical port that is set to use a receive pipeline configured with the MIME/SMIME component (in the decode stage):
The new configuration for the MIME/SMIME component (programmatically set in the orchestration) will then temporarily override the hardcoded configuration or the per-instance configuration pipelines configuration.

Ideally this is useful to cut down on the number of pipelines or physical send or receive ports that have to be created.

In order to use this method you can do the following:

1) Create receive or send pipelines set with the appropriate pipeline components. Deploy the pipelines.
Note: You do not have to configure the pipeline components properties.
Note: You can also use the out of the box XML Receive or Send pipelines.

2) Create physical ports that are set to use the pipelines from 1).

3) Create and deploy orchestrations that temporarily override the hardcoded or per-instance send or receive ports pipeline configuration as was illustrated in an above graphic.
Logical ports in the orchestration are needed to send or receive the messages from an orchestration. The logical ports are then bound to physical ports. The configuration set in the orchestration will then temporarily override the configuration of the physical ports.

Note: To get a sample of the configuration for a pipeline. Do the below:
Using a tool such as Microsoft Sql Server Management Studio, query/open and copy the following values for a port created in 2), from a row in the following table.column in the BizTalkMgmtDB database:

bts_sendport.nvcSendPipelineData
bts_sendport.nvcReceivePipelineData
adm_receivelocation.ReceivePipelineData
adm_receivelocation.SendPipelineData

Note: If the column/row for a port is null, using the BizTalk Console, edit the receive or send ports pipeline configuration so that it overrides the default configuration. The configuration for the pipeline component(s) should then be set in one of the above table.column combinations. You can then revert back to the default configuration, by choosing the passthrough pipeline and then again choosing the original pipeline.

Note: Do not modify the contents of the rows/columns directly in the BizTalkMgmtDB database.
Note. The BizTalk Object Model could also be used to retrieve the configurations.
Note: The configuration for the pipeline can be stored and then retrieved from a configuration store (i.e. config file, SSO, database etc) and then set in the orchestration.

4) Bind the logical ports in the deployed orchestrations from 3) to physical ports from 2).

Good Things
1) This is a technique to cut down on the number of pipeline components and/or physical ports.
For example if the same XML received message was to be delivered to the same file directory i.e. (C:\flatfiles) as a delimited and positional flat file,
the following send ports, pipelines and orchestrations would be required:

Executing Send Pipelines in an Orchestration
i) Send Pipeline with FlatFile Assembler component for delimited flat file.
ii) Send Pipeline with FlatFile Assembler component for positional flat file.
iii) Send Port configured with Passthrough pipeline.
iv) Orchestration

Per Instance Pipeline Configuration
i) Send Pipeline with FlatFile Assembler.
ii) Send Port with per instance configuration of above pipeline to create delimited flat file.
iii) Send Port with per instance configuration of above pipeline to create positional flat file.
iv) Optionally an orchestration.

Dynamically Configuring Pipeline Component Properties in a BizTalk Orchestration
i) Send Pipeline with FlatFile Assembler.
ii) Send Port configured to use above pipeline.
iii) Orchestration

Not So Good Things
1) To dynamically configure and execute a receive pipeline, a two way send port will have to be used. This might introduce the need for a loopback adapter. A loopback adapter also introduces messagebox hops that will degrade performance.
2) Maintaining a store of the Pipeline configuration XML. The configuration store replaces individual pipeline components or per instance configuration of pipelines in a physical send or receive port.

There are two examples included in the download :
1) Orchestration sets receive pipeline configuration and uses a loopback adapter that executes altered configuration.
2) Orchestration sets send pipeline configuration and uses send file adapter to invoke altered configuration.
Note: A loopback adapter is also included in the download. Please read the ReadMe before installing

Unfortunately
I wish you could do something like the below:

OutputMessage(BTS.ReceivePipelineConfig) = "Pipeline Config XML would go here";
OutputMessage = Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteReceivePipeline(typeof(PipelinesAndSchemas.ReceivePipeline), InputMessage);

It did not produce a runtime error, but the configuration for the Receive Pipeline was not overridden.

Last of All
I have yet to use this technique in a production environment. I have not tried this technique with pipelines configured to use multiple or custom components, but it should work with these scenarios. Please consider all the pros and cons of using this technique. Again, this could be an alternative to replace an unmanageable number of receive/send ports and pipelines.

Extending BizTalk Mapping/Transformation operations into Sql Server

2006-07-19T19:52:00Z

A staple in BizTalk 2004/2006 is to design and implement mapping/transformation operations using the BizTalk mapper. Mapping in BizTalk can be extended and improved by:

a) Using a rich set of Functoids that ship with BizTalk.
b) Using custom XSLT in a map.
c) Writing Custom Functoids that can be used in a map (link)
d) Writing JScript,C# or VB.Net code in a Scripting Functoid.

As mapping operations become more complex, Custom XSLT must often be used to perform the complete transformation or a portion of the transformation.

Unfortunately many developers do not know XSLT and in some cases do not have time to learn it. But, most developers do have Sql skills and are comfortable writing Sql Statements that join tables to produce a final result set.

This post will discuss a transformation of a source XML message to a destination XML message using a Sql Server 2005 stored procedure. The code for this example can be downloaded at the end of this entry.

A portion of the source XML message for the transformation is as below.
One or many Applicant nodes are submitted with the message.

A brief description of the <Applicant> node for the above message is as below:
1) <Applicant> nodes contain information such as <ApplicantId>,<FirstName>,<LastName>,<Sex>,<BirthCountryCode> etc.
2) <Applicant> nodes contain 0 to many <NickName> Nodes.
3) <Applicant> nodes contain <TestScore> nodes, that include the results of a test taken.
4) <Applicant> nodes contain <RelationInfoToScores> Nodes that contain IDRefs to relate the ApplicantId to the correct TestScores in the message. The red arrows in the above graphic illustrate the relation.

The Destination XML message for the completed transformation is as below. A separate <ConcatenatedApplicantInfo> node is created for each <Applicant> node in the source message. Information for each Applicant is concatenated with a padding of 25 spaces between items.

The below Sql Server 2005 stored procedure will complete the transformation of the source message to the destination message.

USE [TransformationHelper]

/****** Object: StoredProcedure [dbo].[Map_ApplicantHierarchical_To_ApplicantFlat] Script Date: 07/13/2006 17:25:26 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE Proc [dbo].[Map_ApplicantHierarchical_To_ApplicantFlat]

-- Parameter to Accept AllApplicantInfo XML message

-- from BizTalk.

@ApplicantXML xml

@Applicant Temp table for Applicant Header Info

from AllApplicantInfo info XML message.

DECLARE @Applicant Table(

ApplicantId int null,

FirstName varchar(20) null,

LastName varchar(20) null,

MiddleName varchar(20) null,

Sex char(1) null,

BirthCountryCode int null,

BirthProvinceCode int null,

BirthCountry varchar(20) null,

BirthProvince varchar(20) null);

Statement to shred Applicant Header Info from XML message

into @Applicant Temp table

WITH XMLNAMESPACES ('http://Applicants' as ns0)

Insert Into @Applicant

(ApplicantId,

FirstName,

LastName,

MiddleName,

Sex,

BirthCountryCode,

BirthProvinceCode)

Select ShreddedApplicantInfoXML.nCol.value('@ApplicantId','int'),

ShreddedApplicantInfoXML.nCol.value(('(FirstName)[1]'),'varchar(20)'),

ShreddedApplicantInfoXML.nCol.value(('(LastName)[1]'),'varchar(20)'),

ShreddedApplicantInfoXML.nCol.value(('(MiddleName)[1]'),'varchar(20)'),

ShreddedApplicantInfoXML.nCol.value(('(Sex)[1]'),'char(1)'),

ShreddedApplicantInfoXML.nCol.value(('(BirthCountryCode)[1]'),'int'),

ShreddedApplicantInfoXML.nCol.value(('(BirthProvinceCode)[1]'),'int')

From @ApplicantXML.nodes ('/ns0:AllApplicants/Applicant/Information') as ShreddedApplicantInfoXML(nCol)

Where ShreddedApplicantInfoXML.nCol.value(('(FirstName)[1]'),'varchar(20)') is not null

/* In below update statement Join to another table to get Country Name:

Join CountryCodes

On Applicant.BirthCountryCode = CountryCodes.Id

Replaces Database functoids.

This increases performance as it cuts out the

Database functoids that would make multiple round trips to

the database

/* Also in below update statement Call Managed Code to get Province name

BirthProvince = dbo.GetProvince(Applicant.BirthCountryCode,Applicant.BirthProvinceCode)

This is like a scripting functoid or custom functoid in a BizTalk map.

Update @Applicant

Set BirthCountry = CountryCodes.Name,

BirthProvince = dbo.GetProvince(Applicant.BirthCountryCode,Applicant.BirthProvinceCode)

From @Applicant as Applicant

Join CountryCodes

On Applicant.BirthCountryCode = CountryCodes.Id

--Below is good for debugging purposes.

--Select * from @Applicant

@NickNames Temp table for Info

from AllApplicantInfo info XML message.

DECLARE @NickNames Table

(ApplicantId int null,

[Name] varchar(20) null) ;

Statement to shred Info from XML message

into @NickNames Temp table

WITH XMLNAMESPACES ('http://Applicants' as ns0)

Insert Into @NickNames

Select NickNameXML.nCol.value('(../@ApplicantId)[1]','int'),

NickNameXML.nCol.value(('(Name)[1]'),'varchar(20)')

FROM @ApplicantXML.nodes ('/ns0:AllApplicants/Applicant/Information/NickName') as NickNameXML(nCol)

--Below is good for debugging purposes.

--Select * from @NickNames

@TestScores Temp table for Info

from AllApplicantInfo info XML message.

Declare @TestScores Table

(TestScoreId char(2) null,

Test varchar(20) null,

Score int null);

Statement to shred Info from XML message

into @TestScores Temp table

WITH XMLNAMESPACES ('http://Applicants' as ns0)

Insert Into @TestScores

Select

TestScoresXML.nCol.value('(@TestScoreId)','char(2)'),

TestScoresXML.nCol.value(('(Test)[1]'),'varchar(20)'),

TestScoresXML.nCol.value(('(Score)[1]'),'int')

FROM @ApplicantXML.nodes ('/ns0:AllApplicants/Applicant/TestScores') as TestScoresXML(nCol)

--Below is good for debugging purposes.

--Select * from @TestScores

@Relation Temp table for Info

from AllApplicantInfo info XML message.

Declare @Relation Table

(ApplicantId int null,

TestScoreId char(2) null);

Statement to shred Info from XML message

into @TestScores Temp table

WITH XMLNAMESPACES ('http://Applicants' as ns0)

Insert Into @Relation

Select

RelationXML.nCol.value('(@ApplicantId)','int'),

RelationXML.nCol.value(('@TestScoreId'),'char(2)')

FROM @ApplicantXML.nodes ('/ns0:AllApplicants/Applicant/RelateInfoToScores') as RelationXML(nCol)

--Below is good for debugging purposes.

--Select * from @Relation

Declare @FinalResults Table

(ApplicantID int not null,

ConcatenatedInfo varchar(8000))

Declare @ApplicantId int

Declare @ApplicantInfoText varchar(8000)

Declare @NickNamesText varchar(8000)

Declare @ScoresText varchar(8000)

-- Below cursor will do the work of concatenating each Applicants

-- info together into one node.

Declare cur_ApplicantRows Cursor Local Fast_Forward For

SELECT ApplicantId

FROM @Applicant

OPEN cur_ApplicantRows

FETCH NEXT FROM cur_ApplicantRows INTO @ApplicantId

While @@Fetch_status = 0

Begin

Set @ApplicantInfoText = ''

Set @NickNamesText = ''

Set @ScoresText = ''

Select @ApplicantInfoText = FirstName + space(25 - len(FirstName)) +

LastName + space(25 - len(LastName)) +

MiddleName + space(25 - len(MiddleName)) +

Sex + space(5 - len(Sex)) +

BirthCountry + space(25 - len(BirthCountry)) +

BirthProvince + space(25 - len(BirthProvince))

From @Applicant

Where ApplicantId = @ApplicantId

Select @NickNamesText = @NickNamesText + [Name] + space(25 - len([Name]))

From @NickNames

Where ApplicantId = @ApplicantId

Select @ScoresText = @ScoresText +

TestScores.Test + space(25 - len(TestScores.Test)) +

rtrim(str(TestScores.Score)) + space(25 - len(rtrim(str(TestScores.Score))))

From @TestScores as TestScores

Join @Relation as Relation

On TestScores.TestScoreId = Relation.TestScoreId

and Relation.ApplicantId = @ApplicantId

--Select @NickNamesText

--Select @ApplicantInfoText

--Select @ScoresText

Insert Into @FinalResults

values (@ApplicantId, @ApplicantInfoText + ' ' + @NickNamesText + ' ' + @ScoresText)

FETCH NEXT FROM cur_ApplicantRows INTO @ApplicantId

End

-- Below statement will generate the Final Flat Applicant XML Result.

-- Return back the final result set

Select 1 as Tag,

0 as Parent,

ApplicantID as [ConcatenatedApplicantInfo!1!ApplicantID],

ConcatenatedInfo as [ConcatenatedApplicantInfo!1!!cdata]

From @FinalResults as ConcatenatedApplicantInfo

FOR XML EXPLICIT

Discussion of Stored Procedure:

1) The AllApplicantInfo source XML Message is passed to the stored procedure via a parameter of type XML.

2) The AllApplicantInfo source XML Message is then shredded into an number of temporary relational tables:

@Applicant
@NickNames
@TestScores
@Relation

3) Sql Statements in the stored procedure are used to join the temp tables to produce the final <ConcatenatedApplicantInfo> node for each <Applicant> node in the source message.

4) The final destination message is returned using a Select statement via an XML Explicit Clause.

5) The Country Code table is directly available and can be joined to in a set operation in the stored procedure. This eliminates using Database Functoids in a BizTalk Map that would invoke multiple round trips to Sql Server.

6) A new feature of Sql Server 2005 is calling managed code. Managed code in Sql Server 2005 allows complex data manipulation that would be difficult to write using straight Transact SQL. In this example, calling managed code from stored procedure replaces the use of functoids in a BizTalk map. The below managed code was called from the stored procedure to determine the Province from a Province code.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString GetProvince (SqlInt32 country, SqlInt32 province)

{

string result = "";

switch (country.Value)

{

case 1:

switch (province.Value)

{

case 1:

result = "Newfoundland";

break;

case 2:

result = "PEI";

break;

case 3:

result = "New Brunswick";

break;

case 4:

result = "Nova Scotia";

break;

case 5:

result = "Quebec";

break;

case 6:

result = "Ontario";

break;

case 7:

result = "Manitoba";

break;

case 8:

result = "Saskatchewan";

break;

case 9:

result = "Alberta";

break;

case 10:

result = "British Columbia";

break;

case 11:

result = "Yukon";

break;

case 12:

result = "NWT";

break;

default:

result = "Unknown";

break;

}

break;

default:

result = "Unknown";

break;

}

return result;

}

};

As below, BizTalk can invoke the stored procedure via the Sql Adapter passing the source message as a parameter and then receiving the destination message as the response.

Sql 2005 has added many enhancements to aid in XML processing.
a) XQuery expressions
An example is below:

for $i in /InvoiceList/Invoice/Items/Item
return
{$i/@Quantity}
{string($i/@Product)}

b) Xml Auto -> Nesting XML auto clauses
c) XML datatype
d) Indexing xml data
e) A subset of the FLWOR syntax
f) Validating XML against a Schema.
g) more..

If you are interested in some free online virtual labs that explore the new Sql 2005 XML capabilities, please click on the below links:

SQL Server™ 2005: XML Capabilities (TechNet) Virtual Lab
And:
Using Xquery with SQL Server 2005 XML Data (TechNet) Virtual Lab

Finally:

The not so good things about this method:

1) You have to call Sql server from BizTalk. When using BizTalk maps, the entire
transformation is localized to the BizTalk host process.

The good things about this method:

1) Simplified debugging of the transformation. For example, in the T-SQL you can place in print statements, select statements that return intermediate result sets, debug a stored procedure in Visual Studio, use the Sql Profiler etc.
2) In this example only a stored procedure needs to be deployed to a Sql Server database.
3) In this example, no data is persisted to permanent Sql Server tables. Only temp tables are utilized in the stored procedure.

Use this method:

1) As an alternative to writing custom XSLT.
2) If you are comfortable writing Sql Server Stored Procedures.
3) To replace database functoids in a map that incur multiple round trips to a database.
4) To perform transformations on larger messages

Some notes:

1) More than one XML message can be passed to a stored procedure.
2) Transformation operations can be split between the BizTalk maps and stored procedures. For example BizTalk maps can carry out a portion of the transformation and then pass the remainder of the transformation to the stored procedures.
3) For Sql 2000, OpenXML statements can be used to shred XML nodes into relational tables.

Conclusion:

Use the new XML enhancements along with the managed code in Sql 2005 to simplify and to improve the performance of complex BizTalk mapping operations.

Download the sample discussed above HERE. Read the ReadMe.Txt before installing and running.

More Adapters from Jesús and Webcasts.

2006-04-19T12:26:00Z

Jesús has been very busy building adapters. They include:

SalesForce.com adapter for BizTalk Server 2006
Web Services Enhancements (WSE) 3.0 adapter for BizTalk Server 2006
SQL Server Service Broker adapter for BizTalk Server 2006 v1.5

A Webcast on the SalesForce Adapter will be given tomorrow (April 20), and a Webcast on the WSE 3.0 adapter will be given on May 5. You can read more about the adapters and Webcasts HERE

BizTalk 2006 RTM ready for Download

2006-03-27T23:29:00Z

The RTM version of BizTalk 2006 is now available for download on MSDN.
Read more about the RTM version Here and Here.

Another BizTalk Presentation

2006-03-27T14:39:00Z

I always forget to post these in advance.

I am doing another BizTalk Presentation at Btug, tomorrow night (March 28) in Toronto.

It is split into two parts:
i)Handling Large Messages in BizTalk
ii)Extending BizTalk Transformations into Sql Server

Also ObjectSharp will be giving away a free seat for a 5 day course -> Building BizTalk 2006 Solutions Best Practices with the first delivery of the course slated for May 1 2006. You can read more about the presentation Here

Presentation -> Using BizTalk 2006

2006-02-09T02:51:00Z

Short notice, but I am doing a presentation tomorrow (Thurs. Feb 9) at the Metro Toronto .Net Users Group.
You can read more about it here:
http://www.metrotorontoug.com/User+Group+Events/231.aspx

Demos will include dealing with binary documents (Word and Excel) in BizTalk 2006.
The demos will also include:

- The new Sharepoint and POP3 adapters for BizTalk 2006.
- Parsing Excel documents to XML in BizTalk.
- Integration of Sql Server 2005 Service Broker with BizTalk 2006.
- Some of the new and improved features of BizTalk 2006.

So if you are in the area, feel free to drop by.

BizTalk 2006 -> Example using the new POP3 and Sharepoint Adapters

2006-02-03T17:45:00Z

Below is an example using the new POP3 and Sharepoint Adapters (Sharepoint Adapter now out of the box) for BizTalk 2006. This example demonstrates the following:

1) Processing binary documents in BizTalk.
2) A method to process multiple attachments in an incoming mail message.
3) Improvements to the Sharepoint Adapter.

You can download the code at the end of this blog.

The example goes something like this.

A candidate is applying for a job. The candidates resume (Word) and an informational spreadsheet (Excel) are attached to an email message as below:

The resume is in word format, and the excel spreadsheet is as below:

The mail message with attachments is read by BizTalk server. BizTalk server will then add a new item to a Sharepoint Document library. The Word document from the mail message is added as the item's document and the Excel spreadsheet is parsed to populate the First Name and Last Name columns as below:

The sample works as below:

1) A BizTalk Receive Port/Receive Location is configured using the POP3 receive Adapter as below:

Note: The Apply MIME Decoding property is set to false. This is because we want the raw MIME message to be delivered to an orchestration. The two attachments of the message will be dealt with in the orchestration (see below).

2) An orchestration as below will then subscribe to the incoming encoded MIME message:

3) In the orchestration, a receive pipeline (ConstructCandidateInfo Construct Shape) is executed to extract the Excel message out of the encoded MIME message and to also parse the Excel message to an XML format.

The pipeline looks as below:

Note: A MIME decoder is used in the pipeline to extract out the Excel Attachment
Note: The ODBC File decoder is used in the pipeline to parse the Excel Attachment to an XML message. This is so the FirstName and LastName can be used to populate the First Name and Last Name columns in the Sharepoint document library list.
Note: Executing receive pipelines in an orchestration is a new feature for BizTalk 2006.

The code to execute the pipeline in the orchestration is as below:

// Execute the Pipeline -> ReceivePipelineCandidateInfo.
// This will create a message with the XML in it
varPipelineOutPutMessages =
Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteReceivePipeline(typeof(BizTalkPop3AndSharepointExample.ReceivePipelineCandidateInfo),
msgMimeCandidate);
msgCandidateInfo = null;
varPipelineOutPutMessages.MoveNext();
varPipelineOutPutMessages.GetCurrent(msgCandidateInfo);

4) In the orchestration, a receive pipeline (ConstructResume Construct Shape) is executed to extract the Word document.

The pipeline looks as below:

Note: A MIME decoder is used in the pipeline to extract out the Word Attachment

The code to execute the pipeline in the orchestration is as below:

// Execute the Pipeline -> ReceivePipelineCandidateResume.
// This will create a message with the Word Document in it
varPipelineOutPutMessages =
Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteReceivePipeline(typeof(BizTalkPop3AndSharepointExample.ReceivePipelineCandidateResume),
msgMimeCandidate);
msgCandidateResume = null;
varPipelineOutPutMessages.MoveNext();
varPipelineOutPutMessages.GetCurrent(msgCandidateResume);

// Get the FirstName and LastName that are in the Parsed XML Excel message.
// set these into the WSS ConfigPropertiesXml
varXmlDomCandidateInfo = msgCandidateInfo;
varXmlNode = varXmlDomCandidateInfo.SelectSingleNode("//FirstName");
strFirstName = varXmlNode.InnerText;
varXmlNode = varXmlDomCandidateInfo.SelectSingleNode("//LastName");
strLastName = varXmlNode.InnerText;
// Set the first names and last names so they will
// appear in the Document Library Columns
strWSSConfigPropertiesXml = "<ConfigPropertiesXml><PropertyName1>First Name</PropertyName1><PropertySource1>" + strFirstName +
"</PropertySource1><PropertyName2>Last Name</PropertyName2><PropertySource2>" + strLastName + "</PropertySource2></ConfigPropertiesXml>";
msgCandidateResume(WSS.ConfigPropertiesXml) = strWSSConfigPropertiesXml;
// Set the name of the File, when created in the Sharepoint Document Library
msgCandidateResume(WSS.Filename) = strFirstName + " " + strLastName + ".doc";

Note: msgCandidateResume(WSS.ConfigPropertiesXml) = strWSSConfigPropertiesXml;
is used to set the First Name and Last Name columns in the document library.

5) The msgCandidateResume message is then sent to the WSS library via a configured
Send Port using the Sharepoint Adapter as below:

Note: That the Column 01, Column 01 Value, Column 02, Column 02 Value, FileName are not configured.
These are dynamically set by the orchestration as explained in 4).

You can download the code HERE. Also look at the ReadMe

Conclusion

1) The new adapters and adapter enhancements for BizTalk 2006 really open up the doors for integrating all sorts of different applications. For a complete list of adapters that will be shipped with BizTalk 2006, please go HERE
2) You can use the new features of BizTalk 2006 (like executing Receive Pipelines in an Orchestration), to easily process complex messages in BizTalk.
3) Adrian Hamza and his team have developed a top notch, feature rich Sharepoint adapter for BizTalk 2006. For more information on the Sharepoint adapter, please visit Adrian's blog HERE. Adrian also has recorded a series of WebCasts on the Sharepoint Adapter:

WSS Adapter Training Videos:

WSSAdapter-PropsInOrchestration.wmv (28.58 MB)
WSSAdapter-SendReceiveCBR.wmv (10.25 MB)
WSSAdapter-SetupAndConfig-Short.wmv (9.58 MB)
WSSAdapter-InfoPathIntegration.wmv (15.11 MB)

You can download these at BetaPlace (BizTalk Server 2006 section)
http://beta.microsoft.com.

To get access to the BizTalk Server 2006 area at BetaPlace, please goto :
http://www.microsoft.com/biztalk/evaluation/bts2006beta.mspx

BizTalk 2006 and the Sql Server 2005 Service Broker Adapter (Beta 2)

2006-01-05T01:33:00Z

Jesús Rodríguez has finished a Beta 2 version of Service Broker Enhancements Adapter for BizTalk Server 2006. They are looking for beta testers, so please go HERE if interested. Service Broker is an exciting new feature for Microsoft SQL Server 2005. Now you can integrate BizTalk Messaging with Service Broker Messaging, using this important new adapter.

BizTalk, Another Custom XSLT mapping sample.

2005-12-16T02:30:00Z

The below mapping example could most likely be accomplished using a series of BizTalk maps. In the maps, Table Looping / Table Extractor / Looping functoids etc. could be used to perform the transformation. Sometimes it makes sense to craft your own custom XSLT to execute the complete transformation. The sample input / output messages for the transformation as below. Also included is the custom XSLT for the transformation. You can also download the source code (at end of this blog entry).

INPUT Message:

--> For each "contenedor" node, in the output message must repeat the sequence of segments ORC + RQD (According to the standard HL7 v.2.3.1)
--> For each "prueba" node, in the output message must repeat the sequence of segments ORC + OBR
--> For each "parametro" node, in the output message must repeat the segment NTE

For the below output, the order of the nodes is also important. The order relates back to the input message.

OUTPUT Message should be:

MSH||||.|
ORC||||.|
RQD||||.|
ORC||||.|
RQD||||.|
ORC||||.|
OBR||||.|
NTE||||.|
NTE||||.|
ORC||||.|
OBR||||.|
NTE||||.|
NTE||||.|
NTE||||.|

The custom XSLT for the mapping/transformation looks like below:

The above XSLT can be placed in a scripting functoid in the map as discussed HERE
Or the custom XSLT can be placed in a separate file, with the map's property -> Custom XSLT Path pointing to the file (see below)

Download the solution HERE

Conclusion: In some cases it is much simpler to write your own custom XSLT for a BizTalk map.
Note: You can also perform a transformation in a BizTalk map using a combination of custom XSLT and links/functoids (see below links)
Note: Using functoids or a combination of functoids in a map are sometimes not enough to perform a transformation or a portion of a transformation. Using custom XSLT for the transformation or a portion of the transformation may be the answer.

Below is more information on using custom XSLT in BizTalk maps:

Extending Mapper (BizTalk Server SDK Samples)

Creating Nodes from Flat Data in BizTalk 2004 Maps

Logical Message Splitting in BizTalk Server 2004

Implementation of Message Transformation Normaliser Pattern in the BizTalk 2004

Sorting XML Nodes with the BizTalk Mapper

Debugging XSLT

New BizTalk 2006 5 day Course -> Building BizTalk 2006 Solutions Best Practices

2005-11-30T17:35:00Z

I am in the process of writing a new BizTalk 2006 course as below:

Preamble

BizTalk is all about messaging and how messages are processed and routed between a companies internal applications (for example: Sap/Mainframe/Peoplesoft/ Database/JD Edwards/Web Services etc.) and how messages are exchanged with trading partners (B2B). BizTalk provides an excellent framework for implementing many common messaging patterns such as:
a) Scatter/Gatherer pattern
b) Splitter pattern
c) Aggregator pattern
d) Message Broker pattern
e) etc.
The objective of this course is to provide students with all the necessary tools to implement messaging patterns using BizTalk 2006.

Below are some of the topics with labs for the course:

a) Various methods to Create Schemas for XML and Flat files messages.
b) Mapping using the BizTalk Mapper, custom XSLT and other alternatives
will be discussed to perform complex transformations.
c) An introduction into building Custom Adapters
d) An introduction into building Custom Pipeline Components
e) Interacting with the BizTalk Messaging Engine
f) Creating and using Custom Pipelines
g) Orchestrations.
h) Using xpath in Orchestrations.
i) Web Services and BizTalk
j) Error Handling with BizTalk
k) Debugging BizTalk Solutions
l) Deploying BizTalk Solutions
m) Business Activity Monitoring and BizTalk
n) Business Rules Engine
o) Implementing Common Enterprise Integration Patterns with BizTalk.
Some of the patterns discussed and put into practice are:
Message Broker
Content Based Routing
Splitter Pattern
Normalizer Pattern
Aggregator Pattern

Additionally the new features of BizTalk 2006 will be highlighted and used in Labs.

It is quite a bit for a five day course (not sure if all of it will make it in). From my experience using the product and teaching a BTS 2004 course for the past year and a half, the above is what I would like to cover in the course.

If you have a suggestion for additional topics for the course, please reply to this entry. I would love to hear them.

Update: This course is available now. Building BizTalk Server 2006 Solutions Best Practices

BizTalk, MIB, Acord and 5 minutes of Marketing

2005-11-28T17:21:00Z

My blog entries are usually technical in nature. This entry is more of a marketing entry discussing how BizTalk 2004/2006 can be used to implement the following specific business scenario:

1) An insurance underwriter requires information (for example -> medical history) about an insurance applicant.

2) An internal application captures applicant information provided by the underwriter such as:
a) Applicants First Name/Last Name/ Middle Name.
b) Birth date.
c) Birth place.

3) The internal application then sends this information to a company called MIB. The underwriters query parameters (Last Name/ First Name etc) are then used to query a data repository at MIB. A result set of the query is then returned to the internal application from MIB. This result set contains information such as the medical history of the applicant.

4) The results from MIB are then displayed to the insurance underwriter to aid in the insurance application process.

Presently many North American insurance companies send/receive information to/from MIB over a dial up connection. Flat Files are sent and received back from MIB.

By December of 2006, all users of the current dial up process are to switch over to a new process provided by MIB as below:

All insurance underwriter requests are to be transmitted to MIB using a secure HTTP connection. The requests are to be sent in XML messages that conform to the Acord schemas. A synchronous HTTP response (XML) message will then be sent back over the same secure HTTP channel as the request. This response message contains the results of the query.

Below is a description of how the above new process can be implemented by BizTalk 2004 or BizTalk 2006.

1) Create a new BizTalk project that contains the Acord XSD schemas as below:

2) Typically a mainframe application may dump the underwriter requests to disk in a flat file or different format.
A configured BizTalk FTP or File Receive Port is then configured to pick up the files.

Note: The original request may be delivered by a different transport or stored to intermediate location as below:

HTTP
MSMQ
MQSeries
Sql Server
Oracle
DB2
Host Applications -> IBM, mainframe zSeries (CICS and IMS) and/or midrange iSeries (RPG)
Host Files -> Data adapter for host file systems on IBM mainframe zSeries VSAM datasets and midrange iSeries physical files
etc.

BizTalk contains many out of the box adapters that can be used to receive messages from all the above transports/locations and others not listed. If the needed adapter is not provided out of the box, then the BizTalk Adapter framework can be used to author your own custom adapter. Third party adapters can also be purchased.

3) Create internal XSD schemas to represent the internal messages.
For example, an XSD schema using flat file extensions would be created to represent flat files produced by the mainframe application.

4) Create BizTalk map(s) to transform the internal flat files to Acord XML messages.

5) Create the necessary BizTalk orchestrations for the business process.

6) Install a certificate on the BizTalk machine for the MIB secure HTTP requests using the Certificate Snap-in.

7) Create a BizTalk Solicit-Response Send port with a configured HTTP adapter for the MIB secure HTTP requests. The Send port is configured to use the installed certificate from step 6).

8) Create BizTalk map(s) to transform the Acord XML response messages to internal messages.

The above pretty well covers all the required BizTalk objects.

So why use BizTalk for the above MIB request/response loop or a similar business process:

1) This is what BizTalk server does. It integrates a companies internal applications together and also provides B2B capabilities.

2) A number of turnkey solutions can be purchased that do the same communication loop with MIB. These turnkey solutions are probably more expensive and proprietary than a BizTalk Solution. There is little coding involved to build the above BizTalk solution and there is complete control over the process as a developer(s) is building it and maintaining it. For changes or modifications to the BizTalk process, a configuration change is sometimes all it takes.

For BizTalk 2006 pricing go to HERE
For BizTalk 2004 pricing got to HERE

I have heard claims that it took more than 1000 man hours to complete the above process in BizTalk. This is completely untrue as it took me a fraction of 1000 hours to implement the above MIB communication loop using BizTalk.

3) The BizTalk server that hosts the above process can be used to host other developed Application Integration or
B2B business processes.

4) BizTalk server is increasing exponentially in popularity. More and more developers have BizTalk experience.

5) BizTalk provides many useful out of the box features such as:

a) Business Activity Monitoring
b) Rules Engine
c) Fault Tolerance/Load Balancing
d) Tracking
e) etc. etc.

Note: I do not mean to sugar coat the process. The BizTalk maps for the MIB requests and response messages are somewhat complex. The complexity of the maps can be attributed directly to the large size of the Acord XSD schemas. There are larger XSD schemas. In my opinion, the HL7 XSD schemas are more complex then the Acord XSD schemas.

Processing a Large Flat File Message with BizTalk and the SqlBulkInsert Adapter

2005-10-23T21:14:00Z

Below discusses the second demo for a presentation that I did at the Business Process Integration & Workflow Conference in Redmond during the week of Oct 4.

The title of the presentation was entitled:
Handling Large Messages in BizTalk

The first demo that I did for the presentation can be found here:
Processing a large message and a faster, less CPU intensive splitter pattern.

This entry compares two different methods for processing large flat files in BizTalk.
The size of the flat files being tested ranged in size from: 8MB to 201MB.

At the end of this entry is the download for the SqlBulkInsert Adapter code and the BizTalk Test Project to
perform the tests.

A section of the flat file message being processed is as below. About half of the columns are cut off:

The above is the flat file representation of the XML Production orders message that were used in the first Demo.
Each line of the flat file message contains a production order. The production order data (columns) are separated by commas and each production order line (record) is separated by a Carriage Return/Line Feed.

To process the flat file message, two different implementations were used. In both implementations, the flat file is pre-processed so that it is in a format for further processing -> For example mapping or splitting.

Implementation One: Processing with a Flat File Schema and Custom Receive Pipeline

This implementation uses the standard method of using a XSD Schema with flat file extensions to represent the above flat file message. A custom receive pipeline utilizing a flat file disassembler transforms the flat file into an internal XML message. This XML message will be published into the MessageBox database. If you have not processed a flat file in BizTalk before, try this example in the SDK.

This is just a short recap of how this implementation works and is really for the benefit for those not familiar with BizTalk or who have never processed a flat file in BizTalk.

a) Flat File is picked up by a Receive Location.
b) Receive Location is configured to use a Custom Receive Pipeline
c) Custom Receive Pipeline will convert (disassemble) the flat file, into an internal XML message as below:

d) This XML Message is then published into the MessageBox database
e) Once the XML Message is published into the MessageBox database, any number of Orchestrations or send Ports can subscribe to this message and process this message. This message could then be mapped into another message or split into separate messages as in the first demo.

Implementation Two: Processing with the SqlBulkInsert Adapter

From a performance perspective there are three areas where the above implementation can be improved. This is especially true, if the incoming flat files messages are large greater than 20 MB.
Areas of improvement are listed below:

1) The pipeline processing time.
2) CPU utilization is at 100% for pipeline processing.
3) Flat files when converted into XML files can double or triple in size. For example when a 201MB flat file production order message is converted into an internal XML message, the size of this internal XML message expands to 767MB. This large 767MB message is then published into the MessageBox database. The size of the XML message is greater because of added tags, elements and attributes.

Therefore one approach would be to skip the pipeline processing for the large flat file message. Pipeline processing is a necessary and great feature in BizTalk, but will be skipped in this implementation to increase performance.

A Custom Adapter (SqlBulkInsert Adapter) will be used to pre-process the flat file.
This adapter can only be used with a One-Way Receive Port. The property pages of the Receive Location are as below:

How this implementation works:

a) This adapter behaves similar to the out of the box File Adapter used in a Receive Location. It will poll a directory on a Hard Drive for files to be picked up and processed.

b) The FileReceiveFolder Property is set to a directory on a hard drive.
For example -> C:\BTSBulkLoad\Files\ReceiveForSqlBulkLoad
Flat Files dropped into this folder will be picked up to be processed.

c) The FileMask Property is set so the adapter will only pick up only files with the mask.
For example -> *.txt

d) The FileErrorFolder Property will be populated with error files.
For example -> C:\BTSBulkLoad\Files\FileErrorFolder.
If some of the rows(data) in the flat file message are malformed, these will not be processed , but will be placed in a file for viewing. This is analogous to a recoverable interchange. Rows in the flat file that are correct in structure will be processed.

For example, if two of the rows in the flat file cannot be processed (Missing Columns, missing delimiters etc) a file is produced with the row(s) that could not be processed.

11506,10020,TST GF 01 GRD 01,7045936818561,20.25,20.25,58.25,58.250359010995972,3 Inch Core,,,
11665,10020,TST GF 01 GRD 01,36,23584,23584,1432.7045936818561,1432.7045936818561,20.25,20.25,5

Additionally another file is produced with a more detailed explanation of why the row(s) could not be processed as below:

Row 16 File Offset 3822 ErrorFile Offset 0 - HRESULT 0x80004005
Row 25 File Offset 6422 ErrorFile Offset 493 - HRESULT 0x80004005

The Max Number of rows with errors can be configured (explained below). If this number is exceeded, the operation will fail as a whole.

Once a File has been picked up, the SqlBulkInsert adapter will use the below properties to delegate the processing of the flat file to a Sql server stored procedure. The large File will not be submitted to the BizTalk engine to be processed. Therefore the BizTalk pipeline processing will be skipped for the large file.

e) The SqlConnectionString Property is set with the connection string to the sql database that will process the flat file.
For example -> packet size=4096;integrated security=SSPI;data source="(local)";persist security info=False;initial catalog=BTSSqlBulkLoad

f) The SqlStoredProcedureName Property is set to the name of the stored procedure that will process the flat file.
For example -> LoadInsertProductionOrders

The declaration of the stored procedure is as below:

CREATE Procedure [dbo].[LoadInsertProductionOrders]

@pathAndFileName varchar(500),
@pathAndErrorFileName varchar(500)

g) The SqlStoredProcFileNameParameter property will set with the name of the parameter that accepts the file to be processed.
For example -> @pathAndFileName

At run time when the adapter picks up the file, it will generate the following to populate the @pathAndFileName stored procedure parameter:
For example -> C:\BTSBulkLoad\Files\ReceiveForSqlBulkLoad\FlatFileToProcess.txt

Note: Only a pointer to the flat file to be processed is passed to the stored procedure.
Note: UNC paths should also work, but have not actually tried.

h) The SqlStoredProcFileErrorParameter property is set to the name of the parameter in the stored procedure
to indicate the location to where the error files should be placed. See part d)
For example -> @pathAndErrorFileName

At run time when the adapter picks up the file, it will set the @pathAndErrorFileName stored procedure parameter to a Value.
For example -> C:\BTSBulkLoad\Files\FileErrorFolder

At run time when the File Receive portion of the adapter has picked up a file to process, the following code is then called in the adapter to invoke the stored procedure:

internal static string callBulkInsertStoredProcedure
   (string connectionString,
   int commandTimeOut,
   string storedProcedureName,
   string fileName,
   string fileErrorName,
   string fileNameParameterName,
   string fileErrorNameParameterName)
{
System.Text.StringBuilder sbForReturnedXML = new System.Text.StringBuilder();
SqlConnection sqlConnect = new SqlConnection();
sqlConnect.ConnectionString = connectionString;
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand.CommandText = storedProcedureName;

// Dynamically set the name of the parameters that should be called in the custom stored procedure.
// This is because each stored procedure may have parameters with different names.
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(fileNameParameterName, System.Data.SqlDbType.VarChar,500));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(fileErrorNameParameterName, System.Data.SqlDbType.VarChar, 500));
sqlCommand.Parameters[fileNameParameterName].Value = fileName;
sqlCommand.Parameters[fileErrorNameParameterName].Value = fileErrorName;

sqlCommand.Connection = sqlConnect;
sqlCommand.CommandTimeout = commandTimeOut;
System.Xml.XmlReader xmlReader = null;
sqlConnect.Open();

try
{
  xmlReader = sqlCommand.ExecuteXmlReader();
  xmlReader.MoveToContent();
  string xmlProductionOrderIdNode;

  while (!xmlReader.EOF)
  {
   xmlProductionOrderIdNode = xmlReader.ReadOuterXml();
   sbForReturnedXML = sbForReturnedXML.Append(xmlProductionOrderIdNode);
  }

  }
  catch (System.Exception ex)
  {
   System.Diagnostics.Debug.WriteLine("Error Occured in Adapter " + ex.Message);
   throw ex;
  }

return sbForReturnedXML.ToString();
}
}

The above is just basic ADO.NET code to call the stored procedure that will return back a small
XML message generated by the stored procedure. This XML message can contain any information. This small XML message will
be published into the MessageBox database. (More about this below).

The stored procedure configured in the receive location is executed. This is where the real processing of the flat file takes place.
Note: This stored procedure could be altered in any way to process the flat file. The below is just an example.

CREATE Procedure [dbo].[LoadInsertProductionOrders]

@pathAndFileName varchar(500),
@pathAndErrorFileName varchar(500)

-- This stored procedure will take the passed path of the flat file (produced by the the adapter) to process.
-- For example c:\directory\FlatFile.txt.
-- The code in this procedure will then Bulk Insert this flat file into a Sql Server table.
-- This stored procedure was originally created in Sql 2000, but was moved to Sql 2005
-- for the following reasons:

-- a) Sql 2005 has introduced a new argument for Bulk Insert Clause [ [ , ] ERRORFILE = 'file_name' ]
-- This will place malformed rows into an error file.

-- b) Sql 2005 has introduced Try Catch Blocks in T-Sql. In Sql 2000, if there were any malformed
-- rows in the flat file, an error would be raised to the .Net Helper Component and the operation would cease.
-- With the Try Catch Block in Sql 2005, no error is raised to the .Net Helper Conponent and processing continues.

-- Flat File rows will be inserted into this temp table.
-- Note: Could of used a Format File file, but this temp table matches the structure of the flat file.
-- Eventually, the rows in this temp table will be inserted into a permanent table.

CREATE TABLE #tempInsertNewProductionOrders (
[trk_unit_id] [int] NOT NULL ,
[pro_product_id] [int] NOT NULL ,
[actual_grade] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[actual_basis_weight] [float] NULL ,
[actual_length_at_turnup] [float] NULL ,
[actual_length] [float] NULL ,
[actual_weight_at_turnup] [float] NULL ,
[actual_weight] [float] NULL ,
[required_width] [float] NULL ,
[actual_width] [float] NULL ,
[required_diameter] [float] NULL ,
[actual_diameter] [float] NULL ,
[actual_core] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[actual_property_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[actual_property_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[actual_property_3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_timechain] [smallint] NULL ,
[update_time] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[update_user_id] [int] NULL ,
[position_index] [int] NOT NULL ,
[comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[required_length] [float] NULL ,
[required_weight] [float] NULL ,
[actual_mfg_grade] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[actual_moisture] [float] NULL ,
[actual_caliper] [float] NULL ,
[actual_colour] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[actual_finish] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[set_number] [int] NULL ,
[position_percent] [int] NOT NULL ,
[tare_weight] [float] NULL ,
[user_scale_weight] [float] NULL ,
[wire_side] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[trkc_length_adjust_type_id] [int] NULL ,
[actual_compression] [float] NULL ,
[actual_hardness] [float] NULL ,
[sch_prod_order_id] [int] NULL ,
[trk_set_item_id] [int] NULL ,
[trk_unit_id_package] [int] NULL )

declare @return int
declare @rowcount int, @error int

declare @sqlStatement varchar(8000)

-- Create the dynamic sql that contains the Bulk Insert Statement to Bulk Load the flat file into the Temp Table
-- An Argument could of been used to control the max number of error rows. As below:
-- MAXERRORS [ = max_errors ]
-- MAXERRORS, Specifies the maximum number of errors that can occur before the bulk copy operation is canceled.
-- Each row that cannot be imported by the bulk copy operation is ignored and counted as one error. If max_errors is not specified, the default is 10.

set @sqlStatement = 'BULK INSERT #tempInsertNewProductionOrders'
set @sqlStatement = @sqlStatement +    ' FROM ' + ''''+ ltrim(rtrim(@pathAndFileName)) +''''
set @sqlStatement = @sqlStatement + ' WITH '
set @sqlStatement = @sqlStatement +     ' ( '
set @sqlStatement = @sqlStatement + ' FIELDTERMINATOR = ' +   '''' + ',' + '''' + ','
set @sqlStatement = @sqlStatement + ' ROWTERMINATOR = ' + '''' + '\n'   + '''' + ','
set @sqlStatement = @sqlStatement + ' ERRORFILE = ' + '''' + ltrim(rtrim(@pathAndErrorFileName ))   + ''''
set @sqlStatement = @sqlStatement +   ')'

-- Temp Table for Sending back results

create table #results (rowsprocessed int,
ErrorCode int,
BatchIdentifier varchar(50))

-- Create a unique GUID, that will be stored in each row.
-- This is to differentiate the rows from other batches.
-- This GUID, will be returned back to the Adapter in a small XML message:

declare @newid uniqueidentifier
set @newid = newid()
declare @UniqueIdentifier varchar(50)
set @UniqueIdentifier = replace(convert(varchar(50), @newid),'-','')

Begin Try
-- Excecute the Bulk Insert Statement
exec (@sqlStatement)

-- Insert the rows from the temp table into the Permanet Table.
-- For each row, also set the Batch Guid.
Insert into InsertNewProductionOrders
Select #tempInsertNewProductionOrders.*,@UniqueIdentifier from #tempInsertNewProductionOrders

Select @rowcount = @@rowcount,@error = @@error
Insert into #results values(@rowcount,@error,@UniqueIdentifier)

End Try
Begin Catch

-- Catch any errors and re-raise

    DECLARE @ErrorMessage NVARCHAR(400);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE();
    SELECT @ErrorSeverity = ERROR_SEVERITY();
    SELECT @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
End Catch;

-- Send back a small informational XML message to the Adapter.
-- This XML message will be published in the Messagebox Database:

WITH XMLNAMESPACES ( DEFAULT 'http://SqlBulkInsert')
Select rowsprocessed, ErrorCode,BatchIdentifier,
(Select Distinct sch_prod_order_id ProductionOrderId
from #tempInsertNewProductionOrders ProductionOrder
For XML Auto ,Type )
from #results Results
For xml auto, ROOT('BulkInsertResults')

-- Note with Sql 2005 can:
-- a) Place a Root node around the returned XML
-- b) Specify a Target Namespace.
-- c) Do nesting with an XML Auto Statement.

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF

A sample of the XML returned from this stored procedure is as below:

The above XML message is then passed back to the Adapter. This Small message is then published into the MessageBox Database.
Note: When authoring this stored procedure, any information can be returned in this XML message.

Once this small XML message is published into the MessageBox database, any number of orchestrations can subscribe to this message. In this particular case, the main message (Now residing in a table in a Sql Server database) is to be split into separate messages by using the distinct production orders returned back in the above
XML message. A discussion of this pattern can be found here. The productions order records in the Sql Server table could also be transformed (mapped) by using Select Statements with XML Auto and XML Explicit clauses.

Results of Tests

The following hardware was used: Laptop with 2.0 GHz processor.
Windows XP operating System hosting a Windows 2003 VPC image with 1.3 MB of memory allocated.
This VPC image hosted the BizTalk 2006 Server and a Sql Server 2005 instance hosting the BizTalk databases.
The local Sql Server instance also hosted the database where Bulk Insert operation occurred.

Test for processing one Message

Only one flat file message was processed at a time. Only one implementation was tested at a time:
Either the PipeLine (Implementation one) or the SqlBulkInsert Adapter (Impelementation Two).

Results for Pipeline Processing

Size of Flat File	Time for Pipeline Processing	XML File Size (Published into the MessageBox)	Number of Rows (Production Orders) in Message
201 MB	25 Minutes	767 MB	807428
108 MB	13 Minutes	413 MB	435076
56 MB	7 Minutes	213 MB	224130
30 MB	4 Minutes	114 MB	120234
15 MB	3 Minutes	56 MB	62234
8 MB	2 Minutes	31 MB	29123

Results for SqlBulkInsert Adapter

Size of Flat File	Time For SqlBulkInsert Adapter Load	Number of Rows (Production Orders) in Message
201 MB	3 Minutes	807428
108 MB	1 Minute 15 Seconds	435076
56 MB	50 Seconds	224130
30 MB	15 Seconds	120234
15 MB	8 Seconds	62234
8 MB	5 Seconds	29123

Discussion of Results

Implementation One: Processing with a Flat File Schema and Custom Receive Pipeline

This is the usual method to process a flat file in BizTalk. Besides disassembling (converting) the flat file into an XML format, receive pipelines perform many other important operations such as:
a) Decoding
b) Disassembling
c) Validation
d) Custom operations using Custom pipeline Components. One example would be Unzipping a file.
etc.

The Good

a) It works!
b) From a design point of view, this is the preferred method as the pipeline can perform a number
of operations on the original message before the final message is published into the messagebox database.
c) Much more complicated flat files can be disassembled in a pipeline. The flat file used in the demo, is simple in structure.
An example of a more complicated delimited and positional flat file is as below, with Header, Detail and Footer Records:

PO1999-10-20
US Alice Smith 123 Maple Street Mill Valley CA 90952
US Robert Smith 8 Oak Avenue Old Town PA 95819
Hurry, my lawn is going wild!
ITEMS,ITEM872-AA|Lawnmower|1|148.95

The Bad

For large Flat File Messages:
a) the operation is somewhat slow (see times above),
b) CPU is pinned the entire time the flat file is being processed in the pipeline.
c) The original flat file message when converted to a XML, more than triples in Size, for example:
201 MB (flat file) transforms to 767 MB (XML). Depending on the operation being performed, a 767 MB message may be unmanageable (for example as a source message in a map).

Implementation Two : Processing with the SqlBulkInsert Adapter

This implementation was discussed in detail above.

The Good

a) Much faster in pre-processing the large messages (see times above).
b) Will not pin the CPU on the BizTalk machine (pipeline processing is skipped)
c) Large XML messages are not published into the messagebox database.
d) There really is not much to this -> A custom adapter and a stored procedure.

The Bad

a) A more complex flat file (see complex flat file message above) cannot be processed with this implementation. This is a limitation of the Bulk Insert Statement.
Other techniques could be investigated to Load structurally more complex flat files into Sql Server including:
i) DTS (Data Transformation Services). Sql 2000 and Sql 2005
ii) Sql Server Integration Services. Sql 2005
b) For the initial message the receive pipeline processing is skipped. If the incoming flat file was encoded or zipped up then the incoming flat file could be first be processed by another receive location/pipeline to decode or unzip, and then routed to a folder where the receive location is configured to use the SqlBulkInsert adapter.
c) The process (orchestration) that subscribes to the small XML message published into the MessageBox, has to be configured to connect to the database where the main message is stored in a relational sql table. This might mean just configuring Solicit - Send Send ports using a Sql Adapter
d) If using a Bulk Insert Statement to load in the flat file, Sql 2005 might have to be used. (See comments in the Stored Procedure)

Final Thoughts

I have only spent about two hours on the adapter and it is not ready for production (It is a prototype).
I have also tested by processing two large messages simultaneously. Therefore if you are interested in
using it, download the code and modify or just start from scratch. The code in the adapter is relatively straightforward.

The adapter was originally created with the BizTalk Server 2004 Adapter Wizard and uses the common adapter base classes and interfaces.

This technique (Adapter) could also be used to Bulk load an XML message into Sql Server.
Download the Code HERE. Read the ReadMe.Txt before installing and running.

Will Windows Workflow Foundation Replace BizTalk Server ?

2005-09-21T15:57:00Z

The answer is no. Here is an excerpt from David Chappell's excellent whitepaper: Introducing Microsoft Windows Workflow Foundation: An Early Look

Use Windows Workflow Foundation when:

An application will itself host workflows. Windows Workflow Foundation lets workflow be built into an application, allowing the workflow to be deployed and managed as a native part of the application. Because it's focused on integrating diverse applications rather than providing a general workflow framework, BizTalk Server always runs orchestrations within the BizTalk Server process.

The business process being implemented requires human workflow. BizTalk Server addresses system workflow, and so it lacks Windows Workflow Foundation's support for things such as state machine workflows and dynamic update. A scenario that requires both human workflow and more complex system integration services could be addressed by using Windows Workflow Foundation and BizTalk Server together, however. For example, the Office "12" support for document-centric workflows, based on Windows SharePoint Services, might be used for the human aspects of the problem, while BizTalk Server handles the system integration aspects. The two can interoperate using the BizTalk Server Adapter for SharePoint.
The workflow will execute on a client system. BizTalk Server is a server-focused product, and so it's less well-suited to run on desktop machines.

Use BizTalk Server when:

Solving an EAI problem that requires communication with diverse applications on diverse platforms. Because of its focus on cross-platform integration, a large set of adapters is available for BizTalk Server that allows communication with a range of other software. Windows Workflow Foundation is focused solely on workflow, not EAI, and so it doesn't provide these things.
B2B services are required. Windows Workflow Foundation doesn't address this area, while BizTalk Server provides tools for working with trading partners, accelerators for RosettaNet, SWIFT, and other industry standards, and more.
BPM services, such as Business Activity Monitoring (BAM), are required. While the Windows Workflow Foundation tracking infrastructure can be used to create these services, BizTalk Server provides important extras, such as tools that let information workers define their own BAM views.
A complete management infrastructure and support for increased scalability are required. Unlike Windows Workflow Foundation, BizTalk Server includes a full set of tools for administering and scaling a production environment.

BizTalk 2004 and 2006 -> Processing a large message and a faster, less CPU intensive splitter pattern.

2005-09-01T00:54:00Z

The following is a comparison of splitter orchestration patterns using BizTalk 2004 SP1 and BizTalk 2006 Beta 1. You can download the samples discussed below at the end of this blog entry and install and test on a BizTalk 2004 installation and/or BizTalk 2006 installation. Included in the download is a large test message (approximate size of 133 MB).

Some details of the tests are as below:

1) All orchestration implementations split or debatch the same message.
2) The xml message to split is approximately 133 MB in size (on disk)
3) There are a total of 80570 production orders in the message.
4) An abbreviated message with just one complete production order looks like the below.

5) Each production order in the message contains a production order id. For example: sch_prod_order_id = 10031
6) There are 51 distinct production order ids in the message.
7) In each splitter pattern implementation, 51 messages are split out from the main message, as defined by the 51 distinct production order ids. Each split message contains all the productions orders with one distinct production order id.
8) The size of the split messages range in size from 111KB to 23MB.
9) Four Different Orchestration Splitter pattern implementations were tested. The patterns were tested on a BizTalk 2004 SP1 installation with 1GB of memory and a BizTalk 2006 Beta 1 installation. All BizTalk databases (i.e. MessageBox etc.) ran on the same physical machine as the BizTalk servers. My BizTalk 2006 Beta 1 installation was installed on a VPC image. The image had only 700 MB of memory assigned to it, and needed more memory for a fair comparison with the BizTalk 2004 SP1 installation. Therefore the results for the BizTalk 2006 installation are incomplete.

Implementation One : Orchestration Splitting with a Map

The details on this splitter pattern can be found HERE. To quickly recap, a large production order message is processed by an orchestration and first run through a map utilizing custom XSLT to produce an xml message with the distinct production order ids as below:

<ns0:DistinctProductionOrders xmlns:ns0="http://SqlSplitterHelper">
<ProductionOrder ProductionOrderId="10001" />
<ProductionOrder ProductionOrderId="10003" />
<ProductionOrder ProductionOrderId="10002" />
</ns0:DistinctProductionOrders>

Each iteration of a loop in the orchestration is used to read a distinct production order id. Additionally, in each iteration of the loop, a map is used to filter out a split message containing productions orders with that iterations distinct production order id.

Implementation Two : Orchestration Splitting using a SQL Server database

One of the problems faced with Splitting the large messsage using a map, is the CPU utilization of the BizTalk server. During the life of a splitter map orchestration instance, the CPU is maxed out at 100% (Note: This was true with the BizTalk 2004 installation but did not seem to be with the BizTalk 2006 installation). If there is only one BizTalk Server in the group, this limits the number of orchestration instances that can be running simultaneously. Therefore why not use something else to split the message and not rely on CPU resource intensive XSLT? One approach is to use a SQL server database to help split the large message. Querying 80000 or more rows is fast and efficient in Sql server and each message can easily be split using a Select Statement (with a For XML Auto or XML Explicit clause). One of the problems is how to get the 133 MB message from a BizTalk Orchestration into a Sql Server database efficiently (explained below). Below are the steps done in the orchestration to get this pattern to work:

1) The large production message comes into the Sql splitter orchestration.
2) The large message is assigned to a variable of type : System.XML.XMLDocument

varXMLDomMessageToProcess = msgProductionOrdersToSplit;

3) A .Net helper component was called from the orchestration passing as one of the parameters the OuterXML of System.Xml.XmlDocument variable as below:

varstrReturnedXML = varObjSqlHelper.GetDistinctProductionOrders(varXMLDomMessageToProcess.OuterXml,varStrSqlConnectString,varGuidOrchestrationInstanceID);

Note: Ultimately a stored procedure is called by the .Net helper code to process the large production order message. A .NET helper component (called within an Atomic Scope shape) is used to call the stored procedure in place of Send/Receive shapes and a Send port using a Sql Adapter for the following reasons:
a) To cut down on the number of items that have to be persisted.
b) Passing the large string of XML to the stored procedure via a Send Port and Sql Adapter did not seem to work with the large message (Note: I did
not try this with BizTalk 2006 only BizTalk 2004).

4) The .Net Helper method, receives the string of XML (Note: This is the entire 133 MB message) and calls a stored procedure passing as one of the parameters the string of XML (Note: The stored procedure is called only once with the entire 133MB message). The Stored procedure then inserts all the production orders into a Sql Server relational table. It also returns back a string of XML with a list of distinct production order ids. The code for the .Net helper component that is called from the orchestration is as below:

public string GetDistinctProductionOrders(string xmlProductionOrderMessage,
      string connectionString,
     System.Guid orchestrationIdentifier)
{
string distinctOrdersXML;
// Set up SqlConnection,SqlCommand and Parameters to call Stored procedure -> InsertProductionOrders
SqlConnection sqlConnect = new SqlConnection();
sqlConnect.ConnectionString = connectionString;
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
// Below is name of the Stored Procedure that will be called
sqlCommand.CommandText = "[InsertProductionOrders]";
// Add the Parameters needed to call the stored procedure
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@xmlString", System.Data.SqlDbType.Text));
sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orchestrationIdentifier", System.Data.SqlDbType.UniqueIdentifier));
// Set the XML passed to the method to the @xmlString Parameter that will be receive by the stored proc.
sqlCommand.Parameters["@xmlString"].Value = xmlProductionOrderMessage;
// Set the UniqueIdentifier (Guid) so calls to splitter proc will get correct records for that orchestration instance
sqlCommand.Parameters["@orchestrationIdentifier"].Value = orchestrationIdentifier;
sqlCommand.Connection = sqlConnect;
// This will take a few minutes, so set to 0 to wait indefinitely
sqlCommand.CommandTimeout = 0;
// open connection to DB
sqlConnect.Open();
// Use an XmlReader to get back the Distinct XML of ProductionOrdersIds from the Stored Procedure
System.Xml.XmlReader xmlReader = sqlCommand.ExecuteXmlReader();
// Now use the XmlReader to get the XML returned by the stored procedure
xmlReader.MoveToContent();
string xmlProductionOrderIdNode;
System.Text.StringBuilder sbForReturnedXML = new System.Text.StringBuilder();
while (!xmlReader.EOF)
{
   xmlProductionOrderIdNode = xmlReader.ReadOuterXml();
   sbForReturnedXML = sbForReturnedXML.Append(xmlProductionOrderIdNode);
}
// Get all the XML from the String Builder
distinctOrdersXML = sbForReturnedXML.ToString();
xmlReader.Close();
sqlConnect.Close();
// Return the Distinct list of Production orders in XML Format
// Note: The calling orchestration will add the Root node and targetnamespace to this xml document.
return distinctOrdersXML;
}

5) The .NET helper component from above passes the large message down to a Sql Server Stored procedure (all 133MB in one call to the stored procedure).
A Sql Server 2000 database was created with one table and two stored procedures to perform the initial insert of the XML into a relational table and to
split out the messages. The objects inside of the database are:

Table -> InsertNewProductionOrders. To hold the contents of the production order XML in a relational table.
Stored Procedure -> InsertProductionOrders. Uses an OpenXML statement to take the XML and insert into the InsertNewProductionOrders table.
Stored Procedure -> GetSplitProductionOrderMessage. Is called to split out each individual message.

The Stored procedure (InsertProductionOrders) inserts the XML message into relational table (InsertNewProductionOrders) using an OpenXML statement. It
then executes a select statement (with a For XML clause) to return the list of distinct orders in the table. Other benefits of using OpenXML with BizTalk can be found HERE and HERE.

Stored procedure -> InsertProductionOrders is as below:

Create Procedure InsertProductionOrders

-- Will take string of passed XML and insert into a table

@xmlString Text, -- This is the String of XML for all the production orders
@orchestrationIdentifier uniqueidentifier -- Identifier for matching records to correct orchestration
instance

DECLARE @iDocProdOrders int

-- Use OpenXML to insert the orders into the table

EXEC sp_XML_PrepareDocument @iDocProdOrders OUTPUT, @xmlString, 'xmlns:ns0="'">http://SqlSplitterHelper"/>'
if @@error <> 0 goto err_handler

Insert Into InsertNewProductionOrders
Select *, @orchestrationIdentifier
From OpenXML (@iDocProdOrders,'//ns0:ProductionOrder',1)
              With (trk_unit_id int '@trk_unit_id',
pro_product_id int '@pro_product_id',
actual_grade varchar (80) '@actual_grade',
actual_basis_weight float '@actual_basis_weight',
actual_length_at_turnup float '@actual_length_at_turnup',
actual_length float '@actual_length',
actual_weight_at_turnup float '@actual_weight_at_turnup',
actual_weight float '@actual_weight',
required_width float '@required_width',
actual_width float '@actual_width',
required_diameter float '@required_diameter',
actual_diameter float '@actual_diameter',
actual_core varchar (80) '@actual_core',
actual_property_1 varchar (255) '@actual_property_1',
actual_property_2 varchar (255) '@actual_property_2',
actual_property_3 varchar (255) '@actual_property_3',
update_timechain smallint '@update_timechain',
update_time datetime '@update_time',
update_user_id int '@update_user_id',
position_index int '@position_index',
comment varchar (255) '@comment',
required_length float '@required_length',
required_weight float '@required_weight',
actual_mfg_grade varchar '@actual_mfg_grade',
actual_moisture float '@actual_moisture',
actual_caliper float '@actual_caliper',
actual_colour varchar '@actual_colour',
actual_finish varchar '@actual_finish',
set_number int '@set_number',
position_percent int '@position_percent',
tare_weight float '@tare_weight',
user_scale_weight float '@user_scale_weight',
wire_side char (3) '@wire_side',
trkc_length_adjust_type_id int '@trkc_length_adjust_type_id',
actual_compression float '@actual_compression',
actual_hardness float '@actual_hardness',
sch_prod_order_id int '@sch_prod_order_id',
trk_set_item_id int '@trk_set_item_id',
trk_unit_id_package int '@trk_unit_id_package')
if @@error <> 0 goto err_handler

EXEC sp_XML_RemoveDocument @iDocProdOrders
if @@error <> 0 goto err_handler

-- Now return back the list of Distinct Production Orders from the
-- table. The orchestration instance id is used to return back the correct Production orders
-- if two or more orchestration instances are running simultaneously.

Select distinct sch_prod_order_id as 'ProductionOrderId'
From InsertNewProductionOrders as ProductionOrder
Where OrchestrationIdentifier = @orchestrationIdentifier
For XML Auto

Return 0

err_handler:

Return -1

6) As with the map splitter orchestration, each iteration of a loop shape processes a distinct ProductionOrderId. (Note: the list of distinct ProductionOrderId's was returned by the InsertProductionOrders stored procedure discussed above). In each iteration of the loop, stored procedure GetSplitProductionOrderMessage is called to retrieve a split message containing productions orders with one distinct production order id. A Send and Receive Shape are used in the orchestration that use a Static Solicit - Response Send port to call stored procedure GetSplitProductionOrderMessage,passing it the following parameters:

@ProductionOrderID int, -- To filter out the production orders for that message.
@orchestrationIdentifier uniqueidentifier, -- GUID of orchestration instance.
@deleteIndicator int = 0 -- To indicate whether to delete this orchestrations instances records,
-- so as not to bloat table InsertNewProductionOrders with unecesarry records.

Stored procedure GetSplitProductionOrderMessage, then uses a For XML Auto clause to return back the split message as below:

Create Procedure GetSplitProductionOrderMessage

@ProductionOrderID int,
@orchestrationIdentifier uniqueidentifier,
@deleteIndicator int = 0

Select *
From InsertNewProductionOrders As ProductionOrder (nolock)
Where sch_prod_order_id = @ProductionOrderID
and OrchestrationIdentifier = @orchestrationIdentifier
For XML Auto -- , XMLData
if @@error <> 0 goto err_handler

if @deleteIndicator = 1
Begin
Delete InsertNewProductionOrders
Where OrchestrationIdentifier = @orchestrationIdentifier
if @@error <> 0 goto err_handler
End

Return 0

err_handler:

Return -1

Implementation Three and Four : Singleton Orchestrations Instances of the Map Splitter and Sql Helper Splitter

For those not familiar with BizTalk, multiple instances of the same orchestration type can be running simultaneously. For example, if the Map Splitter Orchestration subscribes to a receive port that has just accepted two large production order xml messages, two separate running instances of the Map Splitter Orchestration will be launched simultaneously to process the two messages (Note: this is not always true and is dependent on server load). Both the Splitter Orchestration using a map and Splitter Orchestration using a Sql Helper Database are resource intensive at certain point(s) during their lives as orchestration instances. If multiple orchestration instances are running simultaneously on the same BizTalk host instance machine, then at some point the host instance may run out of memory or CPU cycles. Details about when and why these patterns are resource intensive will be discussed below in the results section. To help alleviate the problem, one solution is to configure an orchestration to limit itself to one running instance. One straightforward method is as below:

1) A correlation type in an orchestration is created as below. CorrelationTypeForSingletonReceive is configured to correlate for all incoming messages on a physical receive port that an orchestrations logical receive port is bound to.

2) A Correlation Set is created in the orchestration that derives from the correlation type that was discussed above:

3) The first Receive Shape in the orchestration will initialize the above correlation set. No more orchestration instances of this type will be launched until this orchestration has completed.

4) An outer loop (Loop for Singleton) is then invoked with an expression of 1 == 1. This never ending loop will process all incoming production order messages, but will process only one at a time. Two nested loops are contained within the outer loop. Loop for Distinct Orders (collapsed in the below image) contains the necessary shapes to split and send out messages. Loop For Subsequent Messages will then pick up a new production order message that is ready for processing, but will only do so when the previous message has been completely processed and split into the 51 separate messages. A receive shape in this loop has its Follow Correlation Set property set to the Correlation set that was derived in step 2) and initialized by the first receive shape of the orchestration. This will pick up a new production order message to process.

5) Once a new incoming message is picked up, it will then go to the top of the outer loop to be split into separate messages.

6) One problem with the above singleton orchestration, is that new production order messages may be received and queued up in the messagebox while the singleton orchestration is processing another production order message. These new production order messages are subscribed to by the running singleton orchestration instance. But if this orchestration instance fails, then these queued up unprocessed messages will be suspended, in an unconsumed state. These Zombie messages will then have be dealt with. In this implementation, a Catch block is used to catch any errors in the orchestration. Inside the catch block is a loop shape. A receive shape in this loop has its Follow Correlation Set property set to the Correlation set that was derived in step 2) and initialized by the first receive shape of the orchestration. This loop will then pick up any of the unconsumed messages. These unconsumed messages could then be submitted back to the singleton orchestration. Additionally in BizTalk 2006, Suspended messages can be subscribed to by orchestrations, so it is possible that this technique could be used.

Results of Splitter Tests

Note: for all tests a series of System.Diagnostics.Debug.WriteLine were used in the orchestrations to capture the progress and processing times of the orchestration instances.
DebugView, was used to view the WriteLine statements in real time.

BizTalk 2004 Results

The following hardware was used: Laptop with 2.0 GHz processor and 1GB of memory.
Operating System : Windows XP SP2
This machine hosted the BizTalk server and a Sql Server with all the relevant BizTalk databases. The SplitterHelper database that is used by the orcSplitterUsingSql.odx and orcSplitterUsingSql_Singleton.odx orchestrations was installed on the BizTalk Server machine, but separate tests were also done with a remote SplitterHelper database installed on a Laptop with a 866 MHz processor and 512MB of memory. The BizTalk server machine and the remote Sql Server database were connected over a LAN.

Tests for processing One Message:

In the below result table, only one 133 MB production order message was processed at a time. Only one orchestration was enlisted and started for each test. Total times begin when an orchestration instance starts and ends when the entire message has been split. They do not include the initial publication of the message into the messagebox. The split messages were not sent out through a send port.
Note: The orchestrations can be configured to send out the split messages.
Note: Orchestrations orcSplitterUsingMap_Singleton.odx and orcSplitterUsingSql_Singleton.odx are not included in the results because only one message is being processed. They will have near identical results to orcSplitterUsingMap.odx and orcSplitterUsingSql.odx.

Below is a legend for the tests:

All times are in minutes:seconds, for example 3:45

Orchestration Name:
The name describes the pattern used.

Local SplitterHelper DB:
For the Map pattern orchestrations, Not Applicable
For the Sql pattern orchestrations, YES if a local SplitterHelper database was called on the BizTalk machine, NO if the SplitterHelper database was called on the remote Sql Server SplitterHelper database.

Get Distinct Orders:
For the Map pattern orchestrations, time to generate the distinct production order xml message using a map with a Custom XSLT template.
For the Sql pattern orchestrations, time when the orchestration instance starts to when the .Net helper component returns the distinct production order xml message to the calling orchestration. This time includes the SplitterHelper Database processing.

Time to Split:
For the Map pattern orchestrations, time to split out all 51 messages using a map.
For the Sql pattern orchestrations, time to split out all 51 messages using the SplitterHelper database GetSplitProductionOrderMessage stored procedure.

Total Time:
For all patterns, total time to process the entire message.

Orchestration Name	Local SplitterHelper DB	Get Distinct Orders	Time to Split	Total Time
orcSplitterUsingMap.odx	N/A	8:36	13:49	22:15
orcSplitterUsingSQL.odx	Yes	3:01	3:54	6:55
orcSplitterUsingSQL.odx	No	4:23	1:20	5:43

Discussion of Results

orcSplitterUsingMap
The CPU was pinned at 100% the entire time this orchestration was running. The map to get the distinct list of production orders and the map to split out the orders are responsible for the CPU utilization.

The good:
a) The whole splitting operation is contained within this orchestration. This simplifies administration and maintenance operations.

The bad :
a) With The CPU pinned at 100%, this limits and/or slows down other operations on the BizTalk server.
b) The total time for the orchestration to complete is not stellar at over 22 minutes.

orcSplitterUsingSql (local SplitterHelper DB)

While the above orcSplitterUsingMap is CPU intensive, the orcSplitterUsingSql is memory intensive, for the following reasons:
a) The operation to load the message in an XML Dom in the orchestration, uses a large amount of memory as the OuterXml is passed to the .NET helper component.
b) The OpenXML call in stored procedure -> InsertProductionOrders , uses a large amount of memory (basically the same reason as above) and is also CPU intensive.

The good:
a) This pattern is much faster. Taking only approximately 5-6 minutes to complete.
Note: Sometimes this pattern took longer, up to 12 minutes, because BizTalk was hogging all the memory and would not give up enough memory to Sql Server and the SplitterHelper database. But the remote Splitter Database had much better results. See below.
b) The CPU is not pinned at 100%.

The bad:
a) Uses quite a bit of memory. If more than one orcSplitterUsingSql instance is running simultaneously on the same server instance, then there will be out of memory issues. (This will be discussed in more detail below)
b) The Sql OpenXML call is memory and CPU intensive. (But the SplitterHelper database can be installed on another machine than the BizTalk Server. See below )
c) The SplitterHelper database is just something else to maintain. Even though it is only a temporary/scratch database, for a fully fault tolerant solution, this database should be installed in a clustered environment.
d) The structure of this production order message is simple and the Sql OpenXML call is relatively straightforward to write. But with a more complicated message, there would be more T-SQL to write in the stored procedures.
e) The split messages in this case are simple in structure and a For XML Auto clause is sufficient to return the proper xml. For more structurally complex split messages, a For XML Auto clause might not be able to return the desired XML. To get more control of the returned XML, a For XML Explicit clause can be used.

Note: The new XML features in Sql 2005 will help tremendously in processing XML messages in Sql Server.

orcSplitterUsingSql (remote SplitterHelper DB)

The above results for orcSplitterUsingSql (local SplitterHelper DB) can be applied here also. The only difference is that the SplitterHelper database is located on a different physical server than the BizTalk Server. When the Sql OpenXML call is executed, the high memory and CPU needs of this operation will be applied to this remote sql server instead of the BizTalk Server. This helps the BizTalk server a great deal as this heavy Sql OpenXML processing is delegated to another server. Comparing times, orcSplitterUsingSql (remote SplitterHelper DB) in general has better times (4-5 minutes) compared to the (5-6) minutes for the orcSplitterUsingSql (local SplitterHelper DB). Obviously having the SplitterHelper database on another physical machine helps tremendously. The Sql Server installed on the BizTalk machine has to process the persistence points and other BizTalk operations. Therefore having the SplitterHelper database installed on another machine really helps.
Note: Better times can be realized, the remote lightweight machine hosting the SplitterHelper database was a laptop with a only a 866MHz processor and 512MB of memory.

Test for processing Two Simultaneous Messages:

The same hardware set up was used as the tests for processing one message.

For more of a real world scenario, two identical 133 MB large messages were submitted simultaneously to be processed at the same time. Only one orchestration type was enlisted and started for each test. Only the total time to split the two messages are recorded in the below table. Total times begin when the first orchestration instance starts and ends when the two messages have been split. They do not include the initial publication of the message into the messagebox. The split messages were not sent out through a send port. A file receive location was used to submit the two messages at the same time. The receive location was first disabled, two 133MB messages were copied into the folder. The receive location was then enabled.

Orchestration Name	Local SplitterHelper DB	Total Time To Process
orcSplitterUsingMap.odx	N/A	45:23
orcSplitterUsingMap_Singleton.odx	N/A	42.11
orcSplitterUsingSQL.odx	Yes	22:49
orcSplitterUsingSQL.odx	No	22:46
orcSplitterUsingSQL_Singleton.odx	Yes	13:54
orcSplitterUsingSQL_Singleton.odx	No	11:38

Discussion of Results

orcSplitterUsingMap

Two instances of this orchestration were launched simultaneously. While watching the debug window, the two orchestrations ran neck and neck, basically splitting the exact same message at the same time. The memory on my machine was just about maxed out. If I added a third message to be processed, I do believe the machine would have run out of memory. The time to split two messages basically doubled, from splitting one message.

Goods and Bads: Basically the same comments as with the one message tests (see above)

orcSplitterUsingMap_Singleton

As explained above, this orchestration is configured to only ever allow one running instance of itself. Therefore it had similar results to the single message tests. The times were just doubled as two large messages were processed sequentially. From above, two instances of the orcSplitterUsingMap could be running at the same time, but any more and the orcSplitterUsingMap_Singleton will have to be used.

Goods and Bads: Basically the same comments as with the one message tests (see above)

orcSplitterUsingSql (local SplitterHelper DB) and orcSplitterUsingSql (remote SplitterHelper DB)

Grouped these two together because they had very similar results. This was fun to watch in the debug window. Two orcSplitterUsingSql orchestration instances would be started up at almost exactly the same time. I then had debug statements to see when the .Net helper component was called by the orchestration instances. Both orchestration instances would then try to call it a the same time, but there was just not enough memory to go around, and one instance eventually won out and started to call the stored procedure on the SplitterHelper Database. In HAT, I could see the losing orchestration instance dehydrating and rehydrating for another retry at the atomic scope shape. The losing instance upon rehydration, would then start at the top of the orchestration (last persistence point). This pattern repeated a few times, with the dehydration and rehydration of the loser orchestration instance. The winning orchestration instances call to the .Net helper component completed and now the losing orchestration instance started again to call the .Net helper component. The winning orchestration instance was now in a position to start splitting messages. The winning instance would split between 0 and 17 messages, and the loser instance would repeat the cycle of trying to call the .Net Helper component, dehydrate and then rehydrate. Eventually, nothing was happening, but then the BizTalk host instance automatically recycled itself (see below error messages and warnings from the event log). After the host instance recycled and started up, the loser orchestration instance successfully called the .Net Helper component, and then the two orcSplitterUsingSql instances happily started calling the Splitter Database at the same time to split their respective messages.

Below are some of the errors from the event log before the automatic recycling of the BizTalk host instance:

The Messaging Engine received an error from a transport adapter while notifying it with the BatchComplete event.

The Messaging Engine failed while writing tracking information to the database

The Messaging Engine encountered an error while deleting one or more messages

The Messaging Engine failed to retrieve the configuration from the database

There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive" Source: "XML disassembler" Receive Location: "SQL://redlake/SplitterHelper/" Reason: Exception of type System.OutOfMemoryException was thrown

The Messaging engine failed to process a message submitted by adapter:SQL Source URL:SQL://redlake/SplitterHelper/. Details:Could not find a matching subscription for the message. This error occurs if the subscribed orchestration schedule or send port has not been started, or if some of the message properties necessary for subscription evaluation have not been promoted. Please refer to Health and Activity Tracking tool for more detailed information on this failure

Finally two warning messages appeared in the event log, announcing the recycling of the BizTalk server instance:

All receive locations are being temporarily disabled because either the MessageBox or Configuration database is not available. When these databases become available, the receive locations will be automatically enabled.

An error has occurred that requires the BizTalk service to terminate. The most common causes are an unexpected out of memory error and an inability to connect or a loss of connectivity to one of the BizTalk databases. The service will shutdown and auto-restart in 1 minute. If the problematic database remains unavailable, this cycle will repeat.

Note: No split messages were lost or duplicated when the host instance was recycled. BizTalk maintains the state of the orchestration instances with persistence points.

Note: I sometimes became impatient waiting for the host instance to recycle itself. I then went into the Windows Task Manager and clicked on the Processes Tab. I would them find BTSNTSvc.exe process, right mouse buttoned on it to launch the pop up menu and clicked on End Process to terminate the BizTalk host instance (Note: This is a real nasty thing to do and I do not recommend it). After manually terminating the BTSNTSvc.exe process the host instance automatically restarted itself and then finished off processing the two orchestration instances.

Note: As explained above, the two orchestration instances were able to successfully complete after the host instance was recycled. One of the problems (I think), was that the winning orchestration instance was still holding onto a good chunk of memory (even after it called the .Net Helper component). But after the host instance recycled, the losing orchestration instance was able to get enough memory to process.

Note : As explained above the .Net Helper component is called within an Atomic Scope shape. A property on the Atomic Scope shape is called Batch and can be set to either True or False. If this property was set to True, and two orcSplitterUsingSql instances are running, only one instance is allowed to call the .Net Helper component and the other has to wait (I observed this by having a Debug statement inside the .Net Helper component). If it is set to False, then by watching the debug statements, both can call it simultaneously. Either way, true of false, the host instance had the same behavior of recycling itself. Setting it to True might be more beneficial in this case. HERE is a scenario where you might want to set it to False.

Note: Sometimes both orchestration instances dehydrated and rehydrated a number of times before one successfully called the .Net Helper component. The different patterns of dehydrating and rehydrating were observed when the batch property on the atomic scope shape was changed and when the two orchestration instances were launched at different times.

Note: HERE is another scenario similar to the above on processing and mapping a large message.

The good:
a) The good news is that no messages were lost, due to persistence points, and both orchestrations instances successfully completed. But other than that, there is really nothing good here.

The bad:
a) Recycling of BizTalk Hosts instances on a regular basis, is not acceptable in a production environment.
b) This is not a good pattern for multiple simultaneous large messages to be processed. If there was multiple instances running in the Host, then they could share the load and take on one large message each. But at some point there might be more large messages to process then available host instances (also you might run out of money buying more Enterprise versions of BizTalk Server). More memory on the BizTalk machine might help also. Moving the BizTalk databases to another physical machine would also help.
Note: One fix is to allow only one running singleton instance of this orchestration (see below).

orcSplitterUsingSql_Singleton (local SplitterHelper DB) and orcSplitterUsingSql_Singleton (remote SplitterHelper DB)

The Good:
1) This pattern can handle simultaneous large messages, by processing one at a time.
2) This pattern is much less CPU intensive then the map patterns.
3) Other orchestrations and operations can be running on the BizTalk server, while the sql splitter orchestration instance is splitting messages.
4) The host did not recycle, because only one orcSplitterUsingSql_Singleton orchestration instance is ever running.

The Bad:
Basically the same comments as with the one message tests (see above)

The overall winner:

If more than one large message is being submitted in the same timeframe, then the singleton orchestrations must be used. This is somewhat dependent on hardware for each BizTalk instance and the number of BizTalk instances assigned to the host running the orchestrations. The performance and time winner is clearly the orcSplitterUsingSql_Singleton (remote SplitterHelper DB), but the downside to this method is that a SplitterHelper database must be maintained and the extra code (.Net and T-Sql) that must be written to get this to work. Additionally, performance will be improved if the SplitterHelper database is installed on a dedicated machine.

BizTalk 2006 Tests

Some of the same tests were performed against the BizTalk 2006 installation. BizTalk 2006 was installed on a VPC Windows 2003 image (running on a Windows XP host) with only 700MB assigned to the VPC image. The BizTalk 2006 installation was handicapped compared to the BizTalk 2004 installation and not all the tests were run. Until the tests using BizTalk 2006 can be performed on the same hardware as the BizTalk 2004 installation, the comparisons are not accurate.

The patterns I do have results for are as below (one message being processed):

orcSplitterUsingMap -> 48:36
orcSplitterUsingSql (remote SplitterHelper DB) -> 17:01

Some of the observations I did make are:
The map to get the distinct list of production orders was much quicker on BizTalk 2006. 2 minutes compared to over 8 minutes on BizTalk 2004. When Splitting the messages using a map, the CPU was not pinned, but this mapping operation was very slow. I am attributing it to the fact that BizTalk 2006 being installed on a VPC image with only 700MB of memory assigned to it.

Final Thoughts

From reading -> Large messages in BizTalk 2004, what's the deal? it discusses
performance problems processing large messages with BizTalk 2004. Therefore, something to consider is using Sql Server to help with message processing in BizTalk:
1) This post has discussed, a BizTalk splitter pattern that has used Sql Server to help process a large message.
2) This POST discusses using Sql Server to help with a BizTalk aggregator pattern.
3) This POST discusses processing a large flat file using a Sql Server BCP operation to help split the message.
4) A future title of a post could be: Extending BizTalk Mapping and Transformations using Sql Server 2000/2005. A good demo for this future post, would be passing two large XML messages down into a Sql Server stored procedure, then using T-Sql to perform the actual mapping of the messages.
5) As mentioned above. Sql 2005 has quite of number of improvements and enhancements for processing XML.

I really would like to try the tests discussed in this entry on a proper BizTalk 2006 installation (I just do not have the time or spare machine to do so at the moment). From reading the documentation and from my few observations, there are real performance improvements with large messages and BizTalk 2006.

Please read all the posts in the below blogs. These are written by the BizTalk team members at Microsoft. Having an understanding of how BizTalk works internally is a must before you start your first BizTalk project.
BizTalk Performance Blog:
Core Engine Blog

The below whitepaper is a good read for performance and BizTalk
Microsoft BizTalk Server 2004 Performance Characteristics Whitepaper

You can download the samples discussed above HERE LINK and try on a BizTalk 2004 and/or BizTalk 2006 installation. Read the ReadMe.Txt before installing and running.

BizTalk Schema Presentation

2005-06-27T14:41:00Z

I am doing a BizTalk Presentation on Wed June 29 in Toronto.
Details can be found below.

Theme for the night -> BizTalk Schema DeepDive

First Presentation:

An Introduction to BizTalk Schemas

One of the first steps in designing a Messaging or Integration system is to have an understanding of the messages that will exchanged between the various participating applications.A paradigm to describe this is SOA (Service Oriented Architecture) and Contract First. In BizTalk, Schemas are used to define the XML, Flat File, EDI, and other types of messages that will be exchanged between the various applications.

Some of the topics discussed and demonstrations will include:

1) Why we need Schemas from a Business perspective and BizTalk perspective.
2) Various methods to create XML Schemas in the BizTalk development environment.
3) Using xsd schemas from other sources.
4) Uses for Schemas in BizTalk.
5) Property Schemas in BizTalk.
6) How to split messages in BizTalk using an Envelope Schema.

Second Presentation:

BizTalk Schemas and Non XML Message Formats

In Today's world, many different types of messages formats exist, for example Flat File, EDI, HL7. A .NET object can also be used to represent and store information about a particular message. In BizTalk, there are many mechanisms in place to create and consume these various types of messages.

Some of the topics discussed and demonstrations will include:

1) Creating Schemas to represent XML that will be deserialized into .NET objects and to represent xml serialized from .Net Objects
2) Flat File Schema Creation
3) Other Types of Schemas (for example HL7) that are available in BizTalk Accelerators.

To sign up please go here to the Toronto BizTalk Usergroup site:

http://www.btug.biz/

BizTalk -> More on Constructing Messages and Configuration Information for an Orchestration

2005-06-16T19:18:00Z

I recently was reading Scott's entry on Constructing Messages from Scratch with Embedded Resources. This is a good method, so take a look a look at it HERE.
Below is a variant on Constructing Messages in an Orchestration. This also can be used as a method to read any type of configuration information into an Orchestration.
This method needs:

1) A Sql Server table to store the templates for the XML Messages and/or Configuration Information.
2) A BizTalk XML Schema.
3) A BizTalk Map.

A Sql Server Table
Below is a create statement for the table with three columns:

if exists (select * from dbo.sysobjects where id = object_id(N'[GenericCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [GenericCodes]
GO

CREATE TABLE [GenericCodes] (
[Code] [varchar] (50) NOT NULL ,
[Value] [varchar] (7000) NOT NULL ,
[Description] [varchar] (500) NULL ,
CONSTRAINT [PK_GenericCodes] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Some sample data for the table is as below:

a) The Code column is the Primary Key of the table that describes the contents of the row.
b) The Value column contains the actual template XML message or Configuration value.
c) The Description column contains a description/purpose of the template xml message or configuration value,

In the example sample data above, the first two rows:

BaseXML_401ExtraInfoRequest
BaseXML_401FlatFileTemplate

contain template xml for messages that need to be constructed from scratch in an orchestration, sample as below:

<ns0:ShippingInfo ShipToAddress="Some Address" ShipToCity="Some City" ShipToCountry="Some Country" xmlns:ns0="http://ConstructingXMLMessages.ShippingInfo" />

The remainder of the rows, contains configuration information for a process:

Request401_FIFOMIBSend -> How many Processor orchestrations can be running at one time (1 or many)
Request401_MaxNumberofFilesToBatch -> How many incoming messages to batch together using a looping sequential convoy
Request401_TestMode -> To indicate if the system is in test mode or production mode.
Request401_TimeoutForFileBatching -> The max time to wait for the next message to batch.
Response401_DB2Process401Results_CommandTimeout -> Passed to a ADO.Net Helper component to update a DB2 database.
Response401_DB2Process401Results_Retries -> Used to configure a Component Retry Pattern.
Response401_DB2Process401Results_RetryInterval -> Used to configure a Component Retry Pattern.
TwoDigitYearSupport -> To help translate two digit years to four digit years in a map.

A BizTalk XML Schema

Create a BizTalk Schema called XmlTemplateAndConfigInfo.xsd as below:

Each node (could be an element or attribute) is of Data Type string. Notice that each nodes name matches a Code column name from a row in the Configuration table.
(Note: For this method to work, It is important to match the names. This will be explained below)
Each node is promoted to a distinguished field.

A BizTalk Map

Create a BizTalk Map called Map_PopulateXMLTemplateAndConfigInfo.btm as below:

Notice that the source and destination schemas use the same schema (XmlTemplateAndConfigInfo.xsd) that was described above.

A DataBase Lookup Functoid and Value Extractor functoid are used to populate each node on the Destination Schema.

The Configuration information for a DataBase lookup functoid looks like below:

The first Input parameter for a DataBase lookup functoid is the lookup value. In this case it should be set to a value of a row from the Primary Key Code column in the configuration table GenericCodes. Instead of Hard Coding this value in for example (Request401_TestMode), a link from the source schema is used. When the link has been created, go to the properties window as below and for the Source Links property set the value to -> Copy name. Therefore instead of the value of the source node being mapped over, the name of the node will be mapped over. This is why it is important for the name of each node to match a rows Code value in the GenericCodes table.

The second Input parameter for a DataBase Lookup functoid is the DataBase Connection string A UDL file was used to hold the connection string for the database as described HERE

A Value Extractor Functoid is then used to extract the XML Template or Configuration Value and map it the Destination node.

A Sample Orchestration to use the XML templates and Configuration Values

Sample Orchestration as below:

The following Messages and Variables are created in the Orchestration View for the Orchestration:

Messages:
msgEmptyXmlTemplateAndConfigInfo MessageType is set to the XmlTemplateAndConfigInfo.xsd schema discussed above.
msgPopulatedXmlTemplateAndConfigInfo MessageType is set to the XmlTemplateAndConfigInfo.xsd schema discussed above.
msgCreateFromXMLTemplate is a test message that is constructed from an XML Template.

Variables:
Both varXMLDomEmptyXmlTemplateAndConfigInfo and varXMLDomCreateFromXMLTemplate are variables of type -> System.Xml.XmlDocument
They are used to help in message construction.

1) In the First construct shape in the Orchestration -> Construct EmptyXmlTemplateConfigInfo
A Message Assignment shape is used to construct message -> msgEmptyXmlTemplateAndConfigInfo
The code in the Message Assignment is as below:

// First need an empty message to construct the Empty Config Info XML Message
// Note: The XML is just the Root Node Name and the target namespace of the schema XmlTemplateAndConfigInfo.xsd
// This is hardcoded, but should never change.
// This constructed message is needed in the next orchestration shape which uses a map to construct the msgPopulatedXmlTemplateAndConfigInfo
// message.
varXMLDomEmptyXmlTemplateAndConfigInfo.LoadXml(@"<ns0:XmlTemplateAndConfigInfo xmlns:ns0=""http://Demo.XmlTemplateAndConfigInfo""/>");
msgEmptyXmlTemplateAndConfigInfo = varXMLDomEmptyXmlTemplateAndConfigInfo;

2) In the Second construct shape -> Construct PopulatedXmlTemplateConfigInfo
A Transform shape is used to construct message -> msgPopulatedXmlTemplateAndConfigInfo.

The map -> Map_PopulateXMLTemplateAndConfigInfo.btm is used in the transform shape.
Message msgEmptyXmlTemplateAndConfigInfo is used as the Transform Source Message
Message msgPopulatedXmlTemplateAndConfigInfo is used as the Transform Destination Message

The map -> Map_PopulateXMLTemplateAndConfigInfo.btm will then be invoked to construct the
msgPopulatedXmlTemplateAndConfigInfo. The nodes in this message will be populated from the values in Sql
table GenericCodes.

3) In the third construct shape, this is an example of constructing a message from an XML template,
that originates from the Sql table GenericCodes.
A Message assignment shape is used to construct message -> msgCreateFromXMLTemplate

// Load the Template XML from the msgPopulatedXmlTemplateAndConfigInfo.BaseXML_401ExtraInfoRequest node.
// Because Distinguished fields are used, then the intellisense works as below
varXMLDomCreateFromXMLTemplate.LoadXml(msgPopulatedXmlTemplateAndConfigInfo.BaseXML_401ExtraInfoRequest);
msgCreateFromXMLTemplate = varXMLDomCreateFromXMLTemplate;

4) Below is an example of reading the msgPopulatedXmlTemplateAndConfigInfo for Configuration information.
The Decide shape (Decide If In Test Mode, Test Mode Rule) uses the following expression to determine if in test mode:

// Intellisense can be used because the Nodes in the XmlTemplateAndConfigInfo.xsd were promoted as
// Distinguished Fields.
msgPopulatedXmlTemplateAndConfigInfo.Request401_TestMode == "1"

Finally

The good things about this method are:

1) No Code. You do not have to write any .Net Helper code to help read in the XML templates and Configuration Values into an Orchestration.
2) This is a simple method that I originally used at a client where .NET skills were scarce. Therefore why burden them with .NET code to maintain/test etc. when they do not have the resources to do so.
3) As soon as the value in the GenericCode tables changes it will be automatically visible.
4) A tool such as Query Analyzer or the Sql Enterprise Manager can be used to change the XML Template and Configuration values in the database.
5) This is a central repository of configuration data that any BizTalk Server in the Group will point to.

The not so good things about this method are:

1) For each node to be populated in the Map_PopulateXMLTemplateAndConfigInfo.btm map, a separate round trip to the database must be done. But we are talking milliseconds.

2) Each orchestration that needs the configuration values, would have to add the necessary Construct shapes to invoke the Population map. But, it would be easy to create one orchestration to centralize this procedure and then let the other orchestrations call into this "Configuration Orchestration" using a Call Orchestration Shape. The "Configuration Orchestration" would then return an msgPopulatedXmlTemplateAndConfigInfo message as an out parameter.

Just a Note

I always try to do a bit of research on my Blog topics so I can point the reader to other techniques and different views. So below are some links to other blogs on BizTalk and Configuration. Depending on your needs, take your pick:

1) The BizTalk Configuration Dilemma
2) Using the Rules Engine for orchestration configuration values *BizTalk Sample*...
3).NET configuration files with Orchestration Hosts
4) How to store configuration data in the Enterprise Single Sign-On Database (SSO)
5) Using the BTSNTSvc.exe.config
6) Maybe BizTalk 2006, has some new features for configuration? Just
installed my copy and have not had the time to look yet.

Calling an Oracle Stored Procedure from a Sql Server 2000 Stored Procedure using a Linked Server

2005-06-14T01:07:00Z

I had to to this a while back -> Calling an Oracle stored procedure from a Sql Server stored procedure. The Oracle stored procedure accepted a number of parameters from the Sql Server stored procedure. The Oracle stored procedure would then execute and pass back a result set to the Sql Server calling procedure. A linked server was used in Sql Server to call the Oracle stored procedure. This is just to save anybody the pain in case they need to set this up.

This is a step by step example on how to set up the server where the Oracle database is installed
and the server where the Sql Server is installed. It also provides a test Sql Server stored proc calling an Oracle stored proc with parameters, with a result set being passed back to Sql Server.

The following are the steps necessary to call an Oracle Stored Procedure from Sql Server.

The test was done with two separate computers as below.

Pre Set Up:

Machine 1)

Windows XP , with Oracle Standard Server 8.1.7 installed.

Note: Other versions of Oracle may work.

Machine 2)

Windows XP , with Sql Server 2000 installed.

Machine 1) Oracle

1) Make sure the Distributed Transaction Coordinator Service is running.

2) Run the following Below Oracle Packages and Procs into the Oracle Instance.

Note :The Oracle stored procedure that Sql Server calls must be within a package.

IN parameters can be passed into the Oracle Stored Procedure, but any results

should be passed back in a ReturnVal OUT Table type parameter.

-----------------------------------------------------------------------

CREATE OR REPLACE PACKAGE CallFromSqlServerPackage

TYPE SqlReturnTbl IS TABLE OF Varchar(500)

/* The index is important, otherwise {resultset} doesn't work. */

INDEX BY BINARY_INTEGER;

PROCEDURE OracleProc

(

Param1 IN varchar2,

Param2 IN varchar2,

Param3 IN varchar2,

Param4 IN varchar2,

ReturnVal OUT SqlReturnTbl

);

END CallFromSqlServerPackage;

CREATE OR REPLACE PACKAGE BODY CallFromSqlServerPackage

PROCEDURE OracleProc

(

Param1 IN varchar2,

Param2 IN varchar2,

Param3 IN varchar2,

Param4 IN varchar2,

ReturnVal OUT SqlReturnTbl

)

Begin

/* From this package would call out to the Oracle Proc that would do the actual work */

/* IN and OUT type parameters could be used to pass data out and get data back in from this Oracle SP.*/

/* For example the call to the other proc would look like the below */

/* SomeRealSP( Param1, Param2, Param3, Param4 ); */

/* From the results from the above proc, place into the TABLE that will be returned, */

/* back to the calling Sql Server Proc. All data passed back would have to be in a Character format */

/* If necessary, the Sql Server Stored procedure, would have to convert the data into Integers/ Datetimes etc. */

/* The Sql Server Proc, would have to know the positions of the data in the rows returned back from Oracle. */

ReturnVal( 1 ) := 'Some Character Data';

ReturnVal( 2 ) := '340';

ReturnVal( 3 ) := '100.22';

ReturnVal( 4 ) := 'Dec 22 2004';

/* Below was test to see if doing a Rollback on the Sql Server Side, would roll this back */

/* If a Rollback was issued in the Sql Server proc, then the below statement was rolled back */

/* Insert into SCOTT.ACCOUNT values (3000,5); */

End OracleProc;

End CallFromSqlServerPackage;

Machine 2) Sql Server

The following Steps were followed as detailed below:

(How to set up and troubleshoot a linked server to Oracle in SQL Server) :

http://support.microsoft.com/kb/280106

1) The Oracle Client Software (8.1.7) was installed.

2) Install MDAC version 8, as outlined in the KB article

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en

3) Modify the registry as outlined in the KB article.

4) Restart the Server.

5) After the server is re-started make sure that the Distributed Transaction Coordinator Service is running.

6) The PATH System variable was edited, so the Sql Server Path, preceded the Oracle Path(s), see below:

The C:\Program Files\Microsoft SQL Server\80\Tools\BINN;C:\oracle\ora81\bin;C:\Program Files\Oracle\jre\1.1.7\bin;

7) A Service Naming Entry was created as below, to connect to the proper Oracle Instance on Machine 1)

8) The following statement was run in Query Analyzer, to set up the correct linked server:

Use master

EXEC sp_addlinkedserver 'Ora817Link', 'Oracle', 'MSDAORA', 'test2'

Note: that Ora817Link, is the name of the Linked Server Entry, test2, is the name of the Service Naming entry that was created above in Step 7)

9) Create the Credentials that will be used by the Linked Server to connect to the Oracle Server

Use master

/* Below will set the Credentials */

EXEC sp_addlinkedsrvlogin 'Ora817Link', 'FALSE',NULL, 'scott', 'tiger'

10) Run the following stored Procedure into Sql Server

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CallOracleProcTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[CallOracleProcTest]

CREATE Proc CallOracleProcTest

declare @createdTran tinyint

Set @createdTran = 0

-- Note : This must be set in order for Transactions to be Started and Rollbacked properly

SET XACT_ABORT ON

-- Starting a Transaction, Note: this is optional.

-- This technique, just describes how to test to see

-- if a transaction has already been started, by the caller

-- If not, then start the transaction.

If @@trancount = 0

Begin

Begin Transaction

Set @createdTran = 1

End

-- Use the below temp table, to capture the results returned back from the call to the Oracle Proc.

Create table #test (indicator int identity(1,1),

Oracle_Result varchar(500))

Declare @parma1ForOracle varchar(100),

@parma2ForOracle varchar(100),

@parma3ForOracle varchar(100),

@parma4ForOracle varchar(100)

Set @parma1ForOracle = '1'

set @parma2ForOracle = '100.2'

set @parma3ForOracle = 'This is info for Oracle test'

set @parma4ForOracle = 'Dec 12 2004'

/* NOTE: Below would build the Dynamic Sql Statement. Parameters passed to Oracle are built into the Dynamic Sql*/

/* Results from Call are placed into a Temp table. NOTE: That the position of the passed back info, would have

to be know by this proc*/

/* Note that resultset 25 , signifies that <= 25 rows can be returned back from the Oracle Stored proc*/

Declare @OracleCall varchar(8000)

set @OracleCall = 'Insert into #test(Oracle_Result) SELECT * FROM OPENQUERY(ORA817Link , ''{CALL SCOTT.CallFromSqlServerPackage.OracleProc('

set @OracleCall = @OracleCall + ''''''+ @parma1ForOracle + ''''''

set @OracleCall = @OracleCall + ',' + '''''' + @parma2ForOracle + ''''''

set @OracleCall = @OracleCall + ',' + '''''' + @parma3ForOracle + ''''''

set @OracleCall = @OracleCall + ',' + '''''' + @parma4ForOracle + ''''''

set @OracleCall = @OracleCall + ',{resultset 25, ReturnVal})}'')'

-- Below is where actually call the Oracle stored procedure.

exec (@OracleCall)

if @@error <> 0 goto err_handler

-- Now get the info passed back from the oracle stored procedure.

-- Note: Must know the row number of the data passed back from the oracle stored procedure,

-- and its type.

Declare @parma1FromOracle varchar(100),

@parma2FromOracle integer,

@parma3FromOracle real,

@parma4FromOracle datetime

Select @parma1FromOracle = Oracle_Result

From #test

Where indicator = 1

if @@error <> 0 goto err_handler

Select @parma2FromOracle = Convert(int,Oracle_Result)

From #test

Where indicator = 2

if @@error <> 0 goto err_handler

Select @parma3FromOracle = Convert(real,Oracle_Result)

From #test

Where indicator = 3

if @@error <> 0 goto err_handler

Select @parma4FromOracle = Convert(datetime,Oracle_Result)

From #test

Where indicator = 4

if @@error <> 0 goto err_handler

print 'Result of @parma1FromOracle = ' + ltrim(rtrim(@parma1FromOracle))

print 'Result of @parma2FromOracle = ' + ltrim(rtrim(str(@parma2FromOracle)))

print 'Result of @parma3FromOracle = ' + ltrim(rtrim(Convert(varchar(30),@parma3FromOracle)))

print 'Result of @parma4FromOracle = ' + ltrim(rtrim(Convert(varchar(30),@parma4FromOracle)))

if @createdTran = 1 and @@trancount > 0

Begin

Commit Transaction

End

SET XACT_ABORT OFF

return 0

err_handler:

print 'Error in proc'

if @createdTran = 1 and @@trancount > 0

Begin

Rollback Transaction

End

SET XACT_ABORT OFF

Return - 1

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

11) Test the Procedure :

begin tran

exec CallOracleProcTest

rollback tran

BizTalk 2004 -> Integrating with Sharepoint 2003 and when NOT to use the BizTalk WSS Adapter

2005-06-08T16:16:00Z

The Sharepoint Send/Receive Adapter released last March of 2004 on GotDotNet can be downloaded HERE.
It provides functionality to add (Send) items from BizTalk into Sharepoint Libraries and to pull (Receive) items that have been added into a Sharepoint Library and to submit them into the BizTalk environment. The Sharepoint Adapter is a great example of how a custom BizTalk 2004 Adapter can be built to allow BizTalk to communicate with any type of application.

Last year in 2004, I was part of a team working on a Sharepoint 2003 Portal/BizTalk 2004 project. BizTalk 2004 was used to integrate an HR (Human Resources) system with Sharepoint WSS libraries and Sharepoint Portal User Profiles. In fact, BizTalk was used to aid in the replication of the data from the HR system (in real time) to the Sharepoint Portal Server (i.e. any changes to the HR Employee Profiles (updates/inserts/deletes) were to be replicated in a timely manner to the Sharepoint Portal Server(s) WSS Lists and/or User Profiles).

More Specifically:

1) Employee profiles from the HR system were inserted, updated and deleted in a Sharepoint Library Lists.
2) Employee Resumes (doc,pdf,rtf formats) from the HR system were inserted,updated and deleted in Sharepoint Document Library Lists.
3) Employee Photos (tiff,jpg formats) from the HR System were inserted updated and deleted in Sharepoint Picture Library Lists.
4) Employee profiles from the HR system were used to create, update and delete Sharepoint User Profiles and to also generate MySites for each User Profile.
Note: User Profiles and MySites is a feature of Sharepoint Portal Server 2003 and not Windows Sharepoint Services (WSS). User Profiles can be used to store information about each portal user. Additionally each portal user can have their own MySite, where they can add/modify and delete their own specific content and have control of their own Web Site within the portal.

Some of the specific requirements/functionality/infrastructure of the integration project:

1) There were two Sharepoint 2003 Portal Servers:
a) English Portal Server (The English Portal Server was a load balanced web farm implemented with two physical servers hosting the English Portal.)
b) French Portal Server

2)
a) The English portal hosted the English User Profiles and English MySites.
On the English Portal there were two main WSS sub sites -> an English Sub Site and a French Sub Site.
Note: there was some discussion on moving the French Sub Site to the Physical French Portal server, but at the time of development the English and French Sub Sites were on the English Portal. Therefore the Employee profile, Resume and Picture libraries were duplicated on the English and French Sub sites of the English portal.
Note: We were trying to have one main list with both the English and French columns at the portal level and then create views of the this main list down on the English and French Sub Sites. This did not work. Therefore the duplicate libraries -> one on each sub site was used as below:

English Employee List:

French Employee List:

b) The French Portal Server hosted the French User Profiles and MySites

3) Any change made about an employee on the HR System (insert/update/delete), was to be replicated across to the Sharepoint Employee Profile List.

4) Any change to an Employee Picture or Employee Resume on the HR System (insert/update/delete), was to be replicated across to the Sharepoint Employee Picture Libraries and Resume Libraries. A Document or Picture Library can be thought of a regular Sharepoint list, except that a File is also associated with the list.

Additionally the HR system could indicate if only the actual Resume file or Picture file changed or only if text information about the the Employees changed (ie, their name or the city they worked in). This could be used to aid in performance -> i.e. if only the persons name changed, then do not bother updating the actual Resume Binary or Picture Binary and only update the Sharepoint List text columns (i.e. First Name, Last Name, City).

Below is the Detail View of the English Picture Library:

Below is the ThumbNail View of the English Picture Library:

5) When a File is added to a Document library, an actual URL (for example: http://spserver/sites/EnglishSite/EmployeePictures/Bob.jpg)
is generated that can be retrieved from a Sharepoint API method call as below:

url = spListItem.File.ServerRelativeUrl;

This URL was sent back to the calling BizTalk Orchestration, so that it could ultimately update a Sharepoint Portal Server User Profile URL property. For example, one of the default properties for UserProfiles is the picture URL. When the MySite of the user is navigated to, the picture url is used to automatically display the picture on the users MySite as below:

Below is the edit page for Bob the Builder's User Profile:

Below is the MySite for Bob The Builder.
Note: That the User Profile for Bob the Builder is used to populate the MySite.

6) If the particular Sharepoint Library did not exist, then automatically create it.
For example if BizTalk submitted an Employee Profile(s) to Sharepoint for processing and the particular library list did not exist, then:
a) Automatically create the list.
b) Populate the list from the submitted HR profile(s).

7) Any change made about an employee on the HR System (insert/update/delete), was to be replicated across to the Sharepoint User Profile Database.

8) A possible future enhancement was to have the information about Resumes also stored in Sharepoint Portal Areas. In this implementation Areas were used to represent the taxonomy of the company.

From the above functionality and requirements it became clear that it was going to be difficult or impossible to use the existing WSS adapter directly. Much finer grained control of the Sharepoint functionality was needed in order to implement the system.
Therefore there were two choices:

Choice 1) Extend the WSS BizTalk Adapter to handle the additional functionality.
Choice 2) Let Biztalk call custom web methods hosted by web services installed on Sharepoint Portal Servers. The custom web methods would receive as parameters information about employees in an XML format and additionally binary employee resumes and pictures. Custom code in the web methods would utilize the Sharepoint API methods in order to insert/update/delete items in the Sharepoint Lists and the Sharepoint User Profiles. The Web Methods could also return results, such as the URL's of the pictures or resumes to set into Sharepoint Portal User Profile properties.

In the end choice 2) was chosen to implement the solution.
The particular customer where I was doing the work had a number of strong .NET/Sharepoint/Web Services developers so it ultimately can down to the lead developers choice and he preferred choice number 2).

Choice 1), is also a viable option, but in retrospect, I am thankful that the custom web services method was chosen. Each Sharepoint List had it's own little quirks and the functionality to populate them varied from list to list. In the end it was much easier to have a separate set of web methods to control the population of each Sharepoint list and the Portal User Profiles. To make the code more modular, a common set of Sharepoint helper methods was used, so that code did not have to be duplicated, from web method to web method. If there were more Sharepoint Lists to be populated and the number of list to be populated grew over time, then using an adapter becomes more attractive. One of the things you lose by writing custom web methods, is that each List is tightly bound to a web method, so if the number of Sharepoint lists grow then the number of web methods also grows.

Below is a more detailed discussion of how the Custom Code was implemented and discusses in some detail using the Sharepoint API. Additionally at the end of this blog you can download the code that populates the Sharepoint Employee List.

A simplified flow of information from the HR System to Sharepoint went something like this:

1) From the HR System, information about the employees was delivered into Biztalk in an XML format.

2) A BizTalk Orchestration would then subscribe to the incoming Employee message.

A simplified Employee XML message is as below:

<ns0:EmployeeProfiles xmlns:ns0="http://BobTheBuilder">
<EmployeeProfile>
    <EMPLOYEE_ID>1</EMPLOYEE_ID>
    <LAST_NAME>The Builder</LAST_NAME>
    <FIRST_NAME>Bob</FIRST_NAME>
    <PHONE>111-1111</PHONE>
    <CITY>Toronto</CITY>
    <FUNCTION>Owner</FUNCTION>
    <PROCESSLISTINDICATOR>-1</PROCESSLISTINDICATOR>
</EmployeeProfile>
<EmployeeProfile>
    <EMPLOYEE_ID>2</EMPLOYEE_ID>
    <LAST_NAME>The Contractor</LAST_NAME>
    <FIRST_NAME>Wendy</FIRST_NAME>
    <PHONE>222-2222</PHONE>
    <CITY>Mississauga</CITY>
    <FUNCTION>Contractor</FUNCTION>
    <PROCESSLISTINDICATOR>-1</PROCESSLISTINDICATOR>
</EmployeeProfile>
</ns0:EmployeeProfiles>

The XML was converted to a string in the BizTalk Orchestration Expression shape. An example is as below.

varXMLDomEmployee = msgEmployee;
// Now the following can be used to extract the XML as a string
strEmployeeXML = varXMLDomEmployee.OuterXML.
More details about the above can be found HERE

If the Employee information was associated with a Resume, or a Picture, then in the Orchestration a .NET Helper Component using ADO.NET was called to retrieve the file from Sql Server. The code would return the Resume or Picture Binary as a string, from a Sql Server text column . An excerpt of the code is as below:

string result;
System.Byte[] resumeImageBytes;
resumeImageBytes = (System.Byte[]) this.sqlCmdGetResumeImage.ExecuteScalar();
result = System.Convert.ToBase64String(resumeImageBytes);
return result;

3) The string of Employee XML and optionally the Base64String of the Employee Resume or Picture was then sent to the Sharepoint Portal Servers from the BizTalk Orchestration via a Web Port to a Web Service installed on the Sharepoint Portal Servers. More detail about this can be found HERE

4) The particular web method on the portal Server would then accept as parameters the string of XML and optionally the binary Resume or Picture (as a string).

5) On the Web Server side, strongly typed datasets were used to hold configuration information for the Sharepoint Lists/Portal User Profiles and also included mapping information between the incoming Employee XML and the Sharepoint List Columns/ User Profile Properties. The dataset is as below.

XML installed on the Web Service side would then populate the dataset. A sample of the XML to populate the dataset is as below:

<?xml version="1.0" standalone="yes" ?>
<SharePointListData xmlns="http://tempuri.org/SharePointListData.xsd">
<SharePointList>
    <SharePointListID>1</SharePointListID>
    <WebLocation>sites/EnglishSite</WebLocation>
    <SharePointListDescription>Employee Profiles List</SharePointListDescription>
    <SharePointListTitle>Employee Directory</SharePointListTitle>
    <SharePointListPKColumn>EmployeeID</SharePointListPKColumn>
    <XMLSourcePKColumn>EMPLOYEE_ID</XMLSourcePKColumn>
    <TemplateName>Custom List</TemplateName>
    <Language>en</Language>
</SharePointList>
<SharePointListColumns>
    <SharePointColumnName>EmployeeID</SharePointColumnName>
    <XMLSourceColumnToMap>EMPLOYEE_ID</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Last Name</SharePointColumnName>
    <XMLSourceColumnToMap>LAST_NAME</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>First Name</SharePointColumnName>
    <XMLSourceColumnToMap>FIRST_NAME</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Phone</SharePointColumnName>
    <XMLSourceColumnToMap>PHONE</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>City</SharePointColumnName>
    <XMLSourceColumnToMap>CITY</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Function</SharePointColumnName>
    <XMLSourceColumnToMap>FUNCTION</XMLSourceColumnToMap>
    <SharePointListID>1</SharePointListID>
</SharePointListColumns>
<SharePointList>
    <SharePointListID>2</SharePointListID>
    <WebLocation>sites/FrenchSite</WebLocation>
    <SharePointListDescription>Les Profils d'employé Enumèrent</SharePointListDescription>
    <SharePointListTitle>Annuaire d'employé</SharePointListTitle>
    <SharePointListPKColumn>EmployeeID</SharePointListPKColumn>
    <XMLSourcePKColumn>EMPLOYEE_ID</XMLSourcePKColumn>
  <TemplateName>Custom List</TemplateName>
  <Language>fr</Language>
</SharePointList>
<SharePointListColumns>
    <SharePointColumnName>EmployeeID</SharePointColumnName>
    <XMLSourceColumnToMap>EMPLOYEE_ID</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Nom de famille</SharePointColumnName>
    <XMLSourceColumnToMap>LAST_NAME</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Prénom</SharePointColumnName>
    <XMLSourceColumnToMap>FIRST_NAME</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Téléphone</SharePointColumnName>
    <XMLSourceColumnToMap>PHONE</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Ville</SharePointColumnName>
    <XMLSourceColumnToMap>CITY</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
</SharePointListColumns>
<SharePointListColumns>
    <SharePointColumnName>Fonction</SharePointColumnName>
    <XMLSourceColumnToMap>FUNCTION</XMLSourceColumnToMap>
    <SharePointListID>2</SharePointListID>
</SharePointListColumns>
</SharePointListData>

The above XML contains all the information necessary to create and/or get a handle to the necessary Sharepoint list(s) and contains the mappings between the incoming XML and the Sharepoint Columns. For example: The LAST_NAME node in the incoming XML would map to the Last Name column in the English Sharepoint list and would also map to the Nom de famille column in the French Sharepoint list. This configuration XML was then stored in the Web Services Cache. When the XML file changed, the File Dependency Cache would automatically load in the new XML file. In this way, the configuration information about the Sharepoint Lists and mappings could be changed on the fly.

Below is Sample Web Dependency Cache loading code.

result.ReadXml(System.Web.HttpContext.Current.Server.MapPath(fileName));
// Save to the Web Cache. Put dependency on file, so when file is changed, old copy will get flushed from the Cache
System.Web.HttpContext.Current.Cache.Insert(cacheKey,
result,
new System.Web.Caching.CacheDependency(System.Web.HttpContext.Current.Server.MapPath(fileName)));

6) The Web Method would then call a helper class, that would actually do the processing
against the Sharepoint List(s)/User Profile Database with the Employee XML and the Configuration Dataset.

a) The Helper class would first determine if the required Sharepoint List(s) existed. The configuration dataset contained the necessary information to create the Sharepoint List(s) if they did not exist, otherwise just get a handle to the existing list. The code to create the list looks something like the below:

try
{
  // If the below WSS list exists, then get it.
  employeeList = this.siteCollection[0].AllWebs[(sharepointListRow.WebLocation)].Lists[sharepointListRow.SharePointListTitle];
}
catch (System.ArgumentException ex)
{
  // Means the WSS list did not exist, therefore create it.
  employeeList = null;
  System.Diagnostics.Debug.WriteLine("The List does not exist. error is as follows -> " + ex.ToString());
}
if (employeeList == null)
{
  // Create the WSS List
  string listTitle="" ,listDescription = "";
  listTitle = sharepointListRow.SharePointListTitle;
  if (!sharepointListRow.IsSharePointListDescriptionNull())
  {
   listDescription = sharepointListRow.SharePointListDescription;
  }
  else
  {
   listDescription = sharepointListRow.SharePointListTitle;
  }
  // This will add the new list to the List Collection of the Site.
  siteCollection[0].AllWebs[sharepointListRow.WebLocation].Lists.Add(listTitle, // Title of New List
                  listDescription,       // Description of the List
                  Microsoft.SharePoint.SPListTemplateType.GenericList); // Type of list need to create

  // This will get a handle to the just added list
  employeeList = siteCollection[0].AllWebs[sharepointListRow.WebLocation].Lists[listTitle];
  // Now add the necessary columns to the newly created list.
  // Also add the columns to the Default View of the List.
  // Note: This code only handles text type columns.
  Microsoft.SharePoint.SPView defaultView = employeeList.DefaultView;
  Microsoft.SharePoint.SPViewFieldCollection spViewFieldsCollect = defaultView.ViewFields;
  foreach (SharePointListData.SharePointListColumnsRow spColumn in sharepointListRow.GetSharePointListColumnsRows())
  {
    employeeList.Fields.Add(spColumn.SharePointColumnName ,Microsoft.SharePoint.SPFieldType.Text,false);
    spViewFieldsCollect.Add(spColumn.SharePointColumnName);
  }
  defaultView.Update();
}

b) The Helper class would then take the Configuration dataset and use it to map the Employee XML into the correct Sharepoint List columns. An indicator is also passed in with the employee XML. If it is set to -1, then the Employee in the Sharepoint list must be deleted, otherwise the Employee must be added to the list if it is not found or updated if it is found. A column in the Sharepoint list is used as a primary key for CAML Queries.

public void setDataInEmployeeSPList(string employeeXml,Microsoft.SharePoint.SPList employeeList, SharePointListData.SharePointListRow sharepointListRow )

{

System.Xml.XmlElement employeeChildNode;
System.Xml.XmlDocument employee = new System.Xml.XmlDocument();
employee.LoadXml(employeeXml);
System.Xml.XmlNode xmlNode;

xmlNode = employee.DocumentElement["PROCESSLISTINDICATOR"];
string deleteListIndicator = xmlNode.InnerText;

xmlNode = employee.DocumentElement[sharepointListRow.XMLSourcePKColumn];
string employeeID = xmlNode.InnerText;

Microsoft.SharePoint.SPListItem spListItemEmployee;
Microsoft.SharePoint.SPListItemCollection spListItemsEmployees;
// Call method that does CAML query to try to find the Employee in the list.
// If found, we will either update or delete it in the list.
spListItemsEmployees = this.QueryListForItems(employeeList,
             sharepointListRow.SharePointListPKColumn,
             employeeID);

if (deleteListIndicator == "-1")
{
  if (spListItemsEmployees.Count == 1)
  {
   // Means must delete the Employee from the list
   spListItemEmployee = spListItemsEmployees[0];
   employeeList.Items.DeleteItemById(spListItemEmployee.ID);
   //employeeList.Update();
  }
  else
  {
   // Means could not find the item in the list to delete
   System.Diagnostics.Debug.WriteLine("Could not find the Employee to delete in the library list.");

  }
}
else // Either the Employee must be updated or deleted in the Employee list
{
  spListItemEmployee = null;
  switch (spListItemsEmployees.Count)
  {
   case 1: // Found an existing Employee -> Update
    System.Diagnostics.Debug.WriteLine("Found in library");
    spListItemEmployee = spListItemsEmployees[0];
    break;
   case 0: // Employee does not exist. Create a new employee item in the list
    System.Diagnostics.Debug.WriteLine("Did not find in library");
    spListItemEmployee = employeeList.Items.Add();
    break;
  }
  // Now set the information from the Employee XML to the Employee Sharepoint List Columns
  foreach (SharePointListData.SharePointListColumnsRow employeeColumnsRow in sharepointListRow.GetSharePointListColumnsRows())
  {
   employeeChildNode = employee.DocumentElement[employeeColumnsRow.XMLSourceColumnToMap];
   if (employeeChildNode != null)
   {
    spListItemEmployee[employeeColumnsRow.SharePointColumnName] = employeeChildNode.InnerText;
   }
  }
  if (spListItemEmployee != null)
   spListItemEmployee.Update();
}
}

/// <summary>
/// Will query a list in Sharepoint. Only one column in the list is queried. For example City = "Toronto"
/// </summary>
/// <param name="listToQuery">The SPList to query</param>
/// <param name="columnToQuery">The name of the column to query in the SPList</param>
/// <param name="queryValue">The value to query for in the column</param>
/// <returns>A collection of List items that were returned by the query</returns>
public Microsoft.SharePoint.SPListItemCollection QueryListForItems(Microsoft.SharePoint.SPList listToQuery,
string columnToQuery,
string queryValue)
{
Microsoft.SharePoint.SPListItemCollection result;
Microsoft.SharePoint.SPQuery spQuery = new Microsoft.SharePoint.SPQuery();
spQuery.Query = "<Where><Eq><FieldRef Name='"+ columnToQuery +"'/><Value Type='Text'>" + queryValue + "</Value></Eq></Where>";
result = listToQuery.Items.List.GetItems(spQuery);
return result;

}

c) In the end, there was not too much code to write, somewhere between 50 and 100 lines of code to get the job done.

Some Notes about the Sharepoint API's

1) The Sharepoint API's will give the maximum amount of control over a WSS or Portal Site.
2) In order to use the WSS API you must make a reference to Microsoft.SharePoint.dll. The Sharepoint dll contains methods to interact with Sharepoint lists, Document/Picture libraries and much more.
3) In order to use the Sharepoint Portal API you must make a reference to the Microsoft.SharePoint.Portal.dll. The Portal dll contains methods to interact with a portals User Profile database, areas and much more.
4) The Sharepoint API's are organized into a series of collections. For example:
a) A Portal contains a collection of WSS sites
b) A WSS site contains a collection of Sharepoint Lists
c) A Sharepoint List contains a collection of Sharepoint columns and Items that contains the data in the List
5) The API's are fairly intuitive, but like everything have their own set of nuances.
For example when adding an new item into a Generic list, it looks something like the below:

// Just add the new item to the Items collection of the list
spListItemEmployee = employeeList.Items.Add();

But when adding a new item into a Document Library or Picture list, it looks something like the below:

// Must add the new item to the lists Folder Files collection
file = resumeList.SharePointFolder.Files.Add(xmlNode.InnerText /* Note.doc comes from PS+ ".doc"*/,Convert.FromBase64String(resumeByteStream));

BizTalk 2006

When BizTalk 2006 ships, it will include a built in out of the box WSS adapter. I am sure there will be improvements to the existing BizTalk 2004 WSS adapter that can be downloaded from GotDotNet. Read more about the new out of the box WSS adapter and other BizTalk 2006 Adapter additions and enhancements HERE

HERE download the sample code that populates a Sharepoint Employee List. Read the Readme.txt before installing and running.
Note: You must have Sharepoint WSS installed and Visual Studio 2003 on a Windows Server 2003 machine in order for the sample to work.

BizTalk 2004 -> Enterprise Integration Patterns and BizTalk

2005-04-18T01:14:00Z

Why use Patterns ?

1) They help in the design of an Integration Project.
Just like any other type of project, you need a good design in order to successfully develop and implement your solution.
For example:
If you are part of a team building a new ASP.NET application, most likely there will be some design and architecture development for a framework that will support the new ASP.NET application. The same can be said for an integration project, but on a different level.

Individual patterns can be described by one (Shape) as below.

With an integration project, the goal is to chain the patterns together as below:

The above diagrams were created in Visio. The Visio template that was used to create the above diagrams
can be downloaded HERE.

The above diagram is NOT a BizTalk specific solution, but really is an Integration (flow) diagram that describes the flow of messages through various system(s) or applications(s).

How the integration solution is implemented, is really up to the technology or technologies that are available
to the developers. It could be implemented using one or more of the following technologies:

a) .NET
b) Java
c) a Database such as Sql Server could be used to help in the implementation,
d) MSMQ could be used to help reliably deliver the messages from application to application
e) BizTalk could be used to help carry out the integration.
f) many more tools are available.

2) Patterns create well designed integration solutions, that produce
implementations that can be easily modified and maintained in the future.

3) Patterns can help with Testing

4) Patterns are simple (for the most part).

a) Splitter (Splits Messages)
b) Aggregator (Aggregates Messages)
c) Content Enricher (Will add more information or missing information to messages)
d) Normalizer (Normalizes messages into one common format)
e) There are approximately another 60 Patterns.

The name of the pattern often describes what the pattern does. But there is much to consider in an individual pattern.
For example:
In an Aggregator pattern, what happens if there are ten expected messages to be aggregated , but only nine messages show up?

Patterns and BizTalk

BizTalk can be used to implement patterns.
For example: If you have worked with BizTalk, you can quickly think of two ways to easily
split a message (a couple are an Envelope or split the message in an orchestration).

The following features in BizTalk aid in implementing patterns (Note: This is not a complete list)
a) The publish and subscribe model that BizTalk implements.
b) Physical Receive and Send Ports.
c) The correlation features in BizTalk (Correlation Sets, Property Schemas etc).
d) Mapping in BizTalk
e) The Delivery Notification and Ordered Delivery properties in logical ports
g) Role Links and Parties
f) more and more features.

Not all Patterns are a snap to build in BizTalk, but
BizTalk does provide a great set of features to implement many of the patterns.

Where to find BizTalk examples of Patterns:

There are many places to find examples of patterns in BizTalk.
Below are just a few:

Bloggers Guide to BizTalk, has a whole section devoted to Patterns in BizTalk

HERE

My last three Blog entries have discussed some examples of patterns

BizTalk 2004 -> Aggregator Pattern Using a Map and Orchestration. Also a Content Based Router Pattern

2005-04-12T15:04:00Z

Below is a method to aggregate many incoming messages into one message using a Map inside of a BizTalk Orchestration.

This Aggregator pattern is the third demo of a webcast I did on March 28 2005 called: BizTalk Server 2004 Implementing Enterprise Integration Patterns. It can be viewed HERE as a recorded Webcast. The full BizTalk solution can be downloaded at the end of this blog.

In this particular scenario, the messages split from the Splitter orchestration have been processed by a Production Order application. These processed messages
now have to be aggregated back into one complete message.

The three abbreviated incoming split messages to be aggregated look something like:

The final aggregated message to construct looks something like :

One solution to accomplish the above is discussed below :

1) Create an Orchestration to accept the incoming messages to be aggregated.

In the Orchestration:

a) The first Receive Shape initializes a Correlation Set to aid in the aggregation.
A looping sequential convoy pattern is used in the Orchestration to receive the incoming messages.The first message received by the orchestration is interrogated to determine the expected number of messages to receive. This attribute can be seen on the message such as : OrderCount="3". Therefore in this case loop
three times in the orchestration, because three messages are expected. Another attribute on the first incoming message is also interrogated such as :
UniqueIdentifier="7e96e060-3685-48bb-8113-9ae9957d8c4b". This value will be used to correlate the three incoming messages, into the correct running instance of the same orchestration. After the first message is received, only messages with UniqueIdentifier="7e96e060-3685-48bb-8113-9ae9957d8c4b" will be accepted. If another messages comes in with a different identifier such as : UniqueIdentifier="D9E9DF5E-0071-4ddd-962D-FD4478E4C6FE", then another instance of the Aggregator Orchestration will be created that just accepts messages with that identifier.
Note: The attributes OrderCount and UniqueIdentifier were added to the split messages by the Splitter Orchestration.

b) Three messages are declared in the Orchestration:

i) msgInternalRollsAll (This messages will be the final aggregated message sent out).
ii) msgInternalRollsAllTemp (This message is of the same type as above, but is used to help in processing, discussed below)
iii) msgInternalRollsOneOrderID (This message represents each of the separate incoming messages to be aggregated)

c) In the orchestration create a valid instance of the msgInternalRollsAll message. This is so the mapping (discussed below) will execute correctly the first time around in the loop.
The below is done inside an expression shape.

varXMLDomForMsgInternalRolls.LoadXml(@"<ns0:Rolls IdMill=""IdMill_0"" xmlns:ns0=""http://RollsInternal"" />") ;
construct msgInternalRollsAll
{
msgInternalRollsAll = varXMLDomForMsgInternalRolls;
}

d) Use a loop shape. The number of iterations for the loop is controlled by the attribute of the first incoming
message such as : OrderCount="3"

e) In the loop, assign the msgInternalRollsAllTemp to the msgInternalRollsAll message.
This is done so we can keep appending to the final output message as each loop executes.
The is accomplished inside an expression shape of the orchestration.

construct msgInternalRollsAllTemp
{
msgInternalRollsAllTemp = msgInternalRollsAll;
}

f) In the loop invoke a map that will keep appending to message -> msgInternalRollsAll:

The Destination message for the map is msgInternalRollsAll. A map with two inputs (two xml messages) is used to create the output message.
The first source for the map is the msgInternalRollsOneOrderID message that contains the contents of each incoming split message to be appended to the final outgoing message -> msgInternalRollsAll. The second source to the map is the msgInternalRollsAllTemp message. This is an interim message that is used to keep a copy of the final output message (msgInternalRollsAll) for each iteration of the loop. The map will then take the contents of two messages (nodes) and combine them into one node.
A looping functoid is used to combine the contents of two nodes into the final source node.
A more detailed explanation of this method can be found HERE.

g) Use a decide shape to determine if more messages are expected.
If more messages are expected, use a receive shape to receive the next incoming split message, then go to the top of the loop to append this split message to the final output message. If no more messages are expected then the final output message (msgInternalRollsAll) is sent to the
correct destination using a Content Based Router Pattern as below.

Content Based Router Pattern
----------------------------------

Initially a message came into the system from a particular Party. In the demo, messages can be received from three different parties : Mill One, Mill Two, Mill Three.
These messages were then Normalized, Split, Aggregated and now the final message must be routed back to the Mill that originally sent the message. Therefore the outgoing message contains some information about where it is going. In this case there is a attribute in the outgoing message such as :
IdMill="Mill_One". This value in the attribute will be used to dynamically route the message to the correct party. Therefore to implement this in a BizTalk Solution, Role Links in an Orchestration and Parties created in BizTalk Explorer were used. In the orchestration the following code in a expression shape is used to route the
message to the correct party:

varStrMillToSendTo = msgInternalRollsAll.IdMill;
RoleLinkToCorrectMill(Microsoft.XLANGs.BaseTypes.DestinationParty) = new Microsoft.XLANGs.BaseTypes.Party(varStrMillToSendTo, "OrganizationName");

If you have not used Role Links and Parties before, go to HERE and HERE for a more detailed explanation.

Again the full sample can be downloaded HERE . Read the ReadMe.txt file in the zip file, before unzipping and installing.

More Aggregator Patterns
------------------------------

Note: There are multiple ways to implement an Aggregator Pattern in BizTalk.
Another method includes using XML Document type .Net objects to build up the message in the orchestration:
Please see HERE and HERE for other examples.

BizTalk 2004 -> Splitter Pattern Using a Map and Orchestration.

2005-04-12T13:05:00Z

Below is a method to Split one message into multiple messages using a Map inside of a BizTalk Orchestration.

This Splitter pattern is the second demo of a webcast I did on March 28 2005
called: BizTalk Server 2004 Implementing Enterprise Integration Patterns. It can be viewed HERE
as a recorded Webcast. The full BizTalk solution can be downloaded at the end of this blog.

In this particular scenario, an Incoming message contains several production orders.
This message was created from a Normalizer(this is a pattern) Orchestration. A discussion
of the Normalizer Pattern can be found HERE.

The abbreviated incoming Normalized XML message looks something like the below:

From the above message there are three distinct Order Ids:

10049
10043
10048

Therefore the goal is to split the incoming Normalized message into three separate messages,
each with their own unique Order Id and respective line items as below:

Message one:

Message two:

Message three:

One solution to accomplish the above is discussed below :

1) Create an Orchestration to accept the incoming message and split it out into
separate messages:

In the Orchestration:

a) Find the distinct list of Order Ids in the message. One method to do this is to utilize some custom
XSLT in a BizTalk map as below :

<xsl:key name="Code-types" match="trk_unit_roll_ageable" use="@sch_prod_order_id"/>
<xsl:template match="/">
<ns0:Rolls xmlns:ns0="http://ObjectSharp">
<xsl:for-each select="//trk_unit_roll_ageable[count(.| key('Code-types', @sch_prod_order_id)[1]) = 1]">
<xsl:element name="ProdOrderIds">
  <xsl:attribute name="sch_prod_order_id">
   <xsl:value-of select="@sch_prod_order_id"/>
  </xsl:attribute>
</xsl:element>
</xsl:for-each>
</ns0:Rolls>
</xsl:template>

This XSLT can be used in a map. A method to include custom XSLT in a map is discussed in more detail HERE.

The final constructed message (msgDistinctOrdersIds) contains a list of distinct order ids:

b) Use an xpath statement to find a count of Distinct Order Ids.
In this case an xpath statement is used in the orchestration as below:

xpath(msgDistinctOrdersIds,"number(count(/*[local-name()='Rolls' and namespace-uri()='http://ObjectSharp']/*[local-name()='ProdOrderIds' and namespace-uri()='']))");

In the above example the xpath statement will return a value of three.

c) To create each separate message, a loop shape is used in the orchestration. The number of times to iterate in the loop is the value returned from the
xpath statement of step b).

d) For each iteration in the loop shape:
i) Use an xpath statement to find the current distinct Order Id in the iteration as below:
   varIntSchProdOrderId = xpath(msgDistinctOrdersIds,"number(//ProdOrderIds[" + varStrOrderCounter + "]//@sch_prod_order_id)");
   The message used in the xpath statement is the message created in step a). This is a message with the distinct order ids.
ii)Construct a helper XML message (msgSplitterParameters) that will be used in the map to aid in the splitting.
   In this case this message will contain the unique Order Id of the current iteration. An expression
   shape with the following code is used to create a brand new version of the message in each iteration of the loop.

varXMLDomSplittingParametersMsg.LoadXml(@"<ns0:SplitterParameters ProdOrderID=""0"" OrderCount=""0"" UniqueIdentifier="""" xmlns:ns0=""http://ObjectSharp"" />");

construct msgSplitterParameters
{
   msgSplitterParameters = varXMLDomForSplittingParametersMsg;
   msgSplitterParameters.ProdOrderID = varIntSchProdOrderId;
   msgSplitterParameters.OrderCount = varIntTotalNumberOfDistinctOrders;
   msgSplitterParameters.UniqueIdentifier = varStrUniqueIdentifier;
}

Note: A discussion of how to construct messages in an orchestration is HERE

e) For each iteration of the loop, Use a map in the orchestration and use the original input message to create a (split)
message with that unique order id:

A map with two inputs (two xml messages) is used to create the final output message. The first source used in the map is the original input message with all order items.
The second source is the parameter message constructed in part d). The whole trick of this implementation is to change this parameter message through each iteration of the loop.
Each iteration will produce the correct split message.

The map is quite simple. The first input is the original input message to create each split message with one unique order id. The second message (parameter message) is used to help filter out the unique line items with that order id in the first input of the map. A logical Equal functoid in the map is used to do the actual filtering.

For example for the first iteration of the loop in the above example, one of the values in the
parameter message is set to:

msgSplitterParameters.ProdOrderID = "10049"

Then when the map is invoked in the loop, the following split message will be produced :

Again the full sample can be downloaded HERE
Read the ReadMe.txt file in the zip file, before unzipping and installing.

Note: That the solution also contains a Normalizer Orchestration and
Aggregator Orchestration that are discussed in other blog entries.

Note: Many methods exist to split messages in BizTalk,
such as using Envelopes, Custom XSLT, XPATH, etc.
Please check out some of the other methods HERE , HERE, HERE, HERE

BizTalk 2004 -> Testing Orchestrations using Nunit, Submit BizTalk Adapter and a Normalizer Pattern

2005-04-12T00:47:00Z

To create a Nunit test of a single orchestration or a series of orchestrations is not a natural process.
I came up with this method for testing Orchestrations using Nunit when I was working on a BizTalk project. The client required that Nunit tests be performed on the orchestrations that were developed. It is also part of the first demo of a WebCast I did on March 28 2005 called: BizTalk Server 2004 Implementing Enterprise Integration Patterns. It can be viewed HERE as a recorded Webcast.
In the below example a Normalizer pattern Orchestration is being tested. The full BizTalk solution can be downloaded at the end of this blog.
The below details are somewhat involved, but if you download the above solution and install, analyze and run the BizTalk demos, it will become clear.

Getting back to the project. I was applying Patterns to this particular integration solution. This in turned produced a more modular type of solution with a Normalizer (this is a pattern) Orchestration and a Message Aggregator (this is another pattern) Orchestration. Because the solution was more modular in nature, it also made it easier to test. Additionally this method uses a Test harness Orchestration to asynchronously call the Normalizer Orchestration. In the production environment the test harness Orchestration would be replaced by another orchestration.

This testing method will not work for every type of Orchestration scenario, but works best if the Orchestration is modular in nature (i.e. implements some pattern). Even if you do not use this method for testing Orchestrations, this example shows:
1) A Nomalizer Pattern (two different BizTalk implementations are discussed).
2) How to pass multi-part messages between orchestrations.
3) How to conditionally turn an asynchronous call to another orchestration into a Synchronous Call.

The basic flow to test the Normalizer Orchestration is to:
1) Use .NET code in a Nunit Test Harness to produce a XML message that will ultimately be passed to the Normalizer Orchestration.
2) The .NET Code then calls the BizTalkMessaging.SubmitSyncMessage method to submit the XML message
to a Request-Response Receive Physical Port that uses the Submit Adapter. Because the SubmitSyncMessage method is used, the code will then wait for a response back from the Receive Port.
3) The Receive Port then publishes the incoming message from the Nunit test harness into BizTalk.
4) A Test harness Orchestration then subscribes to the message published by the Submit Adapter. This Orchestration will then use the Start Shape and asynchronously call the Normalizer Orchestration passing a couple of parameters, one containing the XML message for the Normalizer to work on and the other
being a Self Correlated Port.
5) The Normalizer Orchestration will then process the message and then construct a multipart message to be sent back to the Test Harness Orchestration. The Normalizer Orchestration will then call back to the Test Harness Orchestration using the Self Correlated Port passed in as a parameter.
6) The Test Harness Orchestration then constructs a response message using the results from the Normalizer Orchestration. This message is then passed back to the Nunit test harness code, using the the Logical Request-Response logical port in the Test Harness Orchestration.
7) The Request-Response Receive Physical Port described in 2) will then send the response message back to the .NET Nunit Test Harness code via the BizTalkMessaging.SubmitSyncMessage call.
8) The .NET code in the Nunit Test Harness can then interrogate the message sent back, and perform some tests on this message.

Therefore the important pieces to this are:
1) .Net code in the Nunit Test Harness
2) The Submit Direct Adapter
3) A Test Harness Orchestration. In a production environment, this orchestration would be replaced by another orchestration that will call the Actual Orchestration to be tested as in 4).
4) The Actual Orchestration being tested, in this case the Normalizer Orchestration.

To go into more detail:

1) In a Nunit test harness, submit a message
to BizTalk. (Note: this is not the full code)

string submitURI = "submit://MessageNormalizer";
btm = new BizTalkMessaging();
System.Xml.XmlDocument xmlDomRollsFromMillOne = New System.Xml.XmlDocument();
xmlDomRollsFromMillOne.LoadXML(@"c:\somexml.xml")
IBaseMessage responseMsg = null;
responseMsg = btm.SubmitSyncMessage(btm.CreateMessageFromString(submitURI,xmlDomRollsFromMillOne.OuterXml));
// Note: Because using the SubmitSyncMessage method, this code will now wait for the response message coming back.

Note:
Nunit 2.2.0, can be downloaded HERE

Note:
SubmitDirect BizTalk adapter can be installed by running ->
C:\Program Files\Microsoft BizTalk Server 2004\SDK\Samples\Adapters\SubmitDirect\Setup.bat
Also read about the SubmitDirect Adapter sample HERE

For users of BizTalk 2000 and BizTalk 2002, the SubmitDirect adapter replaces the BizTalk 2000 and 2002 API calls Submit and SubmitSync. The SubmitDirect Adapter allows the submittal of messages to BizTalk Server programmatically. Therefore you can write some C# or VB.Net code that can submit a message to BizTalk server and optionally receive back a response.

2) Create a Request-Response Receive Physical Port that uses the Submit Adapter as below:

3) Create some common orchestration types to help in the testing:

a) Multi-part Message Type: mpMsgTypeRolls
(This multipart is used to pass information to the Normalizer Orchestration)
mpMsgTypeRolls contains Message Parts:
i) msgCallBackToOrchestrationFlag. This flag is set to "1" if the Normalizer Orchestration is to call back
to the calling Orchestration. Therefore in the development/testing environment this flag is set to "1".
In the production environment this flag is set to "0". This is so I can use the Normalizer Orchestration
in the production environment without modifying anything and just changing the passed flag to "0"
ii) msgXMLRolls. This is a string of XML that contains the Roll Production Orders.

b) Multi-part Message Type: mpMsgTypeTestHarnessResults
(This multipart is used to pass information from the Normalizer Orchestration back to the test harness orchestration.)
mpMsgTypeTestHarnessResults contains Message Parts:
i) msgErrorMessage. This is a string that will be set with any error message.
ii) msgNumberOfRollsSplit. This is populated by Message Splitter Orchestration
iii) msgXmlRolls. This is returned with the mapped normalized message.

c) Multi-part Message Type: mpMsgTypeInvalidMessage
(This multipart is used to pass information from the Normalizer Orchestration, to the Invalid Message Orchestration.
mpMsgTypeTestHarnessResults contains Message Parts:
i) msgInvalidXMLMessage. This contains the invalid xml that the Normalizer Orchestration cannot parse.

d) Port Type: PortTypeTestHarnessResults
This port type is used to create a port that will be passed as a parameter into the Normalizer Orchestration.
The Normalizer Orchestration will then call back on this port to the calling orchestration.

4) The test harness orchestration then receives the message from the Nunit test harness via the Submit Receive Port, and then in turn calls the Normalizer Orchestration with the following parameters:
i) mpMsgRollsRequest. This multipart message is populated with the XML message to normalize and a flag to
indicate to call back to this particular orchestration.
ii) PortReceiveBackTestHarnessResults. Port of type PortTypeTestHarnessResults. This is a Direct - Self Correlating Port.

The Normalizer Orchestration can then call back to the test harness orchestration even though the
test harness orchestration called the Normalizer Orchestration asynchronously. A good discussion of Direct
Port Binding Types can be found HERE

5) The Normalizer Orchestration then receives the information from passed parameters and
then Normalizes the message. Because an untyped message is passed to this orchestration, it must first determine the map to invoke. Therefore a property on the message is interrogated to determine the type of message :
varStrMessageType = msgIncomingRolls(BTS.MessageType);
varStrMessageType is then populated with the correct message type such as -> http://RollsFromMillOne#Rolls.
This is of course the TargetNameSpace#RootNodeName combination of the incoming message. A decision shape in this orchestration will then decide what map will be used to Normalize the message based on the contents of the Message Type. If the message is unrecognized, then the InValid MessageChannel Orchestration is called. (This is another pattern).

6) Once the Normalized message has been processed, the Test Harness Orchestration is called back from the Normalizer Orchestration using the port PortCallBackResults that was passed as a parameter by the calling test harness orchestration. This is how an asynchronous call can be conditionally turned into a synchronous call.

7) The Test Harness Orchestration then calls back to the to the Submit Direct Test harness code in 1).This code, can then perform tests on the results sent back.

For a comprehensive Nunit Test Framework please go HERE

Another Normalizer Pattern
-------------------------------

A much simpler method to create a Normalizer pattern in BizTalk is by specifying a Set of Maps in
a Physical Receive Port or Physical Send Port as below:

Depending on message type of the incoming message, one of the following maps will be invoked
producing the normalized message:

MessageType	Map Invoked
http://RollsFromMillOne#Rolls	Map_RollsFromMillOne_To_RollsInternal.btm
http://RollsFromMillTwo#Rolls	Map_RollsFromMillTwo_To_RollsInternal.btm
http://RollsFromMillThree#Rolls	Map_RollsFromMillThree_To_RollsInternal.btm

In the above scenario, no orchestrations are required and all the necessary mapping can be
accomplished in Physical Receive or Send Ports

Again the full sample can be downloaded HERE. To install and run, Read the ReadMe.txt file in the zip file, before unzipping and installing. Also included with the sample are a Splitter Pattern Orchestration and an Aggregator Pattern Orchestration that will be discussed in future blog entries.

BizTalk Server 2004 and Sql Server 2000 -> XML Auto and XML Explicit

2005-03-23T03:20:00Z

BizTalk 2004 provides support for processing XML documents returned from Sql Server
via the Sql Adapter that ships with BizTalk Server 2004.

For example:

Select *
From Customers
For XML Auto

will produce an XML document that can be processed by BizTalk 2004 received via the Sql Receive Adapter.

The BizTalk documentation states that only the XML Auto clause is supported and
not the XML Explicit clause. But in fact BizTalk can process Sql Server XML documents that
are generated using the XML Explicit clause (this will be explained below).

If you have not used the Sql Adapter before, try the following two examples in the BizTalk SDK:

Using the SQL Adapter with a Stored Procedure in an Orchestration

SQL Adapter (BizTalk Server Sample)

For BizTalk to interact with an XML document originating from Sql Server,
one of the first steps is to create a xsd schema that describes the
XML returned from a Select statement.
This xsd schema can be automatically generated in a BizTalk project by Adding a Generated Item, as outlined
HERE This is only if the Select statement has an XML Auto clause (and XMLData clause, that is removed after the
schema is generated). If a Select Statement with a XML Explicit clause is used to generate the
schema, the xsd schema generation will fail with an error such as : The required attribute 'name' is missing.
An error occurred at , (0,0). Or : Failed to execute sql Statement. Please ensure that the supplied syntax is correct.

This is possibly why the BizTalk documentation states that the XML Explicit is not supported.
But again, BizTalk can use XML returned by XML Explicit (explained below).

So why use the XML Explicit clause over the XML Auto clause? Simply because the XML Explicit clause
gives you much more control of the XML structure that is returned by Sql Server compared to
the XML Auto clause.

For example, an Order is associated with a Shipper, Customer and Order Items.
The following Select Statement using XML Auto:

Select [Order].OrderID,
Shipper.ShipperID as ShipperID,
Shipper.CompanyName As ShipperName,
Customer.CompanyName as CustomerName,
OrderDetails.Quantity as Quantity,
OrderDetails.UnitPrice as UnitPrice,
OrderDetails.ProductID as ProductId
From Orders as [Order]
Join Shippers as Shipper
On [Order].ShipVia = Shipper.ShipperID
       Join Customers as Customer
On [Order].CustomerID = Customer.CustomerID
        Join [Order Details] as OrderDetails
On [Order].OrderID = OrderDetails.OrderID
For Xml Auto

Will return XML in the format of:

<Orders>
<Order OrderID="11077">
  <Shipper ShipperID="2" ShipperName="United Package">
   <Customer CustomerName="Rattlesnake Canyon Grocery">
    <OrderDetails Quantity="24" UnitPrice="19.0000" ProductId="2" />
    <OrderDetails Quantity="4" UnitPrice="10.0000" ProductId="3" />
   </Customer>
  </Shipper>
</Order>
<Order OrderID="11076">
  <Shipper ShipperID="2" ShipperName="United Package">
   <Customer CustomerName="Bon app'">
    <OrderDetails Quantity="20" UnitPrice="25.0000" ProductId="6" />
    <OrderDetails Quantity="20" UnitPrice="23.2500" ProductId="14" />
    <OrderDetails Quantity="10" UnitPrice="9.2000" ProductId="19" />
   </Customer>
  </Shipper>
</Order>
</Orders>

  Note: That the top level parent node Orders is added by the Sql Adapter

The above format is not desired because the parent node
of the Customer node is the Shipper node and the parent node of the
OrderDetails is the Customer node. In this case the desired format of the
XML message will have the Order Node being the parent node of the
Shipper, Customer and OrderDetails nodes as below:

To return the XML in this (above) format, the below Select statement with a XML Explicit clause is used:

SELECT 1 as Tag,
NULL as Parent,
Orders.OrderID as [Order!1!OrderId],
Null as [Shipper!2!ShipperID],
Null as [Shipper!2!ShipperName],
Null as [Customer!3!CustomerName],
Null as [OrderDetails!4!OrderId!hide],
Null as [OrderDetail!5!Quantity],
Null as [OrderDetail!5!UnitPrice],
Null as [OrderDetail!5!ProductId]
From Orders
Join #OrdersToGet
On #OrdersToGet.OrderID = Orders.OrderID
Union All
Select 2,
1,
#OrdersToGet.OrderID,
Shippers.ShipperID,
Shippers.CompanyName,
Null,
Null,
Null,
Null,
Null
From Shippers
Join #OrdersToGet
On #OrdersToGet.ShipVia = Shippers.ShipperID
Union All
Select 3,
1,
#OrdersToGet.OrderID,
Null,
Null,
Customers.CompanyName,
Null,
Null,
Null,
Null
From Customers
Join #OrdersToGet
On #OrdersToGet.CustomerID = Customers.CustomerID
Union All
Select distinct 4,
1,
#OrdersToGet.OrderID,
Null,
Null,
Null,
OrderDetails.OrderID,
Null,
Null,
Null
From [Order Details] as OrderDetails
Join #OrdersToGet
On #OrdersToGet.OrderID = OrderDetails.OrderID
Union All
Select 5,
4,
OrderDetails.OrderID,
Null,
Null,
Null,
OrderDetails.OrderID,
OrderDetails.Quantity,
OrderDetails.UnitPrice,
OrderDetails.ProductId
From [Order Details] as OrderDetails
Join #OrdersToGet
On #OrdersToGet.OrderID = OrderDetails.OrderID
Order By [Order!1!OrderId],Tag,[OrderDetails!4!OrderId!hide]
For XML Explicit

Note: The above Sql Statement is part of a stored procedure that
can be downloaded at the end of this blog.

The XML Explicit clause is used with Unions of Select statements
and the keywords -> Parent and Tag to generate the desired result.
The Order By clause is also very important, when using XML Explicit.
It's a good idea to take off the XML Explicit clause to view the regular sql result set (especially if the
format of your returned XML is incorrect or to help debug).
A good description of the XML Explicit clause with some examples is as below:

Using EXPLICIT Mode

Below describes how to create a BizTalk xsd Schema for XML returned using a XML Explicit Sql Statement:

1) Create and test (perhaps using Query Analyzer) a XML Explicit Sql Select statement
that includes the Tag, Parent and Unions of Select Statements.

2) Create a Sql Receive port that will invoke the Select Statement (this could be a stored proc)
that contains the XML Explicit clause. This Sql Receive Port should use the PassThrough Pipeline.
Note: in the Receive Location properties you must include the namespace of the
generated XML, Root Node name, Select Statement or Stored Procedure. In this example it was :
Document Target NameSpace : http://ObjectSharp/Orders
Document Root Element Name: Orders
Sql Command: exec BTSGetOrders 1000

3) Create a File Send Port that subscribes to the Receive Port, for example create
a filter on the Send Port with BTS.ReceivePortName = "Name of Receive Port Here"
This Send Port should also use the PassThrough Pipeline.

4) Turn on the above two ports and let a sample message be placed into the File Location
of the Send Port.

5) Create a new BizTalk Project or use an existing one and add a Generated Item. Use the Generate
Schemas option and Select -> Well-Formed XML. Select the xml file produced in step 4).
A xsd schema is now generated that can be used in a BizTalk Solution. If this BizTalk
project is deployed, the Receive Port and Send Ports created in Step 2) and 3) can now use the
standard XML Pipelines.
Note : It may be necessary to modify the xsd schema to alter the attribute types
min max properties etc.

6) The produced xsd schema in 5) can now be used in a BizTalk Solution for Mapping, Messages etc.
Orchestrations can subscribe to xml messages that are derived from a Select Statement that uses
the XML Explicit Clause.

Conclusion: Select Statements using the XML Explicit clause can generate XML documents
that can be consumed by BizTalk Server 2004.

Download the Sample Stored procedure and XSD file HERE

Note : The sample uses tables found in the NorthWind Database that is optionally installed with
Sql Server.

BizTalk Server WebCast -> BizTalk Server 2004 Implementing Enterprise Integration Patterns

2005-03-15T13:06:00Z

I am doing a WebCast on Mar 28 2005 :

BizTalk Server 2004 Implementing Enterprise Integration Patterns

Description: This intermediate level presentation will discuss how to create various Enterprise Integration Patterns in BizTalk 2004. Discussions and demonstrations will include patterns such as: Message Translator, Message Broker, Message Splitter, and Message Aggregator.

To sign up :

https://www118.livemeeting.com/cc/lmevents/view?id=msft032805ad&pw=RWRP4B

(I have updated the link to the recorded webcast. Matt May 10 2005).

Bloggers Guide To BizTalk, Dec 2004 Edition

2004-12-13T15:25:00Z

If you are working on a BizTalk 2004 project, please follow the below link to download this extremely useful source of information, kindly provided by Alan Smith :

http://geekswithblogs.com/asmith/archive/2004/12/12/17341.aspx

BizTalk 2004 Map, Getting Distinct Values using an Inline XSLT Call Template

2004-11-24T14:31:00Z

Need to get a distinct list of items from an XML message.

For example the XML message containing Order Items is as below:

(Note: That the Order ids are not sorted)

From the above message produce a distinct list of order ids such as below:

The custom XSLT code to get the distinct list of values looks like this:

To use an Inline XSLT Call Template inside of a BizTalk 2004 Map,
a) Create a new Map inside of your BizTalk project
b) Choose the source and destination schemas
c) Drop a Scripting Functoid on the Map, then in the properties window with
the Scripting Functoid selected, press the -> Configure Functoid Script button.
d) For the Script type choose -> Inline XSLT Call Template. Place the XSLT into the Inline Script Buffer
window.

The map to get the distinct Order Ids looks like the below:

Note : The scripting functoid inside of the map has no incoming or outgoing links.
The Custom XSLT will handle the complete transformation. Ignore any warnings about no incoming
or outgoing links when building the project.

Download the Complete solution HERE

Creating/Modifiying Schemas in BizTalk 2004. Quick Tip.

2004-11-16T01:51:00Z

When BizTalk 2004 is installed on a developer machine, one of the many things that
is installed is the BizTalk Editor. The BizTalk Editor is used to
create and modify .xsd schemas in VS2003 that will eventually be used in a BizTalk process.

The BizTalk Editor is broken up into three parts:
1) The left hand side displays a tree structure to add new nodes to the specification.
2) The centre displays the xml markup representation of the schema.
3) The right hand side displays the properties pane, to edit the properties of the currently
highlighed node on the left hand side tree structure.

This is illustrated below.

One of the limiting factors of the BizTalk Editor is that the centre xml markup representation
is not editable.

To be able to edit the xml markup representation of the .xsd schema do this:
1) Right mouse button on the schema and choose Open With... on the pop up menu.
2) A dialog such as the one below will appear -> In this dialog, choose XML Schema editor.

3) The .xsd schema will now display in the XML Schema Editor. This editor is more object based,
displaying each element in a box. New attributes can be added to the Invoice, Invoices and InvoiceItem
elements. The properties window, can also be used to edit individual attributes.

4) While in the editor, at the bottom choose the XML tab, as below.

5) Now the below schema can be edited directly.

Constructing BizTalk 2004 XML Messages (In an Orchestration) - Choices

2004-11-10T01:37:00Z

Before using a BizTalk XML message inside an Orchestration , it must first be
constructed. In fact ,this is just populating the message with XML. If there is an
attempt to use the message before it is constructed, the following error will appear when
building the BizTalk project -> use of unconstructed message 'MyMessageNameHere'.
Some messages come pre-constructed, such as messages that originate from a port, but there
will be times when a message needs to be constructed inside of an orchestration.

For example if there is a message inside an Orchestration called msgShippingInfo (that is unconstructed),
and is of type ShippingInfo.xsd and the xsd schema looks like the below:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://ConstructingXMLMessages.ShippingInfo" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://ConsltructingXMLMessages.ShippingInfo" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ShippingInfo">
    <xs:complexType>
      <xs:attribute name="ShipToAddress" type="xs:string" />
      <xs:attribute name="ShipToCity" type="xs:string" />
      <xs:attribute name="ShipToCountry" type="xs:string" />
    </xs:complexType>
</xs:element>
</xs:schema>

Then at some point, the message must be populated with some xml as below, before that message can
be used:

<ns0:ShippingInfo ShipToAddress="Some Address" ShipToCity="Some City" ShipToCountry="Some Country"
xmlns:ns0="http://ConstructingXMLMessages.ShippingInfo" />

Below are some options for constructing XML messages, inside of an Orchestration.

1) Create new message(s) from a existing message(s) using a BizTalk Map.
This is one of the more common methods. For example,
I need to map my incoming Purchase Order message to an outgoing
Invoice message. Also keep in mind that you can have multiple input messages and output messages
when mapping. For example within an Orchestration, there is an existing constructed Purchase Order and Shipping messages.
I can then use these two messages to create an Order message.

A simple example is as below:

If you have not done it before :
To create a map with multiple inputs and outputs, create the needed messages
in the Orchestration View. For example , in this case an Order message,
PurchaseOrder Message and the Shipping Message.
Drop a transform shape on your orchestration and then choose the source(input) message(s).
In this case there are two : PurchaseOrder message and Shipping message.
Choose the destination(output) message(s) : Order message.
In the Transform Configuration dialog box, choose -> new map. In this case
a new map is created that looks like the above (minus any of the links and functoids between source and
destination messages). These were put in later.

2) Assign one message to another.
At some point you may need a copy of another message. If msgOrderCopy is of the same type
as msgOrderOriginal, then the below assignment can be done.

Construct msgOrderCopy
{

msgOrderCopy = msgOrderOriginal;
//Once the new message is constructed, it can now be modified.
// For example if the schema for the order has distinguished fields,
// these can now be set. XPATH can also be used to set the data in (Some xpath examples are further down).
msgOrderCopy.OrderedBy = "Bob";

}

3) Create a message with the help of a System.XML.XMLDocument variable.

In the below example, the XML to populate the message is hardcoded inside the
expression shape. The XMLDocument variable is initially assigned to the hardcoded XML. The BizTalk message is
then assigned to the XMLDocument variable. The BizTalk message is now good to go.
This is a handy technique especially when the schemas/xml instance messages are small. If the schema
has distinguished fields, then these can then be used to set the attributes in the message with real data, otherwise XPATH
can be used to populate the ID and Name attributes of the below simple XML message. It is also
possible to alter the hardcoded XML with the real data, and then do the message assignments.

construct msgShipping
{
// Use the Variable varXMLDom to load in some XML
varXMLDom.LoadXml(@"<ns0:ShippingInfo ShipToAddress=""Some Address"" ShipToCity=""Some City"" ShipToCountry=""Some Country"" xmlns:ns0=""http://ConstructingXMLMessages.ShippingInfo"" />");
// Assign the BizTalk message to the XMLDom variable. Note this can be accomplished becuase a BizTalk message derives from an XMLDocument.
msgShipping = varXMLDom;
// If there are Distinguished Fields available, then
msgShipping.ShipToAddress = "1 Main Street";
msgShipping.ShipToCity = "Toronto";
msgShipping.ShipToCountry = "Canada";
// If there are no distinguished fields, then XPATH can be used to set the attributes in the message.
xpath(msgShipping , "//@ShipToAddress") = "1 Main Street";
xpath(msgShipping, "//@ShipToCity") = "Toronto";
xpath(msgShipping, "//@ShipToCountry") = "Canada";

}

4) Creating a more complex message using : System.XML.Document, System.XML.XMLNode,System.XML. XMLAttribute, Interim BizTalk Message
The below example, uses the same method as in 3) , but the message is more complicated.
A Looping shape is used to add the order items to an order message. The part of the orchestration that does this
is as below:

In the the First Expression Shape, Construct the initial Order Message (With just the Order Header), with the help of a XMLDocument variable
construct msgOrderFromAppending
{
   // Initially create the Order Header part of the Order Message
   varOrderXMLDom.LoadXml(@"<ns0:Order OrderedBy=""SomeBody"" TotalOrderAmount=""0"" xmlns:ns0=""http://ConstructingXMLMessages.Orders""></ns0:Order>");
   msgOrderFromAppending = varOrderXMLDom;

}

In the next expression shape add the order items. Note that in the Orchestration this expression
shape is in a loop, so there are multiple order items being added.

// Construct the Temp Order Message to Append the Node.
construct msgOrderFromAppendingTemp
{
   // First set the XMLDOM variable to the msgOrderFromAppending in the first shape.
   varOrderXMLDom = msgOrderFromAppending;
   // use CloneNode to make a fresh copy of msgOrderFromAppending
   varOrderXMLDom = (System.Xml.XmlDocument) varOrderXMLDom.CloneNode(true);
   // Create a new Order Item Node
   varXMLNodeOrderItemNode = varOrderXMLDom.CreateElement("OrderItems");
   // Create Amount Attribute
   varXMLAttribute = varOrderXMLDom.CreateAttribute("Amount");
   varXMLAttribute.InnerText = "0";
   varXMLNodeOrderItemNode.Attributes.Append(varXMLAttribute);
   // Create Description Attribute
   varXMLAttribute = varOrderXMLDom.CreateAttribute("Description");
   varXMLAttribute.InnerText = "A Description";
   varXMLNodeOrderItemNode.Attributes.Append(varXMLAttribute);
   // Create Qty Attribute
   varXMLAttribute = varOrderXMLDom.CreateAttribute("Qty");
   varXMLAttribute.InnerText = "0";
   varXMLNodeOrderItemNode.Attributes.Append(varXMLAttribute);
   // Now append the actual Order Item Node to the XML Instance
   varOrderXMLDom.FirstChild.AppendChild(varXMLNodeOrderItemNode);
   // Now actually construct the Temp message.
   msgOrderFromAppendingTemp = varOrderXMLDom;
   System.Diagnostics.Debug.WriteLine("The output is " + varOrderXMLDom.OuterXml);
}

// Now Set the Real Order Message From the Temp Message.
// This is so we can keep adding messages to the real message, without losing them.

construct msgOrderFromAppending
{
   msgOrderFromAppending = msgOrderFromAppendingTemp;
   // Set the Data in the newly added Order Item Node.

   // Now use XPAth to set the Data in the attributes,
   // NOTE: I could of also done this by setting the real data
   // on the XMLAttribute object -> varXMLAttribute.InnerText = "Description for Order Item : " + System.Convert.ToString(varOrderItemCount) ;

   strvarOrderItemCount = System.Convert.ToString(varOrderItemCount);
   xpath(msgOrderFromAppending,"//OrderItems[" + strvarOrderItemCount + "]//@Amount") = System m.Convert.ToString(varOrderItemCount) ;
   xpath(msgOrderFromAppending,"//OrderItems[" + strvarOrderItemCount + "]//@Qty") = System.Convert.ToString(varOrderItemCount) ;
   xpath(msgOrderFromAppending,"//OrderItems[" + strvarOrderItemCount + "]//@Description") = "Description for Order Item : " + System.Convert.ToString(varOrderItemCount) ;
}

5) Use a .Net helper class to create the message.

As in the above example , instead of using XMLDOM type code in the orchestration, a helper
.NET class could of been called in the orchestration to create the message.
An example helper class would look like the below, just returning an XMLDocument that could be assigned to
the message inside the orchestration.

public static XmlDocument GetXmlDocumentOrderTemplate()
{
// Code to create the message.

}

Now inside the orchestration, the code to call the .Net helper class to construct the message.

Construct myOrderMessage
{

myOrderMessage = HelperClass.GetXmlDocumentOrderTemplate

}

Also look here

Download the above examples , Here

BizTalk 2004 Map. Mapping two input messages into one output message , plus matching content from one message to another.

2004-11-01T13:03:00Z

I have had the problem of having a message such as a Order message, being delivered
as two separate messages. For example, the Order header Records delivered as one XML message,
and then all the Order Items that belong to Order header Records delivered as a separate XML message.

The two separate messages being delivered look something like below:

Order Headers:

<ns0:Orders Correlator="1" xmlns:ns0="http://IDREFTest.Orders">
<Order OrderID="6" OrderedBy="BillyBobJim" />
<Order OrderID="1" OrderedBy="Bob" />
<Order OrderID="2" OrderedBy="Bobby" />
<Order OrderID="3" OrderedBy="BobbyJohn" />
<Order OrderID="4" OrderedBy="BobbyNoItems" />
<Order OrderID="5" OrderedBy="BillyBob" />
<Order OrderID="0022" OrderedBy="BillyBobJim" />
</ns0:Orders>

Order Items:

<ns0:Order Correlator="1" xmlns:ns0="http://IDREFTest.OrderItems">
<OrderItems OrderID="1" Qty="1" />
<OrderItems OrderID="1" Qty="11" />
<OrderItems OrderID="1" Qty="111" />
<OrderItems OrderID="2" Qty="2" />
<OrderItems OrderID="2" Qty="22" />
<OrderItems OrderID="2" Qty="222" />
<OrderItems OrderID="3" Qty="3" />
<OrderItems OrderID="3" Qty="33" />
<OrderItems OrderID="3" Qty="333" />
<OrderItems OrderID="3" Qty="3333" />
<OrderItems OrderID="5" Qty="5" />
<OrderItems OrderID="5" Qty="1" />
<OrderItems OrderID="5" Qty="11" />
<OrderItems OrderID="5" Qty="111" />
<OrderItems OrderID="6" Qty="333" />
<OrderItems OrderID="6" Qty="3333" />
<OrderItems OrderID="0022" Qty="3" />
<OrderItems OrderID="0022" Qty="33" />
</ns0:Order>

My goal then was to create a final Order XML Message that looks like the below (combining
information from the two messages into one message, plus also matching the correct order items with
the correct order headers). I wanted to accomplish this by just using BTS maps and functoids
that are installed with BTS2004.

<ns0:Orders xmlns:ns0="http://IDREFTest.OrdersandOrderItems">
<Order OrderedBy="BillyBobJim" OrderID="6">
    <OrderItems OrderID="6" Qty="333" />
    <OrderItems OrderID="6" Qty="3333" />
</Order>
<Order OrderedBy="Bob" OrderID="1">
    <OrderItems OrderID="1" Qty="1" />
    <OrderItems OrderID="1" Qty="11" />
    <OrderItems OrderID="1" Qty="111" />
</Order>
    <Order OrderedBy="Bobby" OrderID="2">
    <OrderItems OrderID="2" Qty="2" />
    <OrderItems OrderID="2" Qty="22" />
    <OrderItems OrderID="2" Qty="222" />
</Order>
  <Order OrderedBy="BobbyJohn" OrderID="3">
     <OrderItems OrderID="3" Qty="3" />
     <OrderItems OrderID="3" Qty="33" />
     <OrderItems OrderID="3" Qty="333" />
     <OrderItems OrderID="3" Qty="3333" />
</Order>
<Order OrderedBy="BobbyNoItems" OrderID="4" />
<Order OrderedBy="BillyBob" OrderID="5">
     <OrderItems OrderID="5" Qty="5" />
     <OrderItems OrderID="5" Qty="1" />
     <OrderItems OrderID="5" Qty="11" />
     <OrderItems OrderID="5" Qty="111" />
</Order>
<Order OrderedBy="BillyBobJim" OrderID="0022">
     <OrderItems OrderID="0022" Qty="3" />
     <OrderItems OrderID="0022" Qty="33" />
</Order>
</ns0:Orders>

Therefore to accomplish this:

1) Created an Orchestration that contains a parallel convoy, that correlates on
the Correlator attribute on both the Orders.xsd and OrderItems.xsd properties.

2) Created a Map with two inputs (Order Header and Order Items) and one output (OrdersAndOrderItemsAll).
To create a map with multiple inputs and outputs, create the needed messages
in the Orchestration View. For example , in this case an OrdersOnly message,
OrderItemsOnly Message and the OrdersAndOrderItemsAll Message.
Drop a transform shape on your orchestration and then choose the source(input) message(s).
In this case there are two : OrdersOnly message and OrderItemsOnly message.
Choose the destination(output) message(s) : OrdersAndOrderItemsAll message.
In the Transform Configuration dialog box, choose -> new map. In this case
a new map is created that looks like the below (minus any of the links and functoids between source and
destination messages). These were put in later.

3) In the map as above, links and two looping functoids were used to create an interim message (OrdersAndOrderItemsAll)
All OrderHeader records will contain all the OrderItem records.

4) Create another map that looks like the below, using the OrdersAndOrderItemsAll message as the source and using a new
message OrdersAndOrderItems as the destination. The equal functoid is used to include only the correct orderitems with each
order header. This map creates the desired resultant xml instance.

Download the complete solution Here.

What I like about this solution, is that it works. What I don't like about this solution is the interim message, with each order header message
including all the order items. In my case the incoming OrderHeader message and OrderItem messages were not terribly large, therefore
the interim message was not huge. Of course there may be other ways to accomplish this, possibly using custom xslt.

Passing an XML instance to a Web Service Method via a BizTalk 2004 Orchestration Web Port -> Choices.

2004-10-30T23:40:00Z

Need to call a web method from a BizTalk 2004 Orchestration Web Port and pass an instance of XML to the web method.
Below are some options:

1) Pass the XML as a string.

For example your Web Method that you need to call from your orchestration might look something like this:

[WebMethod]
ProcessOrder (string myOrder)
{

System.XML.XMLDocument myOrderDom = new System.XML.XMLDocument()
myOrderDom.load(myOrder)
// Now have code to process the order
System.Xml.XmlNodeList nodeList = myOrderDom.GetElementsByTagName("OrderItems");
System.Diagnostics.Debug.WriteLine("The number of Order Items passed in with the XMLDocument is: " + nodeList.Count.ToString());
foreach(System.Xml.XmlNode orderItemNode in nodeList)
{
System.Diagnostics.Debug.WriteLine("The order item node is : " + orderItemNode.OuterXml);

}

In the Orchestration :
To create the XML string representation of the message in your orchestration:
a) In your orchestration create a variable of type System.XML.XMLDocument

System.XML.XMLDocument varXMLDOM

b) In an expression shape assign the XMLDocument type variable to the message that
you want to pass to the XMLDocument.

varXMLDOM = msgIncomingOrder;

c) Finally set the message that contains the string parameter that will be passed to the Web Method:

msgXMLStringRequest.XMLAsString = varXMLDOM.OuterXml;

2) Pass the XML as a System.XML.XMLDocument
For example your Web Method that you need to call from your Orchestration might look something like this:

[WebMethod]
ProcessOrder (system.XMl.XMLDocument myOrder)
{

System.Xml.XmlNodeList nodeList = myOrder.GetElementsByTagName("OrderItems");
System.Diagnostics.Debug.WriteLine("The number of Order Items passed in with the XMLDocument is: " + nodeList.Count.ToString());
foreach(System.Xml.XmlNode orderItemNode in nodeList)
{
System.Diagnostics.Debug.WriteLine("The order item node is : " + orderItemNode.OuterXml);

}

In the Orchestration :
To set the message that contains the XMLDocument parameter that will be passed to the Web Method , simply set the web message to the orchestration message :
msgXMLDomRequest.XMLAsXMLDocument = msgIncomingOrder;

3) Pass the XML as a Strongly Typed Class

Your order class (defined on the Web Service side) might look something like this :

[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://PassingXMLToWS.Orders")]
[System.Xml.Serialization.XmlRootAttribute(Namespace="http://PassingXMLToWS.Orders", IsNullable=false)]
public class Orders
{
[System.Xml.Serialization.XmlElementAttribute("OrderItems", Form=System.Xml.Schema.XmlSchemaForm.Unqualified)]
public OrdersOrderItems[] OrderItems;

[System.Xml.Serialization.XmlAttributeAttribute()]
public string OrderId;

[System.Xml.Serialization.XmlAttributeAttribute()]
public string TotalAmount;
}

[System.Xml.Serialization.XmlTypeAttribute(Namespace="http://PassingXMLToWS.Orders")]
public class OrdersOrderItems
{

[System.Xml.Serialization.XmlAttributeAttribute()]
public string OrderItemId;

[System.Xml.Serialization.XmlAttributeAttribute()]
public string ItemAmount;
}

Note: I created the above class by using the xsd utility that is installed with VS.Net.
a) I created an xsd BizTalk Schema, and called it Orders.xsd
b) I then generated the above class, by opening up the Visual Studio Command Prompt and then
typing in the following :
c:\passxmltowebservice> xsd Orders.xsd /c /l:cs
c) The xsd utility then created a class for me called, Orders.cs, which I then copied over to
my web service project.

Your Web method that accepts an Order class might look like the below:

[WebMethod]
public void AcceptOrderClass(Orders myOrders)
{
System.Diagnostics.Debug.WriteLine("In AcceptOrderClass");
System.Diagnostics.Debug.WriteLine("The Id of the Order passed in is: " + myOrders.OrderId);
foreach (OrdersOrderItems orderItem in myOrders.OrderItems)
{
System.Diagnostics.Debug.WriteLine("The Id of the Order Item is " + orderItem.OrderItemId);
}
}

In your BizTalk project, when the Web Reference is added,
an XSD schema will be created that represents the Orders class (Called Reference.xsd).
Inside your Orchestration you create a message of type Reference.xsd and then construct this message, just like any other message.
When your message reaches the web service side it will automatically be converted to an instance of -> public class Orders

Here is a sample of each.

Passing a Binary as a parameter to a Web Method from a BizTalk 2004 Orchestration Web Port

2004-10-21T12:48:00Z

Christof Claessens has an excellent entry on how to pass a binary object
to a Send Port, such as a file send port. The binary that is saved by the
send port using his method will automatically be saved to its native format when written out to a file. For
example : c:\myBTS2004SendLocation\MyExcelFile.xls

But using the above will not necessarily work when calling a web method
via a BizTalk Orchestration Web Port as below:

[WebMethod]
ProcessBinary (System.XML.XMLDocument binaryDocument)
{

}

The Web Method is expecting well formed XML that is not present in the binary document.
When the Web Service is called from the orchestration, the Web Service will throw an error.

A possible solution is to convert the binary document to an array Of bytes
and then call a web method via a BizTalk Orchestration Web Port as below:

[WebMethod]
ProcessBinary (System.Byte[] binaryDocument)
{

}

But when you try to add the Web Reference to a BizTalk project, you will get an error such as:
Could not generate BizTalk files. An (WebMethodName) operation parameter
or return type is an Array. Array types are not supported.

A very simple solution is to convert the binary document to an array of bytes,
then convert the array of bytes to a string using something like the code below:

byte[] myBytes;
FileStream fs = new FileStream(@"C:\myExcelDoc.xls",FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
myBytes = r.ReadBytes(Convert.ToInt32(r.BaseStream.Length));
// Now convert the array of bytes to a string.
string myBase64String;
myBase64String = Convert.ToBase64String(myArrayofBytes);

The above string can now be passed to the below web method via a
BizTalk Orchestration Web Port as below:

[WebMethod]
ProcessBinary (System.String binaryDocument)
{

byte[] arrayOfBytes;
arrayOfBytes = Convert.FromBase64String(binaryDocument);
FileStream fs = new FileStream(@“c:\fileout.xls“, FileMode.CreateNew);
BinaryWriter w = new BinaryWriter(fs);
w.Write(arrayOfBytes);

}

Here download the complete solution.

BizTalk 2004 Presentation

2004-06-14T02:12:00Z

I am doing a BizTalk 2004 presentation on Wed. June 16 at the Metro Toronto .NET User Group. So if you can make it please come.

http://www.metrotorontoug.com/User+Group+Events/97.aspx

DebugView/OutputDebugString Lives in .NET

2004-05-23T20:20:00Z

Back in my COM+ VB6 days I always had a tough time to
debug into my VB6 components that were running in a dllhost (COM+ Server Application
environment). Basically when the call jumped into the component that was hosted
by COM+ , it would sometimes do funny things like hang the VB6 debug session.

So one method to help debug, was to use an api in the code called OutputDebugString in addition with a little application called DBWIN32.exe, to capture and display the OutputDebugString calls in the code.
In a nutshell DBWIN32 is a little window that captures and displays any Win32 debug output.

So in the VB6 COM+ component code , basically when I needed to debug something I would put in the
code:

OutputDebugString("I am debugging")

Then if the DBWIN32 window was up and running it would append to it's output:

July 7 2003 10:02:03 I am debugging

Does this work in .NET? -> Yes. But the nice thing about it is that you do not have to
use OutputDebugString API calls to add to the Win32 debug output.

Instead all I have to do is call something that I am sure you are familiar with such as:

System.Diagnostics.Debug.WriteLine("I am debugging"); or
System.Diagnostics.Trace.WriteLine("I am Tracing");

To try this out (if you haven't already, go ahead do it, you might find this useful later!) go to the following
site and download debugview :

http://www.sysinternals.com/

This is not a full blown install, but only a zip file with a little exe in it. Once you have unzipped Dbgview.exe:

1) Run Dbgview.exe. The little DebugView window will be launched.
2) Create a new .NET windows or .NET ASP application.
3) Somewhere in the code put (make sure the code is called on startup)

System.Diagnostics.Debug.WriteLine("I am debugging");
System.Diagnostics.Trace.WriteLine("I am Tracing");

4) Compile it, (but make sure that it is the debug version).
5) Run the .exe (Windows) or navigate to the URL (ASP.Net) to run the debug version.
(Note if you are in VS.NET go to menu choice Debug/Start Without Debugging or
the VS.NET Output Window will grab the debug Win32 output)
6) In the Dbgview.exe output window you should see something like:

20 3:35:37 [3476] I am debugging
20 3:35:38 [3476] I am Tracing

Why is this useful? :

1) If you have not set up a listener for your Debug or Trace output, you will
not see the debug or trace output if your are running outside the VS.NET environment.
2) This is extremely useful for Biztalk 2004 debugging , but go here to
see why :
http://blogs.msdn.com/darrenj/archive/2004/04/29/123254.aspx
3) If you are bored you can always see what else is logging Win32 debug output.
4) For other Win32 debug output listeners and discussions go here :
http://weblogs.asp.net/cszurgot/archive/2003/05/21/7368.aspx

Biztalk Server 2004 / InfoPath,Excel / Sharepoint.

2004-05-23T17:50:00Z

In the last month or so I have been using the Sharepoint Adapter for Biztalk 2004.
http://www.gotdotnet.com/Community/Workspaces/viewuploads.aspx?id=0d1aa85c-cf8d-497e-84f4-3ffec8db115f

What the Sharepoint Adapter does in a nutshell is allow Biztalk
Server to poll for documents posted to a Sharepoint Document Library, grab them
and process them. Biztalk Server can also post a document up to a Sharepoint Document Library.

Why should anyone care about this?

1) Sharepoint Web Sites are a snap to set up.
For example : I can easily set up a Sharepoint Site for employees to
submit vacation requests or timesheets.

2) InfoPath and Excel (part of Office 2003) easily integrate with XML Schemas.
For example: The Vacation Request or timesheets that the employee must fill in
can be easily be created using InfoPath or Excel using an XML Schema as a datasource.

3) InfoPath Scares me.
Not because it's difficult to use , but because it is a snap to design a powerful functional
InfoPath form using an existing XML Schema or a database as a datasource.
With an InfoPath form, you can connect to a Web Service to populate a dropdown,
do validations, and very easily create a smart client interface with minimal coding. InfoPath has a whole host of
controls such as DropDowns, Calendars, Buttons , Repeating Tables Master Detail Tables and many
more. When the InfoPath document is saved it will automatically populate the XML Schema(s) that it
is bound to.

Just to give you a high level workflow that incorporates
Biztalk 2004/ InfoPath / and Sharepoint.

1) The user will go into the Sharepoint Web Site.
Navigate to the Vacation Request Document Library
2) Hit the New Vacation Request form button.
3) Fill out the form.
4) Hit Save.
5) The InfoPath XML document will be saved to the Sharepoint Document Libray.
6) Biztalk will pick up the XML document from Sharepoint and process it.
7) Optionally Biztalk could post the same or another XML document back up to Sharepoint.

Scott Woodgate ( http://blogs.msdn.com/scottwoo ) was absolutely correct in saying that -> What Outlook is to Exchange Server ,
InfoPath is to Biztalk 2004. InfoPath provides a human interface to Biztalk Server.
As for Sharepoint, it is and will continue to be a very popular product.
Mainly because of its powerful configuration properties and I love it because it
integrates seamlessly with InfoPath and Biztalk 2004.

Setting focus back to the control that caused the PostBack in an ASP.NET Form

2004-05-06T20:15:00Z

SmartNavigation can be set to true on your ASP.NET webform so that when postbacks occur , the page when rendered back to the browser, will navigate back to the control that caused the postback.

But SmartNavigation can be problematic especially when dynamically loading controls onto your webform.

Therefore if you have SmartNavigation turned off = false, below is a piece of code that you can call from your webform that will add javascript to your page, to automatically navigate back to the control that originally caused the postback.

I tested the code against IE6 and Netscape 7.1.

///

  /// This method will take passed webPage, and find the control that caused the postback. If it finds
  /// one it will set javascript on the page to set focus to that control
  ///

  /// The web page
  public void SetFocusPostBackControl(System.Web.UI.Page webPage)
  {
   string[] ctlPostBack;

   ctlPostBack = webPage.Page.Request.Form.GetValues("__EVENTTARGET");
   if (ctlPostBack != null && ctlPostBack.Length > 0)
   {
    string ctlUniqueId;
    ctlUniqueId = ctlPostBack[0];
    System.Web.UI.Control findControl = webPage.Page.FindControl(ctlUniqueId);
    if ((findControl != null) &&
     (findControl is DropDownList ||
     findControl is TextBox ||
     findControl is RadioButton ||
     findControl is RadioButtonList))
    {
     string ctlClientId;
     ctlClientId = findControl.ClientID;
     string jScript;
     jScript = "<SCRIPT language=\"javascript\"> document.getElementById('" + ctlClientId + "').focus(); document.getElementById('"
     + ctlClientId + "').scrollIntoView(true) </SCRIPT>";;

     webPage.Page.RegisterStartupScript("focus",jScript );

    }
   }
  }

Developing an ASP.NET Framework From a Windows Forms .NET Perspective.

2004-05-02T18:07:00Z

A couple of months ago, I had to quickly develop an ASP.NET framework.
I incorporated parts of a Windows .NET framework that I had previously worked on. The basic
premise being that a Windows .NET Form and an ASP.NET WebForm are both event driven
and have controls such as buttons and dropdowns.

There were two basic steps in developing this ASP.NET framework.

1) Creating Ancestor code behind pages for all the code behind pages used in the project:

a) public class WebFormBase : System.Web.UI.Page -> For the Web Forms
b) public class WebUserControlBase : System.Web.UI.UserControl -> For the Web User Controls

When a Webform or Web UserControl needs to be created, their code behinds inherit from the custom base class:

public class OrderWebForm : WebFormBase
public class ProductWebuserControl : WebUserControlBase

I think the above is a pretty standard thing to do.

The only thing I really did a little bit differently was to raise more events up to the descendent pages such as:

Loading
Load

Init
Initing

PreRendering
PreRender

etc.

In this way the descendent code has a chance to do some work before and after the code in ancestor.

2) All server side controls used on a WebForm or Web UserControl are inherited from the standard Microsoft Web Controls, or a third party control:

public class MyWebButton : System.Web.UI.WebControls.Button
public class MyWebMenu : Infragistics.Web.UI.UltraWebMenu
etc. etc. As you know there are many more. Hyperlink, Label, DataList etc.

For this framework thats pretty well it, in a nutshell.

This has really paid off for the future development work, because server Side controls can now implement custom interfaces,
such as :
ITranslation
IDisable

Then in the base classes for the code behind for the WebFormBase or the WebUserControlBase, all the code is there to handle translation of pages to French or English or to disable or enable or disable controls automatically depending on a custom property put on the Web page called Enabled. Other things that have been built into the framework are resource file management, session management, navigation management and a custom help button that launches another browser with some help.