S3 Tech Blog: Developing an Application Integration Component (AIC) in BizTalk Server to Parse XML Data and Store

Share |

This article explains the various steps that are needed to develop an application integration component (AIC) in BizTalk Server to parse XML data and store into SQL Server table.

Creating an XML Data File

Create an XML data file using any text editor, such as Notepad, enter the following segment, and save it as Books.xml.

<Books>
   <Book BookID="ABC"
         BookName="Designing Databases with SQL Server"
         Author="John Murray" Price="250">
   <Book BookID="XYZ"
         BookName="EAI Implementation with BizTalk Server"
         Author="Jim Carry" Price="500">
</Books>

Creating a Table in SQL Server to Store XML Data

Run the following commands by using the SQL Server query analyser tool to create the necessary database, tables, and stored procedures in SQL Server.

USE master
GO
CREATE DATABASE BooksDB ON (NAME = books_dat,
                            FILENAME = 'c:\program files _
                                          \microsoft sql server _
                                          \mssql\data\books.mdf')
GO

USE BooksDB
CREATE TABLE Books (sID char(8) NOT NULL, sName varchar(250) _
                    NOT NULL, sAuthor varchar(50) NOT NULL, _
                    iPrice int NOT NULL)
GO
CREATE CLUSTURED INDEX BooksID on Books(sID)
GO

CREATE PROCEDURE StoreBooks @doc text as
BEGIN TRANSACTION
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
DELETE FROM Books
INSERT INTO Books(sID, sName, sAuthor, iPrice)
SELECT * FROM OPENXML (@idoc, '/Books/Book', 0) _
         WITH (BookID char(8), BookName varchar(250), _
               Author varchar(50), Price int)
EXEC sp_xml_removedocument @idoc
IF (SELECT COUNT(*) FROM Books) > 0
    COMMIT TRANSACTION
ELSE
    ROLLBACK TRANSACTION
GO

GRANT SELECT ON Books TO PUBLIC
GRANT EXECUTE ON ProcBooks TO PUBLIC
GO

Creating a VB ActiveX DLL Component

Create a new ActiveX DLL project using Visual Basic. From Project menu, select the References item and add the following references to the project.

Microsoft BizTalk Server Application Interface Components 1.0 Type Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft XML, v3.0

Enter the following code in the code window for the class:

Implements IBTSAppIntegration
Public Function IBTSAppIntegration_ProcessMessage(ByVal _
                bstrDocument As String)
As String
Dim con
Dim cmd
Dim dom

Const adCmdStoredProc = 4
Const adChar          = 129
Const adParamInput    = 1

Set dom   = CreateObject("MSXML2.DOMDocument")
dom.async = False
dom.LoadXML bstrDocument

Set con = CreateObject("adodb.connection")
con.Open "Provider=sqloledb; Data Source=(local);Initial _
          Catalog=master;", "sa", ""

Set cmd = CreateObject("adodb.command")
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "StoreBooks"
cmd.Parameters.Append cmd.CreateParameter("@doc", adChar, _
               adParamInput, Len(dom.xml), dom.xml)
cmd.Execute

Set cmd = Nothing
Set con = Nothing
Set dom = Nothing
IBTSAppIntegration_ProcessMessage = True
End Function

First, name the class StoreBooks. Then, save the project as StoreBooks.vbp, build StoreBooks.dll, and register the DLL by using regsvr32 from the command prompt.

Deploying an ActiveX DLL Component in COM+

Launch the Component Services from Control Panel or Administrative Tools.
Expand the Component Services until you get COM+ Applications.
Right-click COM+ Applications, click New, click Application, and click Next.
Click Create an empty application.
Enter a name for the application, and then click Next.
Click the This user radio button and type in the username and password for the account that you want this application to run under. Click Finish.
Navigate through the list of COM+ applications and expand your newly created application.
Right-click the Components folder for this application, click New, click Component, and then click Next.
Click Install new component(s), browse to StoreBooks.dll that you have created already, select the DLL, click Open, click Next, and then click Finish.

Marking the Component as AIC in BizTalk Server

To use a COM component as an AIC in BizTalk Server, the component must be registered with the category IDs for all components and for the specific type of component; in this case, that would be application integration. You need to register the AIC component as out-of-process to provide better isolation level.

Sometimes, an AIC component also can be registered as in-process to increase its performance. In the case of out-of-process, the component must be configured to run in an identity other than the interactive user. The AIC component must be registered by a user that belongs to the BizTalk Server Administrators group.

Each AIC component must be associated with two category IDs. One category ID specifies the component as a BizTalk Server component. The other category ID indicates the type of the component. In this case, the type is application integration components (AIC).

To assign category IDs for VB component, you need to first register your component using Regsvr32. Then, search the Registry for the CLSID of your AIC under the HKEY_CLASSES_ROOT\CLSID key. Next, expand the Implemented Categories key of your AIC. You need to add two new keys as follows:

HKEY_CLASSES_ROOT\CLSID\AIC_CLSID\Implemented Categories\ _
                  {5C6C30E7-C66D-40e3-889D-08C5C3099E52}
HKEY_CLASSES_ROOT\CLSID\AIC_CLSID\Implemented Categories\ _
                  {BD193E1D-D7DC-4b7c-B9D2-92AE0344C836}

The first key shown above identifies the AIC as a BizTalk Server component. The second key identifies the component as an AIC component.