Introduction
BizTalk Server can be used to integrate enterprise applications and business processes. It provides a framework for message routing. The integration of some legacy systems with BizTalk Server brings a challenge. In such cases, components such as Preprocessors, parsers, and Application Integration Components (AICs) can be developed to bridge the gap between BizTalk Server and the legacy systems. This article provides detailed information about integrating an Excel spreadsheet with BizTalk Server.
BizTalk Server
BizTalk Server allows you to perform the integration of enterprise applications using XML messages. The messaging applications on BizTalk Server are developed by connecting channels to ports. The channels represent source applications or organizations and ports represent the destination applications or organizations. These messaging elements can be combined into complex interactions using the BizTalk Orchestration that provides messaging schedules. XML is used as a standard interface in BizTalk for all message routing and transformations.
Application Integration
BizTalk Server can connect the enterprise applications that use XML as a native messaging interface. The BizTalk Editor allows developers to define specifications to convert flat files and EDI documents to XML. Integrating the legacy systems with a BizTalk system can pose a challenge because many existing production systems do not support XML and BizTalk messages. This can be addressed by extending BizTalk processing with COM interfaces.
Excel
The Excel spreadsheet contains apartment details as follows:
Type | 1 bed | 2 bed | 3 bed | 4 bed |
Price | 105000 | 155000 | 205000 | 255000 |
The table’s structure to store the above data can be given as follows:
create table apartment ( type CHAR(10), price INTEGER )
Preprocessor
The Receive Function in BizTalk Server is a routine that can poll a file location or a message queue for the arrival of an incoming file or message. The Receive Function is capable of invoking a custom Preprocessor to read the incoming legacy data and convert it to XML. The Preprocessor must implement an IBTSCustomProcess interface, which includes an Execute method that is invoked when data is being processed. The Preprocessor component for Excel can be written in C# as follows:
void IBTSCustomProcess.Execute(object vDataIn, int nCodePageIn, bool bIsFilePath, ref object nCodePageOut, ref object vDataOut) { nCodePageOut = nCodePageIn; string sConn = null; string sData = null; OleDbCommand oCmd = null; OleDbConnection oConn = null; OleDbDataReader oReader = null; try { // Configure database connection sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=apartment.xls"; oConn = new OleDbConnection(); oConn.Open(); // Set the command properties oCmd.Connection = oConn; oCmd.CommandText = "SELECT type, price from [Sheet1$]"; // Read the spreadsheet data oReader = oCmd.ExecuteReader(); While (oReader.Read) { sData = sData & "<Apartment " + oReader.GetName(0) + "="" + oReader.GetString(0) + "" " + oReader.GetName(1) + "="" + oReader.GetInt32(1) + ""/>"; } // Close data connection oReader.Close(); oConn.Close(); } catch(Exception ex) { throw new Exception("Unable to Execute", ex); } // Return XML data to BizTalk sData = "<Data>" + sData + "</Data>"; vDataOut = sData; }
The XML message that is submitted to BizTalk Server will be as follows:
<Data> <Apartment type="1 bed" price="105000"/> <Apartment type="2 bed" price="155000"/> <Apartment type="3 bed" price="205000"/> <Apartment type="4 bed" price="255000"/> </Data>
Application Integration Component (AIC)
BizTalk Server delivers the data to its destinations by using a Port, which is associated with a transport protocol. It supports many transport mechanisms for delivering the data, including file, e-mail, MSMQ, HTTP, and AIC. There are two types of AIC components, known as Lightweight and Pipeline. The former component implements the IBTSAppIntegration interface, and the later one implements the IPipelineComponent interface. The AIC component can be written in C# as follows:
public string ProcessMessage(string xml) { string sConn = null; string sCols = null; string sVals = null; string sQry = null; XmlDocument oDoc = null; XmlNodeList oList = null; OleDbCommand oCmd = null; OleDbConnection oConn = null; try { // Configure database connection sConn = "provider=sqloledb;Database=APT;uid=sa;pwd="; oConn = new OleDbConnection(); oConn.Open(); // Load XML oDoc = new XmlDocument(); oDoc.LoadXml(xml); // Get data values XmlElement root = doc.DocumentElement; oList = root.GetElementsByTagName("Apartment"); IEnumerator iEnum = oList.GetEnumerator(); // Loop over XmlNodeList using the enumerator while (iEnum.MoveNext()) { XmlNode oNode = (XmlNode) iEnum.Current; for(int i=0; i < oNode.Attributes.Count; i++) { XmlAttribute oAttr = title.Attributes[i]; if (i = oNode.Attributes.Count-1) { sCols = sCols + oAttr.Name; sVals = sVals + "'" + oAttr.Value + "'"; } else { sCols = sCols + oAttr.Name + ","; sVals = sVals + "'" + oAttr.Value + "',"; } } } // Build query sQry = "Insert into apartment (" + sCols + ") values (" + sVals + ")"; // Set the command properties. oCmd.Connection = oConn; oCmd.CommandText = sQry; // Execute query oCmd.ExecuteQuery(); // Close data connection oConn.Close(); } catch(Exception ex) { throw new Exception("Unable to ProcessMessage", ex); } return "AIC completed successfully"; }
Configuration
- Create an input Excel spreadsheet.
- Create a target SQL database and tables.
- Using BizTalk Messaging Manager, create the document definition.
- Using BizTalk Messaging Manager, create source and destination organizations.
- Using BizTalk Messaging Manager, create a port that uses the custom AIC.
- Using BizTalk Messaging Manager, create a channel that will send messages to the port.
- Using BizTalk Server Administration, create a File Receive Function that will use the custom preprocessor.
Conclusion
BizTalk Server is used to connect enterprise applications and business processes by using its messaging elements and orchestration schedules. BizTalk messaging is based on document routing using XML, but this will not restrict the incorporation of other legacy data formats into BizTalk Server.