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 DATABASE BooksDB ON (NAME = books_dat,
FILENAME = 'c:\program files _
\microsoft sql server _
CREATE TABLE Books (sID char(8) NOT NULL, sName varchar(250) _
NOT NULL, sAuthor varchar(50) NOT NULL, _
iPrice int NOT NULL)
CREATE CLUSTURED INDEX BooksID on Books(sID)
CREATE PROCEDURE StoreBooks @doc text as
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
GRANT SELECT ON Books TO PUBLIC
GRANT EXECUTE ON ProcBooks TO PUBLIC
- Microsoft BizTalk Server Application Interface Components 1.0 Type Library
- Microsoft ActiveX Data Objects 2.5 Library
- Microsoft XML, v3.0
Public Function IBTSAppIntegration_ProcessMessage(ByVal _
bstrDocument As String)
Const adCmdStoredProc = 4
Const adChar = 129
Const adParamInput = 1
Set dom = CreateObject("MSXML2.DOMDocument")
dom.async = False
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)
Set cmd = Nothing
Set con = Nothing
Set dom = Nothing
IBTSAppIntegration_ProcessMessage = True
- 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.
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:
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.