Developing Custom Tracking Solutions
So far in this chapter you have been presented with a full tour of the document tracking system from the perspective of an end user. You are now armed with all the information that you need to perform the two primary tracking activities: configuring tracking settings and viewing tracking data. The remainder of this chapter introduces the topic of building customized tracking solutions using the infrastructure provided by BizTalk Server. We begin by covering the tracking database, including the core tables where interchange data, document data, and routing data is stored. Our database discussion also includes some tips on keeping the database size from growing too large. Finally, we discuss the COM interfaces that can be used to build custom tracking solutions and provide an example to complement the discussion.
The Document Tracking Database
The document tracking database ships with 23 tables and 49 stored procedures. Figure 8.16 shows the most important tables and their relationships with one another.
Figure 8.16. Core document tracking database tables.
A solid black circle connected to a table name indicates a foreign key relationship. For example, the dta_outdoc_details table is showing six foreign keys. Its relationship to the dta_document_data table is one-to-one, whereas its other relationships are many-to-one.
A closer look at the core tables reveals the following three classes of information:
Interchange data
Document data
Routing data
The tables associated with each type of information are introduced in the following sections.
Tables Containing Interchange Data
Interchange data is captured in two tables named dta_interchange_data and dta_ interchange_details (shown in Figure 8.17). Both tables contain one record for every inbound and outbound interchange. Data specific to an interchange is stored in binary format in the imgInterchangeData field of dta_interchange_data. The nBLOBType field indicates the format of the data in the imgInterchangeData field. A value of one indicates that the data can be loaded using the MSXML DOMDocument object. DTA_interchange_data also contains the original filename submitted for inbound interchanges.
Figure 8.17. The dta_interchange_details and dta_interchange_data tables.
DTA_interchange_details identifies the direction of an interchange through the nDirection field. Zero indicates an outbound interchange, and one indicates an inbound interchange. The DTA_interchange_details table also includes date and time stamps for the interchange, information about the transmission protocol used, and any error information that may have been generated as a result of the interchange.
Tables Containing Document Data
The dta_document_data table serves as the master table for all instance document data. More specific information regarding inbound and outbound documents can be found in the dta_indoc_details and dta_outdoc_details tables, respectively. The imgDocumentData field of dta_document_data contains a full copy of documents processed by BizTalk Server. The dta_outdoc_details table contains two fields for each data type that may be present in a BizTalk specification. When you select specific fields for tracking using channel tracking settings, the values are stored to these fields. The strCustomSearch field stores all custom field values selected for tracking in a channel.
Tables Containing Routing Data
The dta_routing_details table (shown in Figure 8.18) serves as the central repository for routing information. The table captures the source and destination organization names, distribution list names, and channel and port references.
Figure 8.18. The dta_routing_details table.
Maintaining the Tracking Database
After you develop an understanding of the tracking database structure, consider developing an automated mechanism for purging the tracking database. One method of archiving the tracking database is to implement a SQL Server agent that runs at scheduled intervals. The agent can use SQL Server's data transformation services to transfer records from your production tracking database to an archive. After a set of records has been safely copied, you can delete them from the production database.
The BizTalk Server SDK includes a sample T-SQL script that sets up an agent for maintaining the tracking database. The script is called DTA_SampleJobs.sql, and it is located in the program filesMicrosoft BizTalk ServerSDKMessaging SamplesSQLServerAgentJobs folder. Examining this script is an excellent way to gain a more in-depth understanding of the inner workings of the tracking database.
The Tracking System Component Object Model
The document tracking system exposes a core set of functionality through a Component Object Model (COM) interface called IBizTalkTrackData. This interface is exposed through a coclass called BTSDocTracking, which you can browse by using the OLE/ COM Object Viewer included with Microsoft Visual Studio. The document tracking COM interface is implemented in CISDTA.dll, located in the Program FilesMicrosoft BizTalk Server folder.
IBizTalkTrackData contains three methods: GetInterchanges(), GetInDocDetails(), and GetOutDocDetails(). Figure 8.19 shows the syntax of these methods.
Figure 8.19. The BTSDocTracking class and IBizTalkTrackData interface.
To make the BTSDocTracking class available in Visual Basic, add a reference to the Microsoft BizTalk Server Doc Tracking 1.0 Type Library. This allows you to instantiate the BTSDocTracking object within your project.
All three of the methods in the IBizTalkTrackData interface require a submission ID to be passed as a parameter. A submission ID is returned when you initiate an interchange by calling the IInterchange::Submit() function. You can use this submission ID to retrieve tracking information for an interchange. You can also query the dta_interchange_details database for a list of submission IDs that have been tracked. Submission IDs are stored in the uidSubmissionGUID field.
Example: A Custom Tracking Solution
This book includes a useful application called RouteTest that can initiate a variety of interchanges using the BizTalk Server API. Chapter 12, “Advanced BizTalk Messaging,” covers the RouteTest application in depth for the purposes of illustrating advanced messaging topics. In addition, the application contains a custom user interface for document tracking that demonstrates how to use the document tracking object model.
To install RouteTest, simply run the executable setup program located in the RouteTestSetup folder. The setup program installs the necessary application files and also automates the process of configuring BizTalk Messaging. After you have successfully run the setup program, run RouteTest.exe to launch the application.
Figure 8.20 shows the main window for RouteTest. You can initiate an interchange by choosing a scenario in the Routing Test list box and then clicking the Submit button. Each scenario represents a different type of interchange submission.
Figure 8.20. The RouteTest application.
To use the document tracking portion of RouteTest, select Scenario 1 from the list of routing tests and click the Submit button. This submits an interchange to BizTalk Server and returns a submission identifier. The submission ID should be displayed in the results window. Next, click the Tracking button to view interchange and document details for the interchange just submitted. The results of making calls to the document tracking object model are displayed in the Interchange Tracking window (see Figure 8.21).
Figure 8.21. Viewing tracking data for an interchange using RouteTest.
Listing 8.1 shows the Visual Basic code for retrieving the tracking data. The code simply creates a new instance of the BTSDocTracking object and calls three methods in succession: GetInterchanges(), GetInDocDetails(), and GetOutDocDetails(). Each method uses the submission ID currently displayed in the RouteTest results window.
Note
The code is part of the RouteTest application that is used in Chapter 12. The code by itself is not a complete executable example, but it demonstrates how to use the IBizTalkTrackData interface in Visual Basic.
The file Code08.bas that contains the code for Listing 8.1 can be downloaded at the publisher's Web site.
Listing 8.1. Using the Document Tracking API
Private Sub btnTracking_Click()
Dim strSubHandle As String
Dim oTracking As New BTSDocTrackingLib.BTSDocTracking
Dim oRSInterchanges As ADODB.Recordset
Dim oRSInDocDetails As ADODB.Recordset
Dim oRSOutDocDetails As ADODB.Recordset
Dim frmTracking As New frmTracking
strSubHandle = ctlResults.Text
' Get tracking data
Set oRSInterchanges = oTracking.GetInterchanges(strSubHandle)
Set oRSInDocDetails = oTracking.GetInDocDetails(strSubHandle)
Set oRSOutDocDetails = oTracking.GetOutDocDetails(strSubHandle)
' Display tracking data
Set frmTracking.ctlInterchanges.DataSource = oRSInterchanges
Set frmTracking.ctlInDocDetails.DataSource = oRSInDocDetails
Set frmTracking.ctlOutDocDetails.DataSource = oRSOutDocDetails
frmTracking.ctlSubmissionID.Caption = strSubHandle
frmTracking.Show
End Sub
|
The methods of IBizTalkTrackData return ADO recordset objects, which can easily be displayed in a grid format using an ActiveX control. RouteTest uses the Microsoft DataGrid control and sets the DataSource property of the control to the ADO recordset returned by the tracking API calls.