Database Integration with Microsoft BizTalk Server 2002
Click here to download sample - BTS_WP_DBIntegration Samples.exe. (687 KB) Click here to download sample - XML for SQL.exe. (1.06 MB) Click here to download sample - setup.exe. (4.53 MB)Scott Woodgate
Microsoft Corporation
May 2002
Applies to:
Microsoft® BizTalk® Server 2002
Microsoft SQL Server™ 2000
Summary: How to integrate BizTalk Server 2002 with databases in general, and with Microsoft SQL Server 2000 in particular, and how to process XML into and out of a database. (39 printed pages)
Download Bts_wp_dbintegration_samples.exe.
Contents
Introduction
BizTalk Server Database Integration
Using ADO in BizTalk Server Database Integration
Leveraging SAX2 for Large Data Files
Integration with SQL Server
Overview of XML and SQL Server
Using FOR XML and OPENXML
Using Updategrams
Using SQL Server XML View Mapper
Leveraging Stored Procedures and DTS in SQL Server 2000
References
Introduction
Business-to-business data processing requires data interchange that uses XML as the ubiquitous, extensible, and platform-independent transform format. The challenge is how to reconcile the requirements of relational data stores and hierarchical XML data. This article focuses on providing reusable samples and techniques for integrating Microsoft® BizTalk® Server 2002 and databases, with specific reference to leveraging Microsoft SQL Server™ 2000.
A number of integration options are discussed. These include:
- Using Microsoft ActiveX® Data Objects (ADO) for integration with generic databases
- Leveraging the most recent version of the Simple API for XML (SAX2) for integrating large data files with generic databases
- Using the FOR XML clause and the OPENXML keyword for integration with SQL Server 2000
- Using Updategrams for integration with SQL Server 2000
- Using SQL Server XML View Mapper for integration with SQL Server 2000
- Leveraging Data Transformation Services (DTS) in SQL Server 2000
Throughout this paper, samples are provided to assist in demonstrating and explaining the concepts. These samples follow a common structure to enable you to make comparisons.
Included with this article is a Samples directory (contained in the download) that contains several folders. Each folder contains sample files for a specific section. For example, sample files for the section "Leveraging SAX2 for Large Data Files" are contained in the directory Samples\SAX2. The samples assume that BizTalk Server is installed on your C drive, and you should unzip the Samples directory to your C drive. If you have installed BizTalk Server on another drive, you need to unzip the Samples directory to that drive and update the file paths in the samples accordingly.
Most samples involve BizTalk Server, SQL Server, and service components such as application integration components or Microsoft Windows® Script Components. Microsoft Visual Basic® Scripting Edition (VBScript) and ADO applications are also used extensively in the samples.
Many samples use SQLXML 3.0 and SQL Server XML View Mapper 1.0. You will need to download both SQLXML 3.0 and SQL Server XML View Mapper 1.0 from the Downloads section of the MSDN® Web site and install them on your computer before running the samples.
Stored procedures have been used whenever possible. It is preferable to use stored procedures for data access because of their performance advantages. In addition, it is relatively simple to execute them from Visual Basic applications by using ADO Command objects.
All examples have been simplified to make the code and the process simple to understand and follow. They are not meant to represent examples of best practices for coding.
BizTalk Server Database Integration
BizTalk Server can integrate with many types of databases. One of the most useful tools for BizTalk Server database integration is ActiveX Data Objects, or ADO. Other tools, including the Microsoft Simple API for XML (SAX) and the XML Document Object Model (XML DOM), can also be leveraged in BizTalk Server database integration.
BizTalk Server can achieve the highest level of integration with SQL Server 2000 databases. This is due to the advanced XML support that SQL Server 2000 provides. The second part of this white paper focuses on BizTalk Server integration with SQL Server 2000.
Using ADO in BizTalk Server Database Integration
ADO is a fast, powerful, and convenient mechanism for interacting from any language with many different databases, such as SQL Server databases, Oracle databases (using the Microsoft OLE DB Provider for Oracle), and DB2 or VSAM data sources (for example, using the OLE DB drivers packaged in Microsoft Host Integration Server 2000).
ADO recordsets allow you to navigate the records easily, and to apply filters and bookmarks. They also provide sorting, automatic pagination, and persistence. Recordsets can be efficiently marshaled across tiers to their native and extremely compact binary format—the Advanced Data TableGram (ADTG) format.
ADO versions 2.5 and later also provide capabilities for retrieving data in XML format by using the adPersistXML option as well as for executing XML query templates to perform database insert, delete, and update operations. The ADO XML support can be leveraged in BizTalk Server database integration.
BizTalk Server 2002 uses ADO internally through the XLANG Scheduler Engine to save the state of XLANG schedules to the persistence database. You can also leverage ADO for BizTalk Server database integration through application integration components (AICs) inside BizTalk Messaging and through script or COM components inside BizTalk Orchestration. In BizTalk Server database integration, the ADO code is normally contained in AICs.
Retrieving XML data by using the adPersistXML switch
In ADO versions 2.5 and later, you can retrieve data by using normal Transact-SQL queries and persist the data as XML by using the adPersistXML option of the ADO recordset.
The ADO recordset provides a Save method that persists data to a destination such as a file or an Active Server Pages (ASP) page. By default, the ADO recordset persists data in the ADTG format using the adPersistADTG option. To persist data as an XML document you need to execute the Save method using the adPersistXML option.
The main steps for retrieving XML data are:
- Connect to a data source using the ADO Connection object.
- Instantiate the ADO Recordset object, and then open a SELECT query to retrieve data.
- Persist the data as an XML document using the Save method of the Recordset together with the adPersistXML switch.
Sample: using ADO
This sample demonstrates how to retrieve XML data from the Customers table in the Northwind database by using the ADO adPersistXML option.
Note All sample files for this section are included in the Samples\ADO directory.
Step 1: define a Windows Script Component
Open Notepad and type the following code:
<?xml version="1.0"?>
<component>
<?component error="true" debug="true"?>
<registration
description="ADOXML"
progid="ADOXML.WSC"
version="1.00"
classid="{71f81b28-4695-4220-bd77-c21abaca02cb}">
</registration>
<public>
<method name="GetXML">
<PARAMETER name="sCOnn"/>
<PARAMETER name="sSQL"/>
<PARAMETER name="sFileName"/>
</method>
</public>
<script language="VBScript">
<
Figure 1.
The main steps for setting up the sample are:
- Define a SQL Server database table
- Register the AIC
- Define a BizTalk Server port and channel
- Define a BizTalk Server receive function
- Run the sample
These steps are summarized in the following paragraphs.
Note All sample files for this section are included in the Samples\SAX2 directory.
Step 1: define a SQL Server database table
The SAX sample uses a Contacts table in the Pubs database to store data provided in the XML documents.
The following script creates the required database table. The script is included in the file CreateContactsTable.sql in the Samples\SAX2\SQLScript samples directory.
use pubs
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Contacts]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Contacts]
Go
CREATE TABLE [dbo].[Contacts] (
[CompanyCode] [char] (20),
[Name] [varchar] (50),
[Tel] [varchar] (50),
[Email] [varchar] (50),
[RecordTimeStamp] [datetime] not NULL default (GetDate())
) ON [PRIMARY]
Go
Using SQL Query Analyzer, define the database and table using the preceding script. Then verify that the table has been created correctly.
Step 2: register the AIC
- Click Start, point to Programs, point to Administrative Tools, and then click Component Services. The Component Services Microsoft Management Console (MMC) window appears.
- In the Component Services console tree, expand Component Services, expand Computers, expand My Computer, and then click COM+ Applications.
- Right-click COM+ Applications, point to New, and then click Application. The COM Application Install Wizard opens
- On the Welcome to the COM Application Install Wizard page, click Next.
- On the Install or Create a New Application page, click Create an empty application. In the Enter a name for the new application box, type SAXAIC. In the Activation type area, click Server application and then click Next.
- On the Set Application Identity page, in the Account area, click Interactive user-the current logged on user, and then click Next.
- Click Finish.
- In the console tree, expand COM+ Applications and expand the new package you created, called SAXAIC. Click the Components folder.
- Browse to the directory Samples\SAX2 and drag AIC4SAX2.dll to the Components folder of package SAXAIC in Component Services.
- Close Component Services.
Note The Visual Basic source code for AIC4SAX2.dll is provided in the same directory as AIC4SAX2.dll.
Step 3: define a BizTalk Server port and channel
- Open Windows Explorer and create the following new directory:
C:\Program Files\Microsoft BizTalk Server\BizTalkServerRepository\DocSpecs\SAX2
- Copy the document specification file Contacts.xml from the Samples\SAX2\SAXSpecsandSamples directory to the new directory.
- Click Start, point to Programs, point to Microsoft BizTalk Server 2002, and then click BizTalk Messaging Manager.
- On the File menu, point to New, point to Messaging Port, and then click To an Application.
- On the General Information page, in the Name box, enter SAXSamplePort, and then click Next.
- On the Destination Application page, select Application and click New. The Organization Properties dialog box appears.
- In the Organization Properties dialog box, click the Applications tab, click Add, and then enter SAXSampleApplication in the Name box. Click OK twice to return to the Destination Application page.
- On the Destination Application page, under Application, select SAXSampleApplication from the drop-down list. In the Primary transport frame, click Browse. The Primary Transport dialog box appears.
- In the Primary Transport dialog box, select Application Integration Component from the Transport type list and then click Browse. In the Select a Component dialog box, select AIC4SAX2 Class1 from the list. Click OK twice to return to the Destination Application page. Click Next twice to enter the Security Information page.
- On the Security Information page, select Create a channel from this messaging port, and then select From a application from the Channel type list. Click Finish. The New Channel wizard appears.
- On the General Information page, enter SAXSampleChannel as the channel name, and then click Next twice to enter the Inbound Document page.
- On the Inbound Document page, click New. In the New Document Definition dialog box, type SAXSampleDoc in the Document definition name box. Select Document Specification and click Browse. In the Select a Component dialog box, double-click the SAX2 folder, click Contacts.xml, click Open, and click OK to return to the Inbound Document page. Click Next to enter the Outbound Document page.
- On the Outbound Document page, click Browse, select SAXSampleDoc from the list, and then click OK.
- Click Next twice, and then click Finish. Finally, close the BizTalk Messaging Manager console.
Step 4: define a BizTalk Server receive function
- Open Windows Explorer and create a new directory, C:\Temp\SAXSample, as the inbound directory.
- Click Start, point to Programs, point to Microsoft BizTalk Server 2002, and then click BizTalk Server Administration.
- In the console tree, expand Microsoft BizTalk Server 2002, expand BizTalk Server Group, and then click Receive Functions.
- Right-click Receive Functions, point to New, and then click File Receive Function. The Add a File Receive Function dialog box appears.
- In the Add a File Receive Function dialog box, type SAXSampleReceiveFunction in the Name box. Type *.xml in the File types to poll for box. Type C:\Temp\SAXSample in the Polling location box.
- Click Advanced and click the Channel name drop-down list. Select SAXSampleChannel from the list. Click OK twice to finish the process.
Step 5: run the sample
- Place the sample document SAXSample_Small.xml in the inbound directory C:\Temp\SAXSample. Verify that the new records defined in the XML document are inserted in the Contacts table of the Pubs database.
- Repeat step 1 for the document SAXSample_Large.xml and ensure that all records are inserted in the SQL Server database.
Note The current logged-on user must be a member of the BizTalk Server Administrators group and must remain logged on while running this sample.
Integration with SQL Server
The focus of this part of the article is to discuss methods for integrating BizTalk Server and SQL Server 2000. These methods combine tools to create a way to obtain XML data from SQL Server in a highly scalable manner and with minimal coding effort. It is worth noting that these techniques are generally applicable to other database technologies.
Overview of XML and SQL Server
SQL Server 2000 introduces many built-in features for XML support. These include:
- The ability to use HTTP publishing functionality
- The FOR XML clause for querying database tables and receiving the results as an XML document
- The OPENXML keyword for updating database tables from XML documents
SQL Server 2000 can return the results of SELECT statements as XML documents. The SQL Server 2000 Transact-SQL SELECT statement supports the FOR XML clause, which returns an XML document instead of a relational result set. The OPENXML keyword allows an XML document to be treated in the same way as a table or view in the FROM clause of a Transact-SQL statement. This allows inserting, updating, or deleting data by using an XML document.
The entire SQL Server XML functionality is implemented in SqlXml3.dll. The template files, annotated schema files, Extensible Stylesheet Language (XSL) files, and XPath queries are handled on the Internet Information Services (IIS) server. SqlXml3.dll translates the XPath queries against the annotated schema into SQL commands.
SQL Server HTTP publishing
To use the SQL Server HTTP publishing functionality, you must set up an appropriate virtual directory. You can do this by using the "Configure SQL XML Support in IIS" utility to define and register a new virtual directory. This utility is shipped with SQL Server 2000, and instructs IIS to create an association between the new virtual directory and an instance of Microsoft SQL Server.
A limitation of this approach is that to access SQL Server by using HTTP, XML views of SQL Server 2000 databases must be defined by annotating XML-Data Reduced (XDR) schemas to map the tables, views, and columns associated with the elements and attributes of the schema. The XML views can then be referenced in XPath queries, which retrieve results from the database and return XML documents. This task is a tedious manual process that requires time and effort. There is no out-of-box tool available in SQL Server 2000 to automate this task. Also, this solution is based on the Internet Server Application Programming Interface (ISAPI) and is not scalable because querying SQL Server through ISAPI yields low throughput.
The following illustration shows how HTTP requests are handled.

Figure 2.
You should also consider the potential limitation of this approach. Constructing a FOR XML clause to obtain a hierarchical XML document based on SQL relational tables/views with multiple joins is a time-consuming and error-prone process. Hierarchical XML documents can get very complicated because multiple tables/views must be joined to produce the desired XML document. Some FOR XML statements are more than 30 kilobytes (KB) long. Automating this process requires a tremendous amount of resources.
Loading XML to SQL Server 2000
Microsoft provides the following three ways to update SQL Server 2000 by using data from XML files:
- The OPENXML keyword. OPENXML ships with SQL Server 2000. It is used for updating data in databases, and is natively supported by SQL Server 2000.
- XML Updategrams. Updategrams ship with SQLXML 3.0. They give developers an XML-based approach to data modification.
- XML Bulk Load. XML Bulk Load ships with SQLXML 3.0. It is for loading large amounts of XML data into a database.
To load small amounts (typically 100 KB) of XML data into SQL Server, OPENXML and XML Updategrams are good choices. To load large amounts (typically 100 MB) of XML data, the XML Bulk Load feature is more efficient.
XML Bulk Load is similar in functionality to the bulk copy (bcp) utility and the Transact-SQL BULK INSERT statement. Unlike bcp and BULK INSERT, which accept only tabular data representations, XML Bulk Load supports loading XML hierarchies into one or more database tables. Also unlike OPENXML and XML Updategrams, XML Bulk Load uses Microsoft XML Core Services (MSXML)—formerly called Microsoft XML Parser—to process data rather than parsing the entire XML dataset into memory before processing it. Using the streaming interface lets XML Bulk Load process datasets larger than 100 MB without running out of memory.
Both XML Bulk Load and XML Updategrams are based on annotated schema; these ISAPI-based solutions must go through IIS to complete the task of interchanging XML with SQL Server. Therefore, they are not scalable due to the performance issue.
XML Bulk Load has another limitation. When bulk-loading XML into multiple tables, the rule is that the XML document must include the primary/foreign key values. In practice, these key values are usually missing. For example, the XML purchase order file from customers (which contains Order and Order Details) usually does not contain primary/foreign key values such as OrderID.
Using FOR XML and OPENXML
SQL Server 2000 provides the FOR XML clause and the OPENXML keyword that allow an XML document to be translated into a data format used by a relational database and vice versa. These functions allow XML data to be retrieved from or inserted into a SQL Server table.
FOR XML
Traditionally, the ActiveX Data Objects (ADO) recordset has been widely used to retrieve data from relational databases. SQL Server 2000 extends the SELECT statement to enable the retrieval of data as an XML document through the FOR XML clause.
To use the FOR XML functionality, simply append the keywords FOR XML to a SELECT statement. This indicates to the SQL Server query processor that you want the results to be returned as an XML stream instead of as a recordset. In addition to including the FOR XML keywords, you must also specify a mode to indicate the format of the XML that should be returned. This mode can be specified as RAW, AUTO, or EXPLICIT. For details about the mode, refer to the SQL Server Books Online in the MSDN library.
Within the BizTalk Server context, the FOR XML clause provides a way to retrieve data as XML documents directly without coding. This greatly simplifies the integration between BizTalk Server and SQL Server.
In practice, one of the challenges in using the FOR XML clause is to create an SQL query that will return an XML document that complies with a predefined XML document schema.
In the Using SQL Server XML View Mapper section, we describe an approach that simplifies the task of creating complex FOR XML queries by using the SQL Server XML View Mapper tool.
OPENXML
The OPENXML keyword is used primarily to insert data directly from an XML document into database tables.
Before the OPENXML functionality can be utilized, the XML document provided by BizTalk Server needs to be parsed, validated as XML, and mapped into a tree structure that represents the nodes and elements of the document. This is done through the sp_xml_preparedocument stored procedure.
After the document has been prepared, the SQL Server OPENXML keyword is used to create an in-memory rowset from the data tree created by sp_xml_preparedocument. This rowset can be used anywhere a table or view is used and is therefore ideal for updating or inserting data using an UPDATE or INSERT statement.
After the XML data has been updated or inserted into the database, the sp_xml_removedocument stored procedure should be executed to reclaim the memory used by the node tree.
The OPENXML keyword offers the following benefits:
- Efficiency is increased as a result of fewer network round trips.
- The data tier is conceptually simple and easy to code. It no longer needs to be aware of the underlying database structure by sending XML to SQL Server as a single input parameter for a stored procedure. That knowledge now lies in the stored procedures that use OPENXML to map XML nodes to tables, rows, and columns.
Process description
Within the BizTalk Server context, an application integration component (AIC), COM component, or Windows Script Component (WSC) can be used to invoke the XML support in SQL Server. This allows an XML document to be processed through BizTalk Orchestration and BizTalk Messaging and inserted directly into a database. The following illustration shows the overall process.

Figure 3.
In the process, an XML document is passed to BizTalk Server by an AIC, a COM component, or a WSC. ADO is then used to invoke a custom stored procedure, supplying the XML document as a parameter. The custom stored procedure leverages the OPENXML functionality to insert the document into the database.
Sample: using OPENXML
This section presents a BizTalk Server implementation that inserts XML data into SQL Server 2000 by using the OPENXML keyword. The main steps are as follows:
- Create a stored procedure for generating a SQL Server database table by using the OPENXML keyword and an XML document.
- Create a stored procedure for inserting XML data into the database table by using the OPENXML keyword.
- Create a WSC, BTS2SQLOPENXML.wsc, which will be called from BizTalk Orchestration. The WSC accesses the stored procedure to insert XML data into the SQL Server database.
- Create a BizTalk Orchestration schedule, TestBTS2SQLOPENXML.skv, which passes an XML document to the BTS2SQLOPENXML.wsc component. The WSC inserts the XML data into the database table.
Note All sample files for this section are included in the Samples\OpenXML directory.
Step 1: define and test the CreateNewTableAndPopulate stored procedure
The OPENXML keyword can be used to create and populate a new database table based on an XML document. The columns of the database table correspond to the elements or attributes of the XML document.
Assume that we have the following purchase order XML document:
<?xml version="1.0"?>
<Order OrderID="1001" CustomerID="ABC-001" OrderDate="01/02/2001"
EmployeeID="001"/>
We want to create a PurchaseOrders table in the Pubs database according to the preceding XML document. Using SQL Query Analyzer, define the Pubs database and use the following script, CreatePOTable.sql to create the PurchaseOrders table. The script is included along with the other SQL scripts for this section in the Samples\OpenXML\SQLScripts directory.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PurchaseOrders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PurchaseOrders] GO CREATE TABLE [dbo].[PurchaseOrders] ( [OrderID] [int] NULL , [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EmployeeID] [int] NULL , [OrderDate] [datetime] NULL ) ON [PRIMARY] GO
Using SQL Query Analyzer, run the following script, CreatePOTableAndPopulate.sql to create a new stored procedure, CreatePOTableAndPopulate.
...
CREATE PROCEDURE CreatePOTableAndPopulate @xmlOrder VARCHAR(2000) AS
DECLARE @iTree INTEGER
EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder
If exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PurchaseOrders]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1) drop table [dbo].[PurchaseOrders]
SELECT * INTO PurchaseOrders
FROM OPENXML(@iTree, 'Order', 1)
WITH (OrderID INTEGER,
CustomerID nCHAR(8),
EmployeeID INTEGER,
OrderDate DATETIME)
EXEC sp_xml_removedocument @iTree
...
Then run the stored procedure, TestCreatePOTableAndPopulate.sql, passing in an XML document as follows:
Exec CreatePOTableAndPopulate '
<?xml version="1.0"?><Order OrderID="1001" CustomerID="ABC-001"
OrderDate="01/02/2001" EmployeeID="001"/>'
After running the stored procedure, browse the Pubs database to verify that a new PurchaseOrders table has been created and the XML data has been inserted correctly.
Step 2: define and test the InsertPurchaseOrder stored procedure
The OPENXML keyword can also be used to insert XML data into columns of a SQL Server database table. In this case the XML data and the database table format must match each other.
For example, we want to insert the following XML data into the PurchaseOrders table created in step 1:
<?xml version="1.0"?>
<Order OrderID="1012" CustomerID="ABC-005" OrderDate="08/01/2001"
EmployeeID="003"/>
We can create the following stored procedure (InsertPurchaseOrder.sql), InsertPurchaseOrder, to perform the task:
...
CREATE PROCEDURE InsertPurchaseOrder @xmlOrder VARCHAR(2000)AS
DECLARE @iTree INTEGER
EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder
INSERT PurchaseOrders (OrderID, CustomerID, EmployeeID, OrderDate)
SELECT * FROM
OPENXML(@iTree, 'Order', 1)
WITH ( OrderID INTEGER,
CustomerID nCHAR(8),
EmployeeID INTEGER,
OrderDate DATETIME)
EXEC sp_xml_removedocument @iTree
...
We then test the InsertPurchaseOrder procedure by using SQL Query Analyzer and the following script, TestInsertPurchaseOrder.sql:
Exec InsertPurchaseOrder '<?xml version="1.0"?><Order OrderID="1012" CustomerID="ABC-005" OrderDate="08/01/2001" EmployeeID="003"/>'
After running the preceding script, verify that the XML data has been correctly inserted into the PurchaseOrders table.
Step 3: create and test the BTS2SQLOPENXML.wsc component
To access the SQL Server stored procedure from BizTalk Orchestration, we can either use a WSC, a COM component, or an AIC. In this sample we use a WSC, BTS2SQLOPENXML.wsc, to run the InsertPurchaseOrder stored procedure.
This WSC has one method, ExecSQL_Param, which takes three parameters—a connection string, an SQL query string, and an XML document string. The following code implements the WSC:
<?xml version="1.0"?>
<component>
<?component error="true" debug="true"?>
<registration
description="BTS2SQLOPENXML"
progid="BTS2SQLOPENXML.wsc"
version="1.00"
classid="{94f80128-269a-4220-bd77-c21abaca4ed3}">
</registration>
<public>
<method name="ExecSQL_Param">
<PARAMETER name="sCOnn"/>
<PARAMETER name="sSQL"/>
<PARAMETER name="sXML"/>
</method>
</public>
<script language="VBScript">
<![CDATA[
function ExecSQL_Param(sConn, sSQL, sXML)
Dim cn
Dim cmd
'Connect to DB and run SQL
Set cn = CreateObject("adodb.Connection")
cn.Open sConn
'Execute procedure
Set cmd = CreateObject("adodb.command")
cmd.ActiveConnection = cn
cmd.CommandText = sSQL & " '" & sXML & "'"
cmd.Execute
'Clean up
set cmd = nothing
set cn = nothing
ExecSQL = ""
end function
]]>
</script>
</component>
After using Notepad to define the .wsc file, save it as BTS2SQLOPENXML.wsc to the Sample\OPENXML folder.
Register the component by using regsvr32.dll or by right-clicking the .wsc file in Windows Explorer and clicking Register.
Now test the WSC by using the following Visual Basic Scripting Edition (VBScript) code. Open Notepad, enter the code, and save the file as TestBTSOPENXMLInsertData.vbs.
Dim o
Dim sXML
sXML = "<?xml version=""1.0""?><Order OrderID=""1012"" CustomerID=""ABC-
002"" OrderDate=""11/01/2001"" EmployeeID=""102""/>"
Set o = CreateObject("BTS2SQLOPENXML.wsc")
o.ExecSQL_Param "Provider=sqloledb; Data Source=(local); Initial
Catalog=Pubs; Trusted_Connection=Yes;", "exec InsertPurchaseOrder", sXML
MsgBox "Job done."
Set o = nothing
Test the activation and execution of BTS2SQLOPENXML.wsc by running TestBTSOPENXMLInsertData.vbs, and verify that a new row has been added to the PurchaseOrders table in the Pubs database.
Step 4: define and test a BizTalk Orchestration schedule
Now we will build a simple BizTalk Orchestration schedule that passes an XML document to the WSC, which then calls the stored procedure to insert the XML data into the SQL Server database table.
The BizTalk Orchestration schedule has only one action, as shown in the following illustration. The schedule instantiates the BTS2SQLOPENXML.wsc component and calls the ExecSQL_Param method, passing in the three parameters required by the method. One of the parameters is the XML document to be inserted into the SQL Server database.
Figure 4. Click thumbnail for larger image.
The main steps for building the sample orchestration schedule are:
- Drag an Action shape onto the process and name it Test SQL OPENXML. Add an End shape to the flowchart, and then complete the flowchart as shown in the illustration.
- Bind the BTS2SQLOPENXML.wsc to the Action shape by using the Windows Script Component Wizard.
- Add three constants in the orchestration data page. Details of the constants are defined in the following table. The XMLString constant contains the XML document to be inserted into the database.
Constant name Constant value ConnectionString Provider=sqloledb; Data Source=(local); Initial Catalog=Pubs; Trusted_Connection=yes;SQLString Exec InsertPurchaseOrderXMLString <?xml version="1.0"?><Order OrderID="1004" CustomerID="ABC-006" OrderDate="01/03/2001" EmployeeID="108"/> - Connect each constant to the corresponding ExecSQL_Param_In message fields by drawing a line from the constant to the input parameter. The following illustration shows the data page of the sample schedule. Figure 5. Click thumbnail for larger image.
- Save the BizTalk Orchestration schedule as TestBTS2SQLOPENXML.skv to the Sample\OPENXML folder. Compile the schedule to generate the executable file TestBTS2SQLOPENXML.skx. Save the file to the same folder.
To test the orchestration schedule, we can create a .vbs file to launch the schedule as we did in previous sections. We can also use the XLANGMon.exe utility to launch the schedule. Here we use the latter approach for testing.
To test the orchestration schedule
- Open Windows Explorer and browse to the folder C:\Program Files\Microsoft BizTalk Server\SDK\XLANG Tools. Double-click XLANGMon.exe. The XLANG Event Monitor window appears.
- Right-click the TestBTS2SQLOPENXML.skx file and drag it onto the XLANG Scheduler node. This will run the orchestration schedule.
- Verify that a new row of data has been inserted into the PurchaseOrders table in the Pubs database.
Using Updategrams
SQLXML 3.0 includes a major feature, the Updategram, which allows changes to an XML document as database inserts, updates, and deletes. An XML Updategram can be used as the source for a command against the Microsoft OLE DB Provider for SQL Server 2000.
The Updategram is a way to specify an update to a SQL Server 2000 database through XML. You specify what the XML data looks like now and what you want it to look like when the Updategram is executed. The Updategram processor automatically generates and executes the SQL statements required to produce the desired change. Inserts, updates, and deletes can be specified with Updategrams.
The Updategram uses annotated schemas to map XML data to database tables. The annotated schema was introduced in SQL Server 2000 and is supported by BizTalk Server. Leveraging the power of annotated schemas, the Updategram is more flexible and versatile to use than the OPENXML keyword.
Updategrams are submitted for processing through the same mechanisms as all XML SQL templates; that is, they are posted to ISAPI, read from a file specified in a URL, or submitted with an ADO or OLE DB command.
To perform standard SQL commands on the database, the following rules are applied to Updategrams:
- If a record instance appears only in the <before> block with no corresponding instance in the <after> block, the Updategram performs a DELETE operation.
- If a record instance appears only in the <after> block with no corresponding instance in the <before> block, the Updategram performs an INSERT operation.
- If a record instance appears in the <before> block and has a corresponding instance in the <after> block, it is an UPDATE operation. In this case, the Updategram updates the record instance to the value specified in the <after> block.
Updategrams and annotated schemas
An Updategram is based on the xml-updategram namespace and contains one or more sync elements. Each sync element represents a transactional unit of database modifications. Updategrams describe a desired change by specifying what the relevant portion of an XML document looks like before and after the change is made. The mechanism for specifying the change is a <sync> block. Each of these update blocks defines a group of changes that are treated as an atomic unit of work. In terms of atomicity the <sync> block defines the transaction scope for an update.
A <sync> block consists of a <before> block and an <after> block as follows:
- The <before> block contains the data values that currently exist in an XML document, and is similar to the WHERE clause in a standard SQL statement. As in the SQL WHERE clause, the before information is used to locate which data is to be updated
- The <after> block contains the new or updated values for the relevant data fields in the database.
The following is an example of an Updategram:
<?xml version="1.0"?>
<employeeupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync updg:mapping-schema="EmployeeSchema.xml">
<updg:before>
<Employee EmpID="1"/>
</updg:before>
<updg:after>
<Employee Phone="555-112233"/>
</updg:after>
</updg:sync>
</employeeupdate>
Because an Updategram is an XML document, it can easily be submitted to BizTalk Server as an interchange, and then processed to perform the required database changes.
Sample: using Updategrams
The use of Updategrams is incorporated in the example in the next section.
Using SQL Server XML View Mapper
Microsoft SQL Server XML View Mapper, or XML View Mapper, is a tool that automates the process of generating annotated schema for retrieving XML data from SQL Server.
XML View Mapper, together with other SQL Server utilities, is used to create XML schemas and database queries that retrieve XML data directly from SQL Server. XML View Mapper is also a useful tool for integrating BizTalk Server and SQL Server.
Using XML View Mapper to generate an annotated schema
XML View Mapper is a mapping tool that relates XML-Data Reduced (XDR) schemas and SQL Server database schemas to generate an XML annotated schema. The XML annotated schema enables SQL Server to interact with the database based on established XDR schemas.
Within XML annotated schemas, a set of predefined annotations is used to define the links and relationships between elements and attributes in the XDR schema and tables and fields in a database. XML annotated schemas can be created by using a text editor, but the process is tedious, error prone, time consuming, and difficult to debug.
XML View Mapper provides a simple, visual, declarative, and integrated environment for defining XML views on a database. No coding is required to generate annotated mapping schemas, and utilities are available to validate, test, and export the schemas that are generated. The annotated schemas can be used to support ad hoc queries. They can also be used as the basis of an application that retrieves XML data from a SQL Server database by using the XPath navigation language.
Getting XML data through an annotated schema requires IIS. This approach has a significant performance disadvantage when the volume of data exchange is high. The problem can be overcome by using a stored procedure against SQL Server to generate a targeted XML file instead of passing the annotated schema through the IIS server. This stored procedure contains the FOR XML EXPLICIT clause in the SELECT statement to obtain XML data directly from SQL Server.
Retrieving XML from SQL Server
The FOR XML EXPLICIT clause is one of the most efficient ways to retrieve XML data directly from a SQL Server database. It is, however, not an easy task to create SELECT . . . FOR XML EXPLICIT queries that return the desired XML data when the relationships between data tables become complex.
XML View Mapper can be used to create annotated XML view schemas easily and visually. The XML view schemas generated in XML View Mapper are equivalent to SQL Server mapping schemas. After a schema is generated we can test the schema by executing an XPath query against the schema in the XML View Mapper environment. We then obtain the complex FOR XML query by using the SQL Profiler tool. The following steps summarize the process:
- Use XML View Mapper to create an annotated mapping schema for an XDR schema and a database. In practice, you usually get the XDR schema based on the target XML document from BizTalk Server.
- Test the mapping schema by using the XPath query tool in the XML View Mapper environment.
- Capture the SELECT . . . FOR XML EXPLICIT query by running a SQL Profiler trace and the XPath query.
- Extract the SELECT . . . FOR XML EXPLICIT query and insert it into a stored procedure or an ADO query.
- Incorporate the stored procedure or the ADO query into a BizTalk Server application through an AIC or WSC.
The following illustration shows the preceding steps.

Figure 6.
Sample: using XML View Mapper
Before running this sample, you need to install XML View Mapper on your computer.
This sample demonstrates using the Updategram and retrieving XML data by using a complex SELECT . . . FOR XML EXPLICIT query from the Northwind database. The query is obtained by using the XML View Mapper tool and SQL Profiler. Both the Updategram and the FOR XML query are created and stored in .xml files.
A WSC component and a BizTalk Orchestration XLANG schedule are also generated for testing the BizTalk Server integration with the SQL Server database.
Note All sample files for this section are included in the Samples\XMLViewMapper directory.
Step 1: create an annotated mapping schema by using XML View Mapper
If you have not used XML View Mapper, it is a good idea to go through the XML View Mapper tutorial before proceeding with this sample. Because the mapping schemas used in this sample are the same as those in the tutorial, we will bypass the process of creating the mapping schema and open the mapping provided in this sample package.
- Browse to the folder Samples\XMLViewMapper and double-click OrderForm.smp. XML View Mapper is launched with the OrderForm project loaded.
- In the Project Explorer window, expand Map Modules and double-click OrderForm-map. A mapping screen appears.
- Expand the SQL Modules node, right-click Northwind, and then click Database Connection. The Data Link Properties dialog box appears.
- Enter the name of the SQL Server computer that you will use and the log on information, and then click Test Connection. Ensure that the test connection is successful.
- Click OK to close the dialog box.
Step 2: test the schema by using XPath Query Tester
XPath Query Tester is a utility that comes with XML View Mapper. XPath Query Tester uses the XDR view schema that is opened in XML View Mapper. The results of the query are returned using the element hierarchy specified in the XDR view schema, enclosed in a <root> element. Use the following procedure to test the mapping schema:
- On the XML View Mapper Tools menu, click XPath Query Tester. The Schema Load Log dialog box appears.
- Click OK. The XPath Query Tester input dialog box appears.
If the database has not been connected, you will be prompted with a message box. Click OK to enter the Data Link Properties dialog box, enter the SQL Server name and logon password, and then click OK. The XPath Query Tester input dialog box appears.
- Type the query Order[@OrderID="10248"] in the XPath Query box, but do not click Execute yet because we need to run SQL Profiler to capture the SQL query string.
- Click Start, point to Programs, point to Microsoft SQL Server, and then click Profiler.
- Start a new trace in the SQL Profiler screen and accept all the defaults for the new trace.
- Click Execute in XPath Query Tester to trigger the SQL trace. The XML data will be retrieved and displayed.
Step 3: get the FOR XML EXPLICIT query and create an XML query
- Stop the trace. In the SQL Profiler window, find and select the application name SQL Server XML Mapper. The trace result shows the desired SELECT . . . FOR XML EXPLICIT command that was generated by the XPath query.
- Open Notepad and enter the following text:
<Order xmlns:sql='urn:schemas-microsoft-com:xml-sql'> <sql:query> BODY </sql:query> </Order> - Cut and paste the trace result from the SQL Profiler window to replace the BODY text in the Notepad file.
- Save the Notepad file as C:\RetrieveXMLOrder.xml. This XML query file will be used in the sample to retrieve order data (OrderID = 10248) as an XML document. By globally replacing the OrderID "10248" in the query with another OrderID, the query can be used to retrieve details of another order.
- Close all open application screens.
Step 4: create an Updategram
- Open Notepad and type the following Updategram:
<?xml version="1.0"?> <updateorder xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync> <updg:before> <_x005B_Order_x0020_Details_x005D_ OrderID="10249" ProductID="14" /> </updg:before> <updg:after> <_x005B_Order_x0020_Details_x005D_ UnitPrice="$15.88" /> </updg:after> </updg:sync> </updateorder>This Updategram updates the unit price for order item OrderID = 10249 and ProductID = 14. It is equivalent to the following Transact-SQL statement:
Update [Order Details] Set UnitPrice=15.88 Where OrderID=10249 And ProductID=14
- Save the file as C:\UpdateOrder10249.xml.
Step 5: create a WSC component
A WSC component, XMLOrder.wsc, has been created and provided with the sample in the Samples\XMLViewMapper folder. You need to register the component by using regsvr32.dll or by right-clicking the WSC file in Windows Explorer and clicking Register.
The WSC component has three methods:
- UpdateOrderDetail(UpdategramFile). Executes the Updategram contained in the file UpdateOrder10249.xml
- GetXMLOrder(OrderID, XMLQueryFile). Retrieves the XML order for a given OrderID using the XML query contained in the file RetrieveXMLOrder.xml
- WriteToFile(Document, FileName). Writes the retrieved data to a text file specified in the FileName parameter
Step 6: create a BizTalk Orchestration schedule
A BizTalk Orchestration schedule will be created that performs three tasks:
- Run an Updategram to modify the unit price for order item OrderID=10249 and ProductID=14
- Retrieve all order items for OrderID=10249 by using a SELECT . . . FOR XML EXPLICIT query
- Write the retrieved XML data into a file
The following illustration shows the BizTalk Orchestration schedule.
Figure 7. Click thumbnail for larger image.
The main steps for building the sample orchestration schedule are:
- Drag three Action shapes onto the process and name them Updategram, Retrieve XML Data, and Write Document to File. Add an End shape to the flowchart, and then connect the Action shapes to complete the business process flowchart as shown in the preceding illustration.
- Bind XMLOrder.wsc to the Action shapes by using the Windows Script Component Wizard.
- Add four constants on the orchestration data page. The following illustration shows details of the constants.
Figure 8.
- On the data page, connect the constants to the corresponding input parameters of the three data tables. The following illustration shows the completed data flow page.
Figure 9. Click thumbnail for larger image.
- Save the orchestration schedule to the Sample\XMLViewMapper folder as TestXMLOrder.skv.
- Compile the orchestration schedule to generate the executable file TestXMLOrder.skx.
- Save the file to the Sample\XMLViewMapper folder.
Step 7: run the sample
- Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager. Ensure that the item [OrderID=10249, ProductID=14] in the Order Details table in the Northwind database has a unit price different from 15.88 (this is the value to be changed by the Updategram in the orchestration). Alter the value if it already has a value of 15.88.
- Open Windows Explorer and browse to the folder C:\Program Files\Microsoft BizTalk Server\SDK\XLANG Tools\. Double-click XLANGMon.exe. The XLANG Monitor screen appears.
- Open Windows Explorer and browse to the TestXMLOrder.skx file. Right-click the file and drag it onto the XLANG Scheduler node in the XLANG Monitor screen. This runs the orchestration schedule.
- Verify that a new XML file, C:\XMLOrderDetails.xml has been created. This file contains XML data retrieved by using the XML query in the file RetrieveXMLOrder.xml. The XML data represents the order with OrderID=10249.
- Verify that the unit price for the order item [OrderID=10249, ProductID=14] has been modified to 15.88.
Leveraging Stored Procedures and DTS in SQL Server 2000
This section describes how to use BizTalk Server 2002 to orchestrate workflow among disparate processes. By using the BizTalk Server 2002 Orchestration Designer, you can sequence tasks implemented in SQL Server stored procedures and Data Transformation Services (DTS) packages.
Sample: calling stored procedures from BizTalk Orchestration
This section demonstrates how to call SQL Server stored procedures from BizTalk Orchestration. Because of the number of components involved, we will use a structured example. The following steps are outlined:
- Define a SQL Server database table and a stored procedure to call from BizTalk Orchestration.
- Use the Windows Script Component Wizard to create a component file, BTS2SQL.wsc, which encapsulates the script code to access SQL Server. This component will have one method, ExecSQL, which will be called from BizTalk Orchestration.
- Create a BizTalk Orchestration schedule, TestSQL.skv, which has one action. This action will be bound to an implementation that calls the ExecSQL method in the BTS2SQL.wsc component.
- Run the sample.
Note All sample files for this section are included in the Samples\StoredProcsandDTS\BTS2SQL directory.
Step 1: define and test the LogIt stored procedure
Dependencies for the sample include a SQL Server stored procedure to call and a mechanism for verifying that the procedure was called. Specifically this includes:
- SQL Server database table—WorkflowMessages
- SQL Server stored procedure—LogIt
The following script, CreateTableAndLogIt.sql creates the required database table and stored procedure in Pubs, and is included in the Samples\StoredProcsandDTS\BTS2SQL directory:
use pubs go create table WorkflowMessages ( message varchar(1024) not null, logged datetime not null default (GetDate()) ) go Create PROCEDURE LogIt @strMsg varchar(1024) AS insert into WorkFlowMessages (message) values (@strMsg) grant select on WorkflowMessages to public grant execute on LogIt to public go
- Using SQL Query Analyzer, define the databases, table, and stored procedure by using the preceding script.
- Using SQL Query Analyzer, run the LogIt stored procedure, passing in a string as a parameter:
EXEC LogIt 'Test String'
- Browse the WorkflowMessages table to verify that the procedure worked properly.
Step 2: define and test the BTS2SQL component
To access SQL Server from BizTalk Orchestration, we will define a Windows Script Component, BTS2SQL.wsc, with one method, ExecSQL. This method takes two parameters, a connection string and an SQL query. It uses ADO to connect to the database and execute the query. The following code implements the script component. The shell was generated with the Windows Script Component Wizard. The ExecSQL code was added by hand.
<?xml version="1.0"?>
<component>
<?component error="true" debug="false"?>
<registration
description="BTS2SQL"
progid="BTS2SQL.WSC"
version="1.00"
classid="{71f80b28-2695-4220-bd77-c21abaca02cb}"
>
</registration>
<public>
<method name="ExecSQL">
<PARAMETER name="sConn"/>
<PARAMETER name="sSQL"/>
</method>
</public>
<script language="VBScript">
<![CDATA[
function ExecSQL(sConn, sSQL)
Dim cn
Dim cmd
'Connect to the db and execute the SQL
Set cn = CreateObject("ADOdb.connection")
cn.Open sConn
Set cmd = CreateObject("ADOdb.command")
cmd.ActiveConnection = cn
cmd.CommandText = sSQL
cmd.Execute
'Clean up
Set cmd=nothing
Set cn=nothing
ExecSQL = ""
end function
]]>
</script>
</component>
- Define the WSC file and add the code to the ExecSQL method.
- Register the component by using regsvr32.dll or by right-clicking the WSC file in Windows Explorer and clicking Register.
- Now we can test the component to ensure that it is functioning properly. The following VBScript code will test the component. Using Notepad, enter the following code and save the file as TestBTS2SQL.vbs:
dim o set o = CreateObject("BTS2SQL.WSC") o.ExecSQL "Provider=sqloledb; Data Source=(local);Initial Catalog=Pubs; Trusted_Connection=yes;", "exec LogIt 'hello world!' " set o = nothing - Test the activation and execution of BTS2SQL.wsc by running TestBTS2SQL.vbs and then verifying that a row has been added to the WorkflowMessages table in the Pubs database
Step 3: create and test the BizTalk Orchestration schedule
A simple BizTalk Orchestration that has one action is suitable.
This schedule should direct the run-time environment to instantiate a BTS2SQL.wsc component and call the ExecSQL method, passing in parameter values defined in the ExecSQL message. In this test, the values defined for the parameters (sConn and sSQL) on the data page within Orchestration Designer are connected to the values of the parameters in the ExecSQL message.
When creating the sample orchestration, the following points are important to making the sample functional:
- Make sure that the Implementation shape is attached to the BTS2SQL.wsc component. This can be done by using the Script Component Binding Wizard.
- Two constants should be included on the orchestration data page: ConnectString and SQLString. These should be mapped to the ExecSQL_in message by drawing a line from the constant to the input parameter.
Constant name Constant value ConnectionString Provider=sqloledb; Data Source=(local); Initial Catalog=Pubs; Trusted_Connection=yes;SQLString exec LogIt 'Sample Insert from BizTalk'
The result should look like the included sample in the TestSQL.skv file and the compiled form in TestSQL.skx.
Step 4: Run the BizTalk Orchestration schedule
The following VBScript code is used to run the XLANG schedule. The code is provided in the sample as TestSQLSKV.vbs.
Dim objSked Dim strPath strPath = WScript.ScriptFullName strPath = Mid(strPath, 1, InStrRev(strPath, "\")) strPath = "sked:///" & strPath & "TestSQL.skx" set objSked = GetObject(strPath) msgbox "Started " & strPath Set oSked = Nothing
To test the component from the BizTalk Orchestration schedule, perform the following steps:
- In Windows Explorer, double-click TestSQLSKV.vbs.
- After the message box appears, click OK and then go into SQL Enterprise Manager or SQL Query Analyzer to verify that Sample Insert from BizTalk was inserted in the WorkFlowMessages database table.
Sample: calling DTS from BizTalk Orchestration
One of the more efficient ways to access DTS packages from outside SQL Server Enterprise Manager is through the SQL Server DTS package object model. This object model ships with SQL Server 2000 and is a complete set of objects to define and run DTS packages through COM interfaces. To demonstrate how to call DTS packages from BizTalk Orchestration, the following steps will be outlined in this section:
- Define a DTS package named Copy Titles that copies data from one table to another.
- Use the Windows Script Component Wizard to create a component file, BTS2DTS.wsc, which encapsulates the script code to access DTS. This component will have one method, RunDTSPackage, which will be called from BizTalk Orchestration.
- Create a BizTalk Orchestration schedule, TestDTS.skv, which has one action. This action will be bound to an implementation that calls the RunDTSPackage method in the BTS2DTS.wsc component.
- Create a VBScript file to run the BizTalk Orchestration schedule.
- Test the BizTalk Server to SQL Server communication.
Note All sample files for this section are included in the Samples\StoredProcsandDTS\BTS2DTS directory.
Step 1: define and test the DTS package
For testing and demonstration purposes, a DTS package is required to run along with a mechanism for verifying that it was called. This package will be defined as Copy Titles. Functionality provided by the Copy Titles package will duplicate rows from the Titles table of the Pubs database to the Northwind database. The package will have two connection objects, one pointing to the Pubs database and the other pointing to the Northwind database. The package will also have two tasks. First, a Transform Data task will copy the data from the Pubs database to the Northwind database, and then an Execute SQL task will log an entry into our WorkFlowMessages table in the Pubs database to indicate that the data was copied.
Perform the following steps to create the package:
- Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
- In the tree control in the left pane, expand Microsoft SQL Servers, expand SQL Server Group, expand [your computer name], expand Data Transformation Services, and then click Local Packages.
- In the right pane, right-click and then click New Package.
- Create a Microsoft OLE DB Provider for SQL Server and use the Connection Properties to name it Pubs and connect it to the Pubs database.
- Repeat the preceding steps to create a second Microsoft OLE DB Provider for SQL Server object, name it Northwind, and connect it to the Northwind database.
- Drag a Transform Data task to the design, where Pubs is the source and Northwind is the destination. On the property page, set the Source tab to the Titles table, the Destination tab to the Titles table (which might need to be created), and use the Transformation tab to select all records.
- Drag an Execute SQL task to the design surface. On the property page, select the Pubs database, enter Log the Count for the description, and enter the following code as the SQL statement. This will count the number of rows in the Titles table in the Northwind database and write a message to the WorkFlowMessages table in the Pubs database.
declare @nrows int select @nrows = count(*) from Northwind..titles declare @sMsg varchar(80) select @sMsg = 'There are ' + convert(varchar(8), @nrows) + ' in the Northwind titles table' exec pubs..LogIt @sMsg
- Click OK to close the property page.
- From the design surface, select the Northwind connection object and the Log the Count task. On the Workflow menu, click On Completion.
- Save the package in SQL Server, as Copy Titles.
- For testing, run the package by clicking Execute on the Package menu. After it is complete, go into SQL Query Analyzer and verify that a message was written to the WorkFlowMessages table and that the Titles table in the Northwind database is populated. Each time you run the package, you should see another message in the WorkFlowMessages table and the size of the Titles table in the Northwind database should grow.
Step 2: define and test the BTS2DTS component
To access DTS from BizTalk Orchestration, we will define a Windows Script Component that encapsulates the code to run DTS tasks. We will define a component named BTS2DTS.wsc with one method, RunPackage. This method takes one parameter, the name of the package to run. The following code implements the script component. The shell was generated with the Windows Script Component Wizard. The RunPackage code was added by hand.
The only part of this script you might need to change is the UseTrustedConnection property on the objExecPkg object. If you typically use a user name and password to gain access to SQL Server, it might be more convenient to set the UseTrustedConnection property to False, and set the ServerUserName and ServerPassword properties of the objExecPkg object to the appropriate values. Similarly, if the instance of SQL Server is on another computer, set the ServerName property appropriately.
<?xml version="1.0"?>
<component>
<?component error="true" debug="false"?>
<registration
description="BTS2DTS"
progid="BTS2DTS.WSC"
version="1.00"
classid="{a9a7f917-35ef-4d45-93f4-3bc935ec75d0}"
>
</registration>
<public>
<method name="RunPackage">
<PARAMETER name="sPackageName"/>
</method>
</public>
<script language="VBScript">
<![CDATA[
function RunPackage(sPackageName)
Dim objPackage
Dim objStep
Dim objTask
Dim objExecPkg
'Create the step and task. Specify the package to be run, and link the
step to the task.
Set objPackage = CreateObject("DTS.Package2")
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecPkg = objTask.CustomTask
objExecPkg.UseRepository = False
objExecPkg.UseTrustedConnection = True
objExecPkg.PackageName = sPackageName
objExecPkg.Name = "ExecPkgTask"
Set objStep = objPackage.Steps.New
objStep.TaskName = objExecPkg.Name
objStep.Name = "ExecPkgStep"
objStep.ExecuteInMainThread = True
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask
'Run the package
objPackage.FailOnError = True
objPackage.Execute
'Release references. Releases must be done before UnInitialize.
Set objExecPkg = Nothing
Set objTask = Nothing
Set objStep = Nothing
objPackage.UnInitialize
end function
]]>
</script>
</component>
To register and add code to the component
- Define the WSC file and add the code to the RunPackage method.
- Register the component by using regsvr32.dll or by right-clicking the WSC file in Windows Explorer and clicking Register.
Step 3: create and test a BizTalk Orchestration schedule to exercise BTS2DTS
To create this workflow in Orchestration Designer:
- Draw the business process action ("Test DTS Package") by dragging the Action shape from the left palette to the design surface. Add an End shape to the design surface and sequence the three steps: Begin, Test DTS Package, End.
- Drag the Script Component shape onto the implementation side and browse to BTS2DTS.wsc by using the Script Component Binding Wizard.
- Connect the business process action to the implementation port ("Port_1") by using the Method Communication Wizard to create a new message and to make a synchronous method call.
This schedule directs the run-time environment to instantiate a BTS2DTS.wsc component and call the RunPackage method, passing in parameter values defined in the RunPackage message. For this test, we will define values for the parameter (PackageName) on the data page of the Orchestration Designer and then connect the values to the parameters in the RunPackage message.
- Go to the data page in Orchestration Designer and right-click the Constants block. From there, add one constant:
Name: DTSPackageName Value:Copy Titles
- Map this constant to the RunPackage_in message by drawing a line from the constant to the input parameter.
- This file should be saved as, and appears in the sample directory as, the TestDTS.skv file and is compiled into the TestDTS.skx file.
Step 4: create a VBScript file to run the BizTalk Orchestration schedule
The following VBScript code will request the XLANG runtime to start the orchestration. Sample file TestDTSSKV.vbs is provided.
Dim objSked Dim strPath strPath = WScript.ScriptFullName strPath = Mid(strPath, 1, InStrRev(strPath, "\")) strPath = "sked:///" & strPath & "TestDTS.skx" set objSked = GetObject(strPath) msgbox "Started " & strPath Set oSked = Nothing
Step 5: test the BizTalk Server to SQL Server communication
Now you are ready to test the component from the BizTalk Orchestration schedule.
- In Windows Explorer, double-click TestDTSSKV.vbs.
- After the message box appears, click OK, and then go into SQL Server Enterprise Manager or SQL Query Analyzer to verify that a message was inserted in the WorkFlowMessages table and the number of rows in the Titles table has increased.
References
Implement SAX2 Classes in VB.
Malcolm, Graeme. Programming Microsoft SQL Server 2000 with XML. Redmond, WA: Microsoft Press, 2001.
XML for SQL Documentation in Microsoft SQL Server 2000 Web Release 1 online documentation.
XML View Mapper Documentation in Microsoft SQL Server XML View Mapper online documentation.



