Legacy Integration with BizTalk Server


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.


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


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;

      // Configure database connection
      sConn = "Provider=Microsoft.Jet.OLEDB.4.0;
               Data Source=apartment.xls";
      oConn = new OleDbConnection();

      // 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
   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:

<Apartment type="1 bed" price="105000"/>
<Apartment type="2 bed" price="155000"/>
<Apartment type="3 bed" price="205000"/>
<Apartment type="4 bed" price="255000"/>

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;

      // Configure database connection
      sConn = "provider=sqloledb;Database=APT;uid=sa;pwd=";
      oConn = new OleDbConnection();

      // Load XML
      oDoc = new XmlDocument();

      // 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 + "'";
               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

   // Close data connection

   catch(Exception ex)
      throw new Exception("Unable to ProcessMessage", ex);

   return "AIC completed successfully";


  1. Create an input Excel spreadsheet.
  2. Create a target SQL database and tables.
  3. Using BizTalk Messaging Manager, create the document definition.
  4. Using BizTalk Messaging Manager, create source and destination organizations.
  5. Using BizTalk Messaging Manager, create a port that uses the custom AIC.
  6. Using BizTalk Messaging Manager, create a channel that will send messages to the port.
  7. Using BizTalk Server Administration, create a File Receive Function that will use the custom preprocessor.


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.

About the Author

Syed Hameed

The author is working for Accenture. He has more than 6 years of experience in Microsoft solution design and development. Some of his major clients are IBM, Caterpillar Inc, Ford Motor Company, Borg warner Automatives, Litton Loan Servicing, Barwil Shipping Corporation, Marks and Spencer.


  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: May 6, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT While you likely have very good reasons for remaining on WinXP after end of support -- an estimated 20-30% of worldwide devices still are -- the bottom line is your security risk is now significant. In the absence of security patches, attackers will certainly turn their attention to this new opportunity. Join Lumension Vice President Paul Zimski in this one-hour webcast to discuss risk and, more importantly, 5 pragmatic risk mitigation techniques …

  • Live Event Date: April 22, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Database professionals — whether developers or DBAs — can often save valuable time by learning to get the most from their new or existing productivity tools. Whether you're responsible for managing database projects, performing database health checks and reporting, analyzing code, or measuring software engineering metrics, it's likely you're not taking advantage of some of the lesser-known features of Toad from Dell. Attend this live …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds