Legacy Integration with BizTalk Server

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

  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.

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.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read