Building Biztalk Adapters
with DbToXml

On this page we will show you fast and eficient way of building integration solutions with DbToXml. Here I assume that you already have some experience with Biztalk, COM components and you already downloaded and installed DbToXml.

Most of current business integration problems are based on keeping data integrity between number of different systems, when data is changed in an origination database this changes has to be promoted to the different destinations.

We have been using Microsoft Biztalk server in several integration projects. And in almost every situation it requires building Application Integration Components (AIC). You can always build your own adapter. Ofcourse, if the vendor application is well known, you can try to buy existing one from many of the biztalk adapter providers. The only problem with those they not always cover all your business requirements.

Asume you have unique vendors and you forced to build adapters to extract and upload data to the different data sources. Two most often used AIC types are pull adapters and push adapters. The pull adapters are implemented to get data from the data source and the push adapters are used to submit information to another data sourrce.

The easiest way to create a pull adapter is actualy force the database to return data to you, when some event happened: the data was updated or new data was inserted to the database. What this actualy means, you will need a trigger that stores update/insert information to the xml file. Biztalk is configured to poll some folder and as soon as the file arrive it will trigger processing. The xml file will be validated and passed to the channel, channel will map the Xml to the output database layout and pass it to the port. The port has to be connected to the push AIC, which actualy inserts data to the output database.
Click to view big diagram

Good. Here is how DbToXml will save you a lot of efforts in this process. First of all, you point DbToXml to the origination database, then select desired table and click the Generate button. Then go to the SQL tab of DbToXml and grab ready to use Xml trigger, you will only need to update destination folder to output the xml. OK, you have installed trigger and confirmed that it generates the Xml for you. Now you need to check the Biztalk Schema tab of DbToXml. Yes there is ready to use Biztalk schema for this table. All you need to do is run Biztalk Editor import this schema and save it to the WebDav.

Note: Even if I like Java much much more then VB, from my personal experience I would not recommend to use Java with Microsoft solutions and vice versa. If you want to live with Java, chose another platform. WebMethods for example is my prefered choice for B2B solutions in Java.

OK. So check the VB tab of DbToXml, you have ready to use COM component, that can take Xml as input for one of the methods and insert it to the output database. All you have to do is implement AIC as simple as this, assume you called the component OutTable:

Implements IBTSAppIntegration   Private Function IBTSAppIntegration_ProcessMessage _    (ByVal bstrDocument As String) As String Dim objOutTable As OutTable On Error GoTo EXCEPTION     Set objOutTable = New OutTable       objOutTable.setXML bstrDocument     objOutTable.Insert         Exit Function   EXCEPTION:     Err.Raise Err.Number, "AIC:IBTSAppIntegration_ProcessMessage", _               Err.Description End Function

You may use the .Net version as well. The DbToXml generates C# and VB.NET clasees for you. To create the COM object from your .Net class you will need to inherit from the ServicedComponent assign the strong name to the AIC. Check the Microsoft Biztalk Server Toolkit for .NET for more information The Adapter code in C# may look as simple as this:

#region IBTSAppIntegration Interface Implementation /// <summary> /// Main mehtos of the AIC will be called by the Port. /// </summary> /// <param name="xml">Xml Message</param> /// <returns>Processed Xml Message</returns> public string ProcessMessage(string xml) {     try   {            dsOutTable.setXML(xml);            dsOutTable.Insert();   }   catch(Exception ex)   {     throw new Exception("Unable to Process Message", ex);   }   return xml; } #endregion

Compile the AIC and the OutTable component to the dll and register it in MTS.

We are almost there.

You just need to go to the Biztalk Schema tab again and save the destination table schema to the WebDav using Biztalk editor, exactly the same way you did first time. Now you are ready to create a map between this 2 Xml schemas.

Open Biztalk Mapper, load the schemas, you just created, origination first and destination second, map corresponding fields and save it to the WebDav.

Open Biztalk Messaging Manager and create a port to the application and point it to the AIC you have created.Create a channel and use the source Xml definition, the destination Xml definition and the Map, you should have those in your WebDav at this point. Now the last final step: open the Biztalk Administrator and create a Receive Function. Use the folder you configured in the trigger and the channel you have just created.

Now execute an insert statement to the source database and you will see how data is actualiy moved to the destination.