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

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)

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

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

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 btsMessage)
{
  string result;
  StreamReader reader = new StreamReader((Stream)btsMessage[0].RetrieveAs(typeof(Stream)));
  result = reader.ReadToEnd();
  reader.Close();
  return result;
}

Or:

/// <summary>
/// Pass in a BizTalk Message Part and will return a string
/// </summary>
/// <param name="btsMessage">The BizTalk message part to get the string from</param>
/// <returns>The string from BTS Part</returns>
 public static string GetStringFromBTSMessagePart(XLANGPart btsPart)
 {
   string result;
   StreamReader reader = new StreamReader((Stream)btsPart.RetrieveAs(typeof(Stream)));
   result = reader.ReadToEnd();
   reader.Close();
   return result;
 }

Therefore in an orchestration expression shape, the following code will return the string representation of a BizTalk message using the helper method:

// Below will return the string from a BizTalk Orchestration message called msgBTS.
// strFromBTSMsg is a string variable declared in the orchestration
strFromBTSMsg = BTSClassHelper.MessageHelper.GetStringFromBTSMsgPart(msgBTS);

An alternative to using the above helper method is to create a System.Xml.XmlDocument variable in the orchestration,
then assign the BizTalk message to the variable. Then as below, the OuterXml can be extracted from the XmlDocument:

varDom = msgBTS;
strFromBTSMsg = varDom.OuterXML;

The downside to using the XmlDocument variable, is that the whole message will be loaded into memory and an extra XmlDocument variable must be created in the orchestration.

The second helper method (CreateBTSMsgFromString) will construct a BizTalk message from a string. This method is a copy from this post, with a few minor modifications.
The referenced post describes how a binary message in an orchestration can be constructed programmatically.
Just as a side note, remember a BizTalk message can be:
a) xml
b) Anything else
ie (Word Document, PDF, excel spreadsheet, jpg, flat file,  etc. etc. etc.)

The helper classes can be downloaded at the end of this blog entry, so I will not repeat the code for the helper method CreateBTSMsgFromString.

Therefore in an orchestration expression shape, the following code will construct a BizTalk message from a string using the helper method:

// strFromBTSMsg is a string variable declared in the orchestration
// msgBTS is the BizTalk message to be constructed
// the last parameter is the encoding to apply to the message
BTSClassHelper.MessageHelper.CreateBTSMsgFromString(strFromBTSMsg,
                                                    msgBTS,
                                                    BTSClassHelper.MessageFactoryEncoding.UTF8);  
 

An alternative to using the above helper method is to create a System.Xml.XmlDocument variable in the orchestration,
Then as below, load in the string and then assign a BizTalk message to the XmlDocument

strFromBTSMsg = "<Message><FirstName>Bob</FirstName></Message>"
varDom.LoadXml(strFromBTSMsg);
msgBTS = varDom;

The downside to the above approach compared to the helper method is the extra overhead of the System.Xml.XmlDocument variable.

You can download the helper classes and a quick example on how to use them Here (Zip File)
Read this before trying to run

 

Turning an Immutable Message in BizTalk into a Mutable message

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.
 

MVP Summit 2007

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.

 

Correlation Demo

A demo that attempts to illustrate the concept of correlation.

Failed Message Routing and Failed Orchestration Routing in BizTalk 2006

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

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

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]

GO

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE Proc [dbo].[Map_ApplicantHierarchical_To_ApplicantFlat]

 

-- Parameter to Accept AllApplicantInfo XML message

-- from BizTalk.

@ApplicantXML xml

 

 

As

 

/*

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