by community-syndication | Jun 15, 2007 | BizTalk Community Blogs via Syndication
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.
by community-syndication | Jun 14, 2007 | BizTalk Community Blogs via Syndication
I am sure most of you (not talking about the BizTalk gurus out there J) have worked with some of the open source utilities available for BizTalk such as the BizTalk Adapter Wizard or the BizTalk Server Pipeline Component Wizard or BizUnit. However what some of you may not know is the wealth of other open source utilities for BizTalk available on CodePlex.
Here are some of the interesting ones and their descriptions as found on CodePlex :
SDC Tasks Library – . The SDC Tasks are a collection of MSBuild tasks designed to make your life easier. You can use these tasks in your own MSBuild projects. You can use them stand alone and, if all else fails, you can use them as sample code.
BizUnit – Framework for Automated Testing of Distributed Systems – BizUnit enables automated tests to be rapidly developed. BizUnit is a flexible and extensible declarative test framework targeted that rapidly enables the automated testing of distributed systems, for example it is widely used to test BizTalk solutions. BizUnit is fully extensible. Its approach is to enable test cases to be constructed from generic reusable test steps, test cases are defined in XML which allows them to be auto-generated and also enables the ’fixing up’ of Url’s for different environments, e.g. test, staging and production environments. Defining test cases in XML enables test cases to be auto-generated.
BizTalk Server Pattern Wizard – The BizTalk Pattern Wizard is an extensible tool to help you capture, share, and reuse your orchestration best practices. By using the BizTalk Pattern Wizard, you can capture a best practice, turn it into a generalized and configurable pattern, and share that pattern with the rest of your team or the entire BizTalk community. The wizard comes with over a dozen patterns ready for implementation in your next project.
WCF Adapter for BizTalk Server 2006
BizTalk Adapter Wizard for BizTalk Server 2006 – The BizTalk Adapter Wizard for BizTalk Server 2006 is a Visual Studio 2005 project wizard which creates all of the framework code for your custom BizTalk adapter. The adapter wizard is accessible from the Visual Studio menu: File – New – Project – BizTalk projects.
BizTalk Server 2006 Documenter – Creates compiled help files for a given BTS 2006 installation. This tool can be run on an ad-hoc basis using the UI or from the command line as a post build/deploy task to create a compiled help file describing a BTS 2006 installation. It will compile: BTS Host configuration, Send/Receive port configuration, Orchestration diagrams, Schema and Map content, Pipeline process flow, Adapter configuration, Rule engine vocabularies and policies, More and publish them as compiled help files. Optionally you can embed custom HTML content and custom descriptions for all BTS artifacts to produce a more customized look and feel to the CHM output
BizTalk Server 2006 Orchestration Profiler – Creates CHM report files illustrating the level of coverage for specified BizTalk orchestrations. This tool can be run to gain a consolidated view of orchestration tracking data for a specified period of time to help developers get an idea of how their orchestrations are being processed and how much coverage they are getting in their testing. In addition to simple coverage information the data presented helps to identify latency and code path exceptions by highlighting long running and error prone orchestration shapes.
PowerShell BizTalk Provider – A full PowerShell provider for exposing BizTalk Server as a filesystem. Administer your BizTalk installation. List all applications / orchestrations / Schemas. Stop an application, enlist an orchestration … Use the full power of the shell to script away the pain of GUI based mass-management.
MapCop – A program for testing BizTalk map files (.BTM) for a set of best practices.
BizTalk Instance Controller – Limit the number of instances of any BizTalk Service.
Have Fun!
by community-syndication | Jun 13, 2007 | BizTalk Community Blogs via Syndication
A site called Innovation Tools (which I recommend) published in September 2006 the results of a survey on mind mapping software (in PDF). MindManager is the clear leader, with ~70% usage, with the open source FreeMind at ~10% (which I tried before prefering MindManager).
This survey is especially interesting when you see the diversity of topics people use this kind of apps for: to-do lists, preparing presentations, taking notes, solve problems, plan projects, decision making, etc. According to the survey, the single most important benefit of using mind mappers is “Clarity of Thinking“. This is very true, and you can get results very quickly. A simple map can take me ~15mins to create, and usually the result can be used directly in your work (for example, for the titles of a PowerPoint presentations, or the chapters of a word document, or even a database structure or class diagram).
