Robust Error Handling for BizTalk Solutions Presentation

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.

Robust Error Handling for BizTalk Solutions.zip

Powerpoint from presentation

BTSErrorHandlingSolution.zip

From Robust Error Handing in BizTalk Presentation at SOA

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.

rfid_phidget_tags_reader

rfid_phidget_tags_reader

rfid_phidgetVMWare

rfid_phidgetVMWare

rfid_phidgetSampleScan

rfid_phidgetSampleScan

rfid_phidget_providererror

rfid_phidget_providererror

rfid_phidgetProviderWS

rfid_phidgetProviderWS