ODBC File Decoder Pipeline Component

You may already know my BizTalk Pipeline Components Extensions Utility Pack project available on GitHub for those who follow me. The project is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will extend BizTalk’s out-of-the-box pipeline capabilities.

This month my team and I update this project with another new component: ODBC File Decoder Pipeline Component.

ODBC File Decoder Pipeline Component

ODBC File Decoder Pipeline Component is, as the name mentioned, a decode component that you can use in a receive pipeline to process DBF or Excel files. Still, it can be possible to process other ODBC types (maybe requiring minor adjustments). The component uses basic ADO.NET to parse the incoming DBF or Excel files into an XML document.

If consuming DBF files is not a typical scenario, we can’t say the same for Excel files. Yet, we often find these requirements, and there isn’t any out-of-the-box way to process these files.

Honestly, I don’t know the original creator of this custom component. I came across this old project that I found interesting while organizing my hard drives. However, when I tested it in BizTalk Server 2020, it wasn’t working correctly, so my team and I improved and organized the structure of the code of this component to work as expected.

How does this component work?

If we take has an example and Excel File (.xls) that has a table with:

  • FirstName
  • LastName
  • Address
  • Position

We can use the ODBC File Decoder Pipeline Component to process these documents. First, we need to create a custom pipeline component and add this component to the decode stage. Once we publish this pipeline, we can configure it as follows to be able to process these types of Excel documents:

  • ConnectionString: ODBC Connection String
    • For Excel documents: Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;
    • For DBF: Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;
  • DataNodeName: Rows node name for the generated XML message
    • For example: Line
  • Filter: Filter for Select Statement
    • Leave it empty
  • NameSpace: Namespace for the generated XML message
    • For example: http://ODBCTest.com
  • RootNodeName: Root node name for the generated XML message
    • For example: TesteXMLResult
  • SqlStatement: Select Statement to Read ODBC Files
    • For example: SELECT * FROM [Sheet1$]
  • TempDropFolderLocation: Support temp folder for processing the ODBC Files
    • For example: c:\Temp\odbcfiles
  • TypeToProcess: Type of file being Processed
    • 0 to process Excel
    • 1 to process DBF

The outcome result will be an Excel similar to this:

<?xml version="1.0" encoding="utf-8"?><ns0:TesteXMLResult xmlns:ns0="http://ODBCTest.com">
  <Line>
    <FirstName>Fred</FirstName>
    <LastName>Black</LastName>
    <Address>187 Main Street</Address>
    <Position>Sales Lead</Position>
  </Line>
  <Line>
    <FirstName>John</FirstName>
    <LastName>Smith</LastName>
    <Address>182 Front Street</Address>
    <Position>Marketing</Position>
  </Line>
  <Line>
    <FirstName>Sally</FirstName>
    <LastName>White</LastName>
    <Address>183 Main Street</Address>
    <Position>Marketing</Position>
  </Line>
</ns0:TesteXMLResult>

Does it work with Xlsx files?

Honestly, I didn’t try it yet. I didn’t have that requirement, and I only remember this scenario now that I’m writing this post, but it should be able to process it. The only thing I know is that we need to use a different connection string, something similar to this:

  • Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES”;
  • or Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES;IMEX=1″;

Download

THIS COMPONENT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

You can download ODBC File Decoder Pipeline Component from GitHub here:

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published.

BizTalk360
BizTalk Server

Over 650+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Operate efficiently with enterprise-grade Azure monitoring,
tracing, remediation & governance in one platform

Learn More

Back to Top