Summary
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 _ mssqldatabooks.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_ROOTCLSID key. Next, expand the Implemented Categories key of your AIC. You need to add two new keys as follows:
HKEY_CLASSES_ROOTCLSIDAIC_CLSIDImplemented Categories _ {5C6C30E7-C66D-40e3-889D-08C5C3099E52} HKEY_CLASSES_ROOTCLSIDAIC_CLSIDImplemented 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.
Configuration of AIC Component in BizTalk Server
You now have an Application Integration Component (AIC) to parse XML data and store it into a SQL Server table. If you expand the Components folder, you can see the interfaces for your component.
To test this AIC component, you need to create a file receive function using BizTalk Server Administration tool to receive the Books.xml file, and then hand it over to a channel that is associated with a port that has a primary transport of Application Integration Component (AIC).
Then, you browse to the component and choose the component name for a transport type of Application Integration Component (AIC) at the time you configure the destination port in the Messaging Manager.
Conclusion
This article clearly explained the steps needed to develop an application integration component (AIC) in BizTalk Server to parse XML data and store it into a SQL Server table.
Author:
Syed Hameed
Wipro Technologies
Bangalore, India