Export (0) Print
Expand All
20 out of 30 rated this helpful - Rate this topic

Working With BizTalk Adapter for SQL Server

Todd K. Uhl

Avanade Inchttp://www.avanade.com

May 2005

Applies to: Microsoft BizTalk Server 2004

Summary: This document provides general knowledge about the Microsoft BizTalk Adapter for SQL Server. It gives suggestions about when to use the SQL transport and how to retrieve, insert, update, and delete data. It also includes code samples. (30 printed pages)

Download sample code

Many Microsoft BizTalk Server implementations require some level of interaction with a database to satisfy certain business processes. This typically involves querying a database for information to make more processing decisions, inserting or updating records in a database based on data received in a message, or periodically polling the database for information to act on. There are a few options available to you when you have to exchange data between a Microsoft SQL Server database and BizTalk Server 2004. One of these available options is the Adapter for SQL Server.

The Adapter for SQL Server is not new to BizTalk. It was introduced as an add-in to BizTalk Server 2002, but is now one of the default transports included with BizTalk Server 2004. This new version includes additional functionality, in addition to several welcome enhancements over the earlier version. The following functionalities are available through this transport:

  • Polling the database to retrieve data using SQL queries or stored procedures.
  • Using updategrams to insert, update, or delete information in the database.
  • Solicit a response from a stored procedure to retrieve data.

Since the adapter lets you create an XML view of your existing relational data, and lets you act on that data as if it were an XML message, it becomes a clear choice when having to share data between SQL Server and BizTalk Server 2004.

This document discusses when the Adapter for SQL Server is a good choice to use, covers some of the basic concepts of the adapter, and then provides three examples of how to use the adapter in common scenarios.

The Adapter for SQL Server is a good choice when you have to share data between BizTalk Server 2004 and SQL Server databases. The adapter is appropriate for data retrieval and update scenarios with messages that are both small and large. Many business processes must interact with the database in a non-transactional manner, retrieving or inserting data in unrelated steps. While you can use the adapter with a single unit of work in a transactional manner (the processes within that unit of work can be transactional), there may be better options when the overall business process requires the units of work to be transactional as a whole, and would require writing custom components to handle this.

The Adapter for SQL Server handles two different functions, providing the means for BizTalk to receive data, and providing the means for BizTalk to send data to SQL Server. These functions are referred to in BizTalk Server 2002 Help as the Receive adapter and Send adapter, respectively. You use the receive function to retrieve information from SQL Server by periodically invoking a SQL command. This SQL command is formatted as either a SELECT statement or as a stored procedure call. The result set is then submitted to BizTalk in the form of an XML message. You use the send function to send an XML message to SQL Server in the form of a stored procedure call or an updategram. An updategram maps XML nodes against database columns in SQL Server. It is the structure of the XML message representing the updategram that determines whether the data will be inserted, updated, or deleted in the database. Making a call to a stored procedure can return a response document that contains the result set formatted as an XML message. When you send an updategram, SQL Server can optionally return a response document to BizTalk that contains the success status of the operation.

SQL Receive Adapter

The Receive adapter functions as a polling service to retrieve information from SQL Server. There are several properties of a receive location using the SQL transport type that can be configured to control the action of the adapter. The SQL command is a property used to store either a SELECT statement or a stored procedure call. This SQL command is then invoked by the Adapter Framework Scheduler at an interval set by the Polling Interval property. Note that the Receive adapter has a limit of 60 messages per minute. One way to get around this is to set the Poll While Data Found property to True so the adapter continuously polls until there is no more data to retrieve at each polling interval. You should keep this in mind when determining the throughput that you want as you may have to return multiple rows at a time in a single message. One of the examples included with this document demonstrates how to deal with messages returned from SQL Server that contain more than one record in the result set. The Receive adapter can only be bound to one-way receive ports.

SQL Statements

When you use SQL statements with the Receive adapter, they must be in the form of a SELECT statement. Since the Receive adapter is a polling service, and you configure the SQL command at design time, you cannot use it to run dynamic queries. Additionally, only static values in the WHERE clause are supported.

SQL statements used with the Receive adapter use slightly different syntax than a typical SQL statement. In order to return a result set from SQL Server in the form of an XML message, you must add the syntax for xml auto or for xml auto, elements to the end of the SQL statement as follows:

SELECT * FROM Employees WHERE LastName = 'Davolio' for xml auto
ms935658.note(en-US,BTS.10).gifNote
The adapter does not support the syntax xml raw.

The syntax for xml auto returns an XML representation of the result set with each record that contains the table name as the element name, and each column returned as an attribute value. The SQL statement above would return a result set in the following format when issued from the Receive adapter:

<root>
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy" …
</root>

An additional property of the Receive adapter set at design time is the Document Root Element Name. You would replace the root element <root> in the above example with the value designated in the Document Root Element Name property.

The syntax for xml auto, elements also returns an XML representation of the result set; however, the column data is returned as child elements instead of attribute values. This is important to remember as your schemas representing the messages submitted by the Receive adapter must reflect these differences.

Stored Procedures

Just as the Receive adapter supports SQL statements with static WHERE clauses, it also supports calls to stored procedures with static parameter values. An example of a supported stored procedure call follows:

exec [Sales by Year] @Beginning_date = '1996-06-01', @Ending_Date = '1996-07-10'

Similar to SQL statements, the adapter will run the SQL command at the specified polling interval. Therefore, at each interval the adapter will retrieve the same result set. Depending on your business requirements, this may not be what you want. When you use a stored procedure, you may code your procedure in such a way that the records you select are flagged, or possibly deleted so they are not selected in future calls. An example of such a stored procedure is as follows:

CREATE PROCEDURE SubScriptionProcedure
AS
DECLARE @Process_Date DateTime
SET @Process_Date=GetDate()
Update SubScription Table Set ProcessedDate=@Process_Date Where ProcessedDate is NULL
SELECT SubscriptionId, Lastname, Firstname, MagazineName, DateOrdered FROM SubscriptionTable WHERE ProcessedDate=@ProcessDate FOR XML AUTO, ELEMENTS
GO

The above example will first set a variable to the current date and time. It then updates the ProcessedDate field to the current date and time for any record where this field is null. The last step is to select the records that just had their ProcessedDate updated and this result set is what is returned to the Receive adapter.

ms935658.note(en-US,BTS.10).gifNote
The Receive adapter does not support stored procedures that return multiple result sets. However, the adapter does support multiple records in a single result set.

Stored procedures must follow the same syntax rules as SQL statements to return result sets as XML. Again, this additional syntax is either for xml auto or for xml auto, elements as explained above.

SQL Send Adapter

The Send adapter supports two separate functions. You can use it to send dynamically generated updategrams to SQL Server to insert, update, or delete records. Optionally, you can have the adapter return an XML message that states the success status of the action. Additionally, you can use the adapter to call stored procedures that return a result set back to BizTalk. When you call a stored procedure, any parameters can be passed in by the XML message. The Send adapter may be bound to a one-way send port, or a two-way solicit-response send port.

ms935658.note(en-US,BTS.10).gifNote
The Send adapter does not support the use of SQL statements. Calls to stored procedures or use of updategrams are the only supported options.

Updategrams

An updategram is a specially formatted XML fragment made up of several blocks, each of which serves a specific purpose. It is the arrangement of these blocks within the fragment that determines whether the updategram will insert, update, or delete the record. The basic structure of the updategram appears as follows:

<root>
<sync>
<before>
<TableName col1='value' col2='value' …/>
</before>
<after>
< TableName col1='value' col2='value' …/></after>
</sync>
</root>

The following describes the functionality of the individual blocks:

<before>

The <before> block is similar to the WHERE clause in a SQL statement. This identifies the records in their current existing state.

<after>

The <after> block indicates the new state of the records.

<sync>

The <sync> block is a container for the <before> and <after> blocks, and indicates one unit of transaction. It is possible for the <sync> block to contain more than one pair of <before> and <after> blocks. If any one of these fail, everything in the <sync> block will be rolled back. It is also possible for the updategram to contain more than one <sync> block. Since each <sync> block is considered its own unit of transaction, they can fail independently of the each other.

As mentioned earlier, it is the arrangement of the blocks that determines whether the updategram inserts, updates, or deletes the records.

When a message appears with only an <after> block, and no corresponding <before> block, the updategram performs an insert operation. Looking at the basic structure above, the column names and the values to insert are represented as attributes of the <after> block.

When a message appears with only a <before> block, and there is no corresponding <after> block, the updategram performs a delete operation. As mentioned earlier, the <before> block acts as the WHERE clause in a SQL statement. Again, looking at the earlier structure, any columns indicated with values matching the attribute values indicate a record to be deleted.

A message that contains a <before> block, in addition to a corresponding <after> block, indicates an updategram that performs an update operation. Again, the <before> block would behave like the WHERE clause, and the records would be updated with the new values indicated by the values of the column attributes.

To perform multiple operations, the updategram can have more than one <sync> block, and also more than one set of <before> and <after> blocks within the <sync> block. For example, you could insert one purchase order record and multiple line item records using one updategram. This is a powerful feature, and it is demonstrated in the examples included with this document. For performance reasons, if the business process requires multiple operations across database tables, it is best to encapsulate these in stored procedures instead of updategrams. You can optimize stored procedures. You compile store procedures instead of interpreting them like updategrams.

Stored Procedures

In addition to the updategram support, the Send adapter also supports calling stored procedures. You can pass dynamic parameters to these stored procedures, in addition to returning a result set in the form of an XML message. These parameters are passed in as elements or attributes in the message. When returning a result set, a solicit-response send port is used to call the stored procedure and accept the return message.

As mentioned earlier, you must add for xml auto or for xml auto, elements to your stored procedures to produce a result set in the form of an XML message.

Generated Items

Up to this point, I have discussed receiving information from SQL Server by using the Receive adapter, and by using the Send adapter for running stored procedures with dynamic parameters or running updategrams. All this depends on passing an XML message to SQL Server, and possibly receiving a response back in the form of an XML message. The format of these XML messages must be very specific, and generating them by hand would be very difficult. Fortunately, the Adapter for SQL Server and BizTalk take care of this for you.

BizTalk gives you the ability to add Generated Items to your project. You do this by right clicking your project, and selecting Add Generated Items from the context menu. This lets BizTalk generate a schema based on the parameters supplied to the adapter. When selecting the Adapter for SQL Server, the resulting SQL Transport Schema Generation Wizard prompts you for information such as target namespace, port type, document root element name, and so on. The adapter then uses these parameters to interact with SQL Server, and generate a schema that reflects the result set based on the information that you supplied. A detailed description of this is provided in the examples included in this document.

The following section discusses the examples that are included with this document. The examples are not the only scenarios supported by the Adapter for SQL Server; however, they cover common scenarios that can be solved with the adapter. A readme file is included with the examples to explain how to install and run the examples. The discussions that follow describe in detail the steps that you must follow to re-create the examples.

The examples included with this document use the Northwind database that is included with SQL Server.

Business Scenario: Northwind Traders has recently implemented several enterprise packages to handle inventory control, human resources, and so on. Northwind wants to integrate these new packages with their current SQL Server-based systems. Northwind has decided to use BizTalk Server 2004 as the enterprise integration solution. This document discusses the following scenarios in detail:

  • Issue Select - The new HR system must be able to interrogate the existing employee information stored in SQL Server to determine which employees might reside in a specific country/region. The new Sales Force automation tool uses this information. The information must be returned as an XML message per employee.
  • Retrieve New Orders - The new Order Fulfillment system is regularly updated with new orders that are entered into the existing SQL Server database. The business requirement is that new orders must be migrated to the Order Fulfillment system within 10 minutes of being generated in the existing system. These new orders are sent to the Order Fulfillment system.
  • Insert New Order - New orders will be generated by a new Sales Support system in addition to the existing Order Generation system. To keep the systems synchronized, new orders and changes to existing orders will be sent to the existing SQL Server database solution. These changes will be handled in the form of updategrams, and will consist of order and order detail information. These messages will update the Order and Order Detail tables in SQL Server by using one message for the entire transaction.

Issue Select

For this particular business process, you must be able to return to the ERP system employees that are staffed in various countries/regions. The ERP system sends a request in the form of an XML messages for employees in a specific country/region. An orchestration will process the file, using a solicit-response port to send a "run stored procedure" message to SQL Server, and accept the result set in the form of an XML message. The result set is then broken into individual messages, one per employee record, and sent back to the ERP system.

Before you start, you must update the EmployeesByCountry stored procedure. This will require two additional pieces of syntax as described earlier in the document. First, for the procedure to return XML mapping each column to an element in the XML, you must add for xml auto, elements to the end of the stored procedure. The other syntax you have to add will be temporary. For the adapter to create the correct schema when you add a generated item for the adapter, you have to add the syntax xmldata to the end of the existing code. This tells the stored procedure to add the schema data representing the table to the output XML. The adapter uses this information to generate the schema when you add a generated item. The stored procedure should now look as follows:

CREATE PROCEDURE EmployeesByCountry @Country nVarChar(15) 
AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName
FROM Employees
WHERE Employees.Country = @Country for xml auto, elements, xmldata
GO

After you add the generated item, you will remove the xmldata at the end of the procedure since you don't want the schema information to be in the message.

Now that you have added the syntax to the stored procedure, you are ready to add the schema representing the request and response. Use the Add Generated Items wizard to create this schema by using the following steps.

To add generated items

  1. Right-click the project, click Add, and then click Add Generated Items.
  2. In the Add Generated Items wizard, in first step of the wizard, under the Generated Schema Files category, select the Add Adapter template, and then click Next.
  3. On the Select Adapter page, select SQL, keep the defaults for the other options, and then click Next.
  4. On the Database Information page, click Set to enter your connection information. You will use this information to create the connection string to the database. Fill out the information about the Data Link Properties dialog box, and then click OK. Click Next when you are returned back to the Database Information page of the wizard.
    On the Schema Information page, type the information the adapter will use to add some additional information to the schema representing the table that is generated by SQL Server.
    Figure 1 SQL Transport Schema Generation Wizard - Schema Information page
    ms935658.Local_1498348371_bts_wp_btsadpt_sql01(en-US,BTS.10).gif

  5. For the Target namespace, you will use "http://SQLAdapterExplore.IssueSelect." Remember this value as you will use it again when supplying the port properties. Since you are sending a message and expecting a response, you will select Send port as the port type. This will let you bind to a solicit-response port. The Request root element name will be set to GetEmployees, and the Response root element name will be set to ReturnEmployees. Click Next.
  6. On the Statement Type information page, you have the option of selecting Updategram or Stored Procedure. Since you are selecting records instead of updating them, select Stored Procedure, and then click Next.
  7. On the Statement Information page, select the SQL statement to run, as well as the input parameters.
    Figure 2 SQL Transport Schema Generation Wizard - Statement Information page
    ms935658.Local_1498348368_bts_wp_btsadpt_sql02(en-US,BTS.10).gif

    Notice that the first column (the Value column) in this figure has a value of UK in it, and its check box is selected. You can set the value of the parameter by clicking in the first column to open the text box. The reason you enter a value for the parameters is so that the adapter can evaluate the stored procedure based on it returning a result set, and include the columns in the tables as attributes or elements of the response message. If no values are entered, it creates a schema with a Success node under the root response node, and this would be used to indicate the status of the stored procedure. For this example, you want the result set to be returned, not the success indication of the stored procedure.
  8. Click Generate to have the adapter evaluate the stored procedure, and generate the resulting schema, and then click Next.
  9. You have now completed the Add Generated Item wizard, click Finish to finish.

The Add Generated Items wizard has created two new items in the project. The first item is the multi-part message schema representing the call to the stored procedure, and the result set that will be returned. By default, this schema is named SQLService.xsd. You can optionally rename this schema to something more appropriate to your project. The schema appears as follows:

Figure 3 SQLService.xsd schema

ms935658.Local_1498348369_bts_wp_btsadpt_sql03(en-US,BTS.10).gif

Notice that GetEmployees was previously set as the request node, as well as ReturnEmployees you previously set as the response node. The Employees node name is determined by the table name, and the child nodes are determined by the column names. When the response is returned, there will be an Employees node for each record in the result set.

The second item added to the project by the Add Generated Items wizard is a new orchestration, which by default, is named BizTalk Orchestration.odx. Again, you may want to rename this orchestration to something more appropriate for your project. This orchestration contains the Begin and End shapes, as well as some port type and multi-part message type information based on the schema that was generated. There may be times when you need several schemas based on the Adapter for SQL in one orchestration, or you might have an existing orchestration that you want to use. You do not have to use the default orchestration that is created for you. You can use the port types and multi-part message types in the generated orchestration as an example to add the generated schema to the orchestration of your choice. For information about this process, see Microsoft BizTalk Server 2004 Help.

Creating the Orchestration

As mentioned in the example description, you want to retrieve employees staffed in a particular country/region. A common scenario when you are using the Adapter for SQL to retrieve data from SQL Server is the need to handle each record in the result set as its own message instead of dealing with the entire result set which is returned as one XML message. There are a few ways to accomplish this, and each involves getting a handle on each record in the message. In the orchestration that is included in this example, iterating through a nodelist is used to retrieve each employee message. Using XPATH is another common solution, and many examples can be found on the Internet.

The business process for this scenario is as follows:

  1. The ERP system sends a request for employees in a particular country/region. This request is sent in the form of an XML message.
  2. The request must be transformed into a message you can send through the Adapter for SQL Server.
  3. The result set will be returned to the business process in the form of an XML message.
  4. For each employee returned, the following process will be repeated:
    • Transform the individual employee XML record into the ERP's employee schema.
    • Send the single ERP employee message to the ERP system.

At the top of the orchestration, notice that you receive the request message, and then transform this message into the request portion (GetEmployees) of the schema that was created when you used the Add Generated Item wizard.

Figure 4 Orchestration

ms935658.Local_1498348374_bts_wp_btsadpt_sql04(en-US,BTS.10).gif

After transforming the message, you want to send the request message to SQL Server through the adapter, and you want to receive the response back from SQL Server through the adapter. To accomplish this, you create a new port and use the request-response communication pattern. For the direction of communication, you send a request, and receive a response. The response message will be in the format of the response portion (ReturnEmployees) of the schema that was created when you used the Add Generated Item wizard.

After the message that contains the result set is returned, you have to loop through each employee record to create an individual employee message to send back to the ERP system.

Figure 5 Looping through each employee record

ms935658.Local_1498348375_bts_wp_btsadpt_sql05(en-US,BTS.10).gif

A loop shape is used to let us loop through each employee record in the response message. Since using a nodelist was the chosen method for looping through the records, the entire process has been encapsulated in an atomic scope. This occurs because a nodelist is non-serializable. The expression shape named GetItemCount, which is in the orchestration, but not included in the image above, has several important steps in it. In the first step, the response message is assigned to a variable of type System.XmlDocument. This will let you retrieve a nodelist based on an XPATH expression, which is the second step in the expression. The last step in the expression is to get the enumerator of the nodelist. This lets you loop through the nodelist using MoveNext() syntax in the loop shape.

Within the loop shape, you will get the current node based on a counter variable that is incremented with each loop. Then you set the message variable that represents the ERP's Employee schema equal to the current node's XML. This is achieved by loading the current node's outer XML into an XmlDocument and assigning that XML document to the message variable within a Message Assignment shape. The individual employee message can now be sent to the ERP system, and control is moved to the next record.

Retrieve New Orders

Northwind Traders has implemented a new Order Fulfillment system. For now, the existing SQL Server-based order fulfillment system will still be used to support some areas of the business. To keep the two systems in sync, the SQL Server-based system will be polled every 10 minutes to retrieve a result set of all new orders since the last polling interval. This result set will be returned in the form of an XML message. For this particular business process, the records only have to be retrieved from the old system and sent to the new system, so content based routing will be used instead of an orchestration. The new Order Fulfillment system requires individual order messages. To support this requirement, a receive pipeline will be used to split the inbound XML message that represents the result set that will probably contain multiple records (one for each new order). Each record will then be persisted to the MessageBox database as separate XML messages.

This example will rely on the SQL Receive adapter to poll the database. As mentioned earlier, the Receive adapter supports static SELECT statements, or stored procedures with static parameters. For this process, you will use a SELECT statement instead of a stored procedure to retrieve the records. Remember that because the same SELECT statement runs at each polling interval, a real scenario would want to flag or delete the records so they are not selected at each polling interval (an example of this can be found above in the Stored Procedures section of the SQL Receive Adapter).

To start, you have to generate the schema representing the orders retrieved by the Receive adapter. Use the Add Generated Items wizard to create this schema following the steps below.

To add generated items

  1. Right-click the project, click Add, and then click Add Generated Items.
  2. In the first step of the wizard, under the Generated Schema Files category, select the Add Adapter template, and then click Next.
  3. On the Select Adapter page, select SQL, leave the defaults for the other options, and then click Next.
  4. On the Database Information page, click Set to enter your connection information. This will be used to create the connection string to the database. Enter the information about the Data Link Properties dialog box, and then click OK. Select Next when you are returned back to the Database Information page of the wizard.
  5. On the Schema Information page, you will enter information the adapter will use to add some additional information to the schema representing the table that is generated by SQL Server. For the Target namespace, use "http://SQLAdapterExplore.RetrieveNewOrders." Since you are receiving a message, select Receive port as the port type. Set the Document root element name to NewOrders. Remember the Target namespace and root element name as these will be used when you create our Envelope schema. The window appears as follows:
    Figure 6 SQL Transport Schema Generation Wizard - Schema Information page
    ms935658.Local_1498348372_bts_wp_btsadpt_sql06(en-US,BTS.10).gif

  6. Select Next to move to the next step.
  7. On the Statement Type information page, you have the option of selecting Select Statement or Stored Procedure. Use Select Statement for this example. Click Next to continue.
  8. The next page is the Statement Information page. On this page, enter the SELECT statement that you want run at each polling interval. As indicated, you will not add the xmldata syntax as you did in the previous example. For this example, you want to select new orders, so you will select records in the Orders table as well as the related records in the Order Details table. The SELECT statement will appear as follows:
    SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Order.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPotsalCode, Orders.ShipCountry, Details.*
    FROM Orders Orders JOIN [Order Details] Details ON Orders.OrderID = Details.OrderID
    WHERE Orders.ShippedDate IS NULL for xml auto, elements
    
    
    Click Next to continue.
  9. You have now completed the Add Generated Item process, click Finish to finish.

The Add Generated Items process created two new items in the project. The first item is the message schema representing the result set that will be returned because of the SELECT statement. By default, this schema is named SQLService.xsd. You can optionally rename this schema to something more appropriate to your project. The other item that is created by default is a new orchestration that has a reference to the new schema, but is otherwise blank. You do not have to use this orchestration. Since content-based routing will be used in the example, no orchestration is required, and it can be excluded from the project. The new schema appears as follows:

Figure 7 New schema

ms935658.Local_1498348373_bts_wp_btsadpt_sql07(en-US,BTS.10).gif

Looking at the schema, you can see that the Orders element will repeat for each record that is returned by the SELECT statement. Additionally, Details will repeat within Orders for each detail that is associated with an order. The business requirement for this example is to return a single order message for each order record. To satisfy this requirement, you will create an envelope schema that the receive pipeline will use to identify what is the "containing" element (NewOrders). The records will then be disassembled into a single order messages based on this envelope. Use the following steps to create the envelope schema.

To create the envelope schema

  1. Right-click the project, click Add, and then click Add New Item.
  2. In the Template pane on the right, select Schema. In the Name box, change the name of the schema to NewOrderEnv.xsd, and then click Open.
  3. In the Editor window, select the <Schema> node in the left pane. In the Properties window, set the Target namespace to http://SQLAdapterExplore.RetrieveNewOrders. This same namespace was used for the schema created in the Add Generated Items step above. Make sure that these match.
  4. Set the Envelope property to True.
  5. Right-click the Root node and select Rename. Change the name to NewOrders.
  6. Set the Body XPath property of the NewOrders node by clicking the ellipse button [...]. In the context window, select the NewOrders node, and then click OK.
  7. Right-click the NewOrders node, click Insert Schema Node, and then click Any Element. Using the Any Element lets you create a location within your schema that corresponds to a location within your message where you are not sure which elements will appear. For this example, it will be the Orders node and all of its children.
  8. Right-click the <Any> node and set the Process Contents property to Lax.

At this point, the Envelope schema is configured. For it to work with the schema that represents the new orders, you will have to make some changes to the NewOrders schema. As the schema exists now, it represents a message with all of the orders in it. What you want to achieve is a single message for each order. You have to modify the schema to represent this single order message.

To modify the schema to represent the single order message

  1. Open the NewOrders schema.
  2. Select the Orders node, and delete any values in the Min Occurs and Max Occurs properties.
  3. Drag the Orders node above the NewOrders node until an up arrow appears, and then release the Orders node.
  4. Delete the NewOrders node.

Your schema should now appear as follows shown in Figure 8.

Figure 8 Modified schema

ms935658.Local_1498348378_bts_wp_btsadpt_sql08(en-US,BTS.10).gif

Now that your schemas are completed, the next step will be to create a pipeline that references your envelope schema and your single order schema. This pipeline will apply the envelope to the inbound message during the XML Disassemble stage, and create the single order message.

To create the single order pipeline

  1. Right-click your project, click Add, and then click Add New Item.
  2. On the context menu, select Receive Pipeline in the Template pane.
  3. Name the pipeline NewOrderSplit.btp, and then click OK.
  4. In the pipeline editor, drag a XML disassembler shape to the Disassemble stage.
  5. Select the XML disassembler shape, and then click the ellipse button [...] in the Document schemas property. On the context menu, select the schema labeled <your project>.SQLService (this assumes that you did not change the name of the schema added as a generated item), and then click OK.
  6. Select the ellipse button [...] in the Envelope schemas property. On the context menu, select the schema labeled <your project>.NewOrderEnv, and then click OK.
  7. Save your pipeline.

At this point, you will want to build and deploy your project. Since you are using content-based routing to transport the message, you will set up a new receive port with a receive location, and a new send port.

To set up the receive location

  1. Using the BizTalk Explorer, create a new receive port and name it SQLAdapterExplore_NewOrders.
  2. Add a receive location to the receive port and name it ReceiveNewOrders.
  3. Set SQL as the transport type, and then click the ellipse button [...] in the Address (URI) property.
  4. Set the properties as shown in Table 1:

    Table 1 Property values

    1. Property
    1. Value

    Poll While Data Found

    False

    Polling Interval

    5

    Polling Unit of Measure

    Minutes

    Connection String

    Explained below

    Document Root Element Name

    NewOrders

    Document Target Namespace

    http://SQLAdapterExplore.RetrieveNewOrders

    SQL Command

    Use the same SELECT statement as step 8 of Add Generated Item above.

    To set the Connection String property, select the ellipse button [...]. In the Data Link Properties dialog box, enter the connection information to Northwind database. You have the option of using either integrated security, or using a specific user name and password. If you choose to use integrated security, the adapter's host service account credentials will be used. To determine what the credentials are, start the BizTalk Administration Console. Under Console Root\Microsoft BizTalk Server 2004, expand Hosts. Select the host that the Adapter for SQL Server is configured to run under (BizTalkServerApplication if you have installed everything using the default values). In the right pane, right-click the server and select Credentials. Depending on the security of your databases, you may have to grant specific privileges to this account. For the Northwind database, you should not have to make any changes.
    ms935658.note(en-US,BTS.10).gifNote
    The Adapter for SQL Server does not support dynamic port configuration. Any connection information must be hard coded at design time.

    The URI field will be filled in for you based on the values you entered in the Connection String property. You can make this value unique by adding text to the end of it as mentioned earlier.
  5. Set the Receive Handler property to BizTalkServerApplication.
  6. Set the Receive Pipeline property to the <your project>.NewOrderSplit pipeline that was created earlier.
  7. Click OK to finish.

The next step is to create a new send port. The key to making content-based routing work is to have the send port subscribe to messages that are received by a particular receive port. You do this by using a filter within the send port.

To create a new send port

  1. Using the BizTalk Explorer, create a new send port and name it SQLAdapterExplore_SendNewOrders.
  2. Set the Transport Type property to File.
  3. Set the Address (URI) property to a location that you want.
  4. Select Send under Configurations, and set the Send Pipeline property to the default XMLTransmit pipeline.
  5. Select Filters under Filters & Maps. Under property, scroll until you find the BTS.ReceivePortName property and select it.
  6. Select == under operator.
  7. Enter SQLAdapterExplore_NewOrders under Value. This is the receive port that was just created in the section above. Make sure that you enter the value without quotation marks.

To run the process, start the send port and enable the receive location. Since you used a SELECT statement that selects orders with a null-shipped date, you will receive the same set of XML files every time the polling interval runs the SELECT statement. To turn the sample off, disable the receive location.

Insert New Order

Orders that are generated in the new Sales Support system have to be added to the existing SQL Server-based solution. Regularly, new orders will be received by BizTalk Server in the form of an XML message. Each order message will consist of order information as well as line item information. A map within an orchestration will be used to generate a message that will be sent to SQL Server to update both the Orders and Order Details tables of the Northwind database with a single XML message.

The message generated by the map in the orchestration will be an updategram. You will recall from the discussion on updategrams, that an updategram can consist of multiple blocks to facilitate transactions. In this example, you have to update the Orders table as well as the related Order Details table. If there are any issues updating the Orders Details table, you should not leave related records in the Orders table. When you add a generated item to create the updategram schema, only selecting one table is supported. In order to facilitate updating multiple tables with the single message, some additional steps will be required for the schema and these are outlined in the following steps.

To add generated items

  1. Right-click the project, click Add, and then click Add Generated Items.
  2. In the first step of the wizard, under the Generated Schema Files category, select the Add Adapter template, and then click Next.
  3. On the Select Adapter page, select SQL, leave the defaults for the other options, and then click Next.
  4. On the Database Information page, click Set to enter your connection information. This will be used to create the connection string to the database. Enter the information about the Data Link Properties dialog box, and then click OK. Click Next when you are returned back to the Database Information page of the wizard.
  5. On the Schema Information page, enter the information that the adapter will use to add some additional information to the schema representing the table that SQL Server generates. For the Target namespace, use http://SQLAdapterExplore.InsertOrderUpdategram. Since you are sending a message, select Send port as the port type. The Request root element name will be set to InsertOrderRequest, and the Response root element name will be set to InsertOrderResponse. Note the target namespace and root element names as these will be used later.
  6. Click Next to move to the next step.
  7. On the Statement Type information page, you have the option of selecting Updategram or Stored Procedure. You will use an Updategram for this example. Click Next to continue.
  8. For this particular business requirement, you will insert new records into the database. The selection made on the Statement Information page will determine how the adapter structures the updategram to insert, update, or delete records. Select Insert for the type, and select Orders for the table name. In the Columns to update section, you can select specific columns to affect. For this example, you will insert all columns, so select them all. Click Next to continue.
  9. You have now completed the Add Generated Item process, click Finish to finish.

The Add Generated Item process created two new items in the project. The first item is the multi-part message schema representing the updategram to insert a new record into the Orders table, and the status that will be returned. This schema is named InsertOrdersService.xsd. You can optionally rename the schema to something more appropriate to your project. The schema will appear as shown in the Figure 9.

Figure 9 New schema

ms935658.Local_1498348379_bts_wp_btsadpt_sql09(en-US,BTS.10).gif

Notice the fields have been added to the <after> section. You will recall from the discussion above on updategrams that when a message appears with only an <after> block, and no corresponding <before> block, it performs an insert operation. Also, notice that Orders, the table name, appears as the parent to the attributes representing the columns.

The second item added to the project by the Add Generated Items wizard is a new orchestration. Again, you may want to rename this orchestration to something more appropriate for your project. It is not required that you use this orchestration. You can use the port types and multi-part message types in the generated orchestration as an example to add the generated schema to the orchestration of your choice. This process is described in Microsoft BizTalk Server 2004 Help.

At this point, you have an updategram that will insert a record into the Orders table. This unfortunately will not satisfy the business requirements. You still have to insert the Order Details records, and it has to be part of the same message. To facilitate this, you will create an updategram to insert records into the Order Detail table, and you will add this to the Order updategram you just created.

To add generated items

  1. Right-click the project, and click Add, and then click Add Generated Items.
  2. In the first step of the wizard, under the Generated Schema Files category, select the Add Adapter template, and then click Next.
  3. On the Select Adapter page, select SQL, leave the defaults for the other options, and then click Next.
  4. On the Database Information page, click Set to enter your connection information. This will be used to create the connection string to the database. Enter the information about the Data Link Properties dialog box, and then click OK. Click Next when you are returned back to the Database Information page of the wizard.
  5. On the Schema Information page, enter information the adapter will use to add some additional information to the schema representing the table that is generated by SQL Server. For the Target namespace, use http://SQLAdapterExplore.InsertOrderDetailUpdategram. Since you are sending a message, select Send port as the port type. The Request root element name will be set to OrderDetailsInsertRequest, and the Response root element name will be set to OrderDetailsInsertResponse. Note the target namespace and root element names as these will be used later.
  6. Click Next to move to the next step.
  7. On the Statement Type information page, you have the option of selecting Updategram or Stored Procedure. You will use an updategram for this example. Click Next to continue.
  8. For this particular business requirement, you will insert new records into the database. The selection made on the Statement Information page will determine how the adapter structures the updategram to insert, update, or delete records. Select Insert for the type, and select Order Details for the table name. In the Columns to update section, you can select specific columns to affect. For this example, you will insert all columns, so select them all. Click Next to continue.
  9. You have now completed the Add Generated Item process, click Finish.

The Add Generated Item process has again created two new items in the project. The first item is the multi-part message schema representing the updategram to insert a new record into the Order Details table, and the status that will be returned. This schema is named InsertOrderDetailsService.xsd. The schema will appear as shown in Figure 10.

Figure 10 Schema InsertOrderDetailsService.xsd

ms935658.Local_1498348467_bts_wp_btsadpt_sql10(en-US,BTS.10).gif

The next step is to add the Order Details section from the schema that was just created to the Orders schema created in the first step. BizTalk Editor does not support copying and pasting nodes between schemas, so the files will have to be edited outside BizTalk Editor. You may choose an XML editing tool, or just use a text editor. If using a text editor, use the following steps.

To add the Order Details section from the schema

  1. Open the InsertOrdersService.xsd file in the text editor.
  2. Open the InsertOrderDetailsService.xsd file in the editor and locate the following line:
    <xs:element minOccurs="0" maxOccurs="unbounded" name="_x005b_Order_x0020_Details_x005d_">. 
    
  3. Highlight the section from the line indicated in step 2 until the first </xs:element> tag. Copy the highlighted text.
  4. Locate the first </xs:element> tag in the InsertOrdersService.xsd file and add the copied text just below this line.
  5. Save the file.

The file should now appear in the BizTalk Editor as shown in Figure 11.

Figure 11 Edited InsertOrdersService.xsd file

ms935658.Local_1498348466_bts_wp_btsadpt_sql11(en-US,BTS.10).gif

The last step that is required in preparing the updategram schema is to associate the Order Details records with the Orders records. The Orders table in the Northwind database has an OrderID field that is auto incrementing, or an Identity field. Therefore, when a record is inserted the OrderID value is automatically generated, and this same value is required to insert related records into the Order Details child table. SQLXML gives you the ability to capture the value generated for the Identity field using the updg:at-identity attribute. Use the following steps to add this attribute to the InsertOrdersService schema.

To add updg:at-identity attribute to the InsertOrdersService schema

  1. Right-click your project, click Add, and then click Add New Item.
  2. Add a new schema to your project and name it UpdateGram_identity.xsd.
  3. Select the Root node and delete it.
  4. Right-click the <Schema> node, click Insert Schema Node, and then click Child Field Attribute. Name the attribute returnid. Change its data type to xs:int.
  5. Right-click the <Schema> node, click Insert Schema Node, and then click Child Field Attribute. Name the attribute at-identity. Change its data type to xs:int.
  6. Change the Target namespace property to urn:schemas-microsoft-com:xml-updategram.
  7. Save the schema.
    ms935658.note(en-US,BTS.10).gifNote
    This example does not use the returnid attribute. You can use this attribute to retrieve the auto-generated value from SQL Server if you need it.

The next process is to import the UpdateGram_identity.xsd schema into the InsertOrdersService.xsd to make the attributes created in the previous steps available.

To import the UpdateGram_identity.xsd schema into the InsertOrdersService.xsd

  1. Open the InsertOrdersService schema in the BizTalk Editor.
  2. Select the <Schema> node, and locate the Imports property, and then click the ellipse button [...].
  3. Leave the default XSD Import value in the drop down and select Add.
  4. Expand Schemas, select the UpdateGram_identity schema, and then click OK.
  5. In the new row that was added to the grid on the bottom, change the ns0 in the prefix field to updg.
  6. Right-click the Orders node, click Insert Schema Node, and then click Child Field Attribute.
  7. Locate the Data Type property of the new attribute, and then select the updg:at-identity(Reference) type located at the bottom of the list.
  8. Save the schema.

The updategram schema is now complete and has been constructed to insert a record in the Orders table, retrieve the value that is auto incremented by the OrderID identity column, and insert the child Order Details records using the retrieved identity value for the OrderID column. In the remaining steps, you will create the map that transforms the New Order message to the InsertOrdersService updategram. You will then create a simple orchestration to retrieve the message, perform the transformation, and send the updategram to the Adapter for SQL Server and on to SQL Server.

To create the map

  1. Right-click your project, click Add, and then click Add Existing Item.
  2. Locate the CustomerOrder.xsd file from the included example.
  3. Right-click your project, click Add, and then click Add New Item.
  4. Click Map, change the name to NewOrder_To_InsertOrderUpdategram.btm, and then click Open.
  5. Add the CustomerOrder schema as the source. Add the InsertOrdersService schema as the destination.
  6. Link all fields except for OrderID, Freight, and Unit Price. These fields require functoids.
  7. Add a Looping functoid and connect the LineItem record from the source and the Order Detail record from the destination. This will create an order detail record for each line item.
  8. Add a String Concatenate functoid. Select the Input Parameters property of the functoid. In the Configure Functoid Inputs window, add a new constant and set its value to $ (money data types must be in the format of $xxx.xx), and then click OK.
  9. Connect the Freight field from the source to the functoid. Connect the functoid to the Freight field in the destination.
  10. Repeat step 9 for the Unit Price field.
  11. To associate the at-identity attribute with the OrderID field of the Order Detail records, add a new String Concatenate functoid. Connect the updg:at-identity attribute to the functoid. Add the OrderID field from the destination Order Details record to the functoid.
  12. Select the Input Parameters property of the functoid. In the Configure Functoid Inputs window, add a new constant and set its value to 1. This value is not important, as it will be replaced with the identity value of the inserted Orders record.
  13. Save the map.

The map appears as shown in Figure 12.

Figure 12 Transform map

ms935658.Local_1498348465_bts_wp_btsadpt_sql12(en-US,BTS.10).gif

Create Orchestration

The result of adding the first generated item created a default orchestration. This default orchestration already has a multi-part message type that represents the InsertOrdersService schema. Since you have made all the changes to this schema, you will only use the orchestration. Use the following steps to complete the orchestration.

To complete the orchestration

  1. Create a new message and name it InsertOrder. Set the Message Type property to InsertOrderRequest from the drop-down list. This is the updategram.
  2. Create another new message and name it NewOrder. Set the Message Type property to CustomerOrder from the drop-down list. This is the schema added from the example files.
  3. Add a Receive shape. Set the name to ReceiveOrder. Set the Message property to NewOrder, and the Activate property to True.
  4. Add a new Transform shape below the Receive shape. Set the name of the Message Construct shape to ConstructUpdategram. In the Messages Constructed property, select InsertOrder.
  5. Set the name of the transform shape to NewOrderToUpdategram.
  6. Select the ellipse button [...] in the Map Name property. In the pop up window, click Existing Map, and then select NewOrder_To_InsertUpdateGram from the drop-down list. Set the Source of the map to NewOrder, the Destination to InsertOrder, and then click OK.
  7. Add a Send shape below the Construct Message shape. Set the Name to SendInsert. Set the Message property to InsertOrder.
  8. Add a new port to the orchestration. Change the name to ReceiveOrderPort. This will be a one-way, receive port. Specify the binding later.
  9. Add a new port to the orchestration. Change the name to SendOrderUpdate. This will be a one-way, send port. Specify the binding later.
  10. Save the orchestration.

Configure Ports

The final step is to create the ports in BizTalk Explorer so that you can deploy the project.

To create the ports

  1. Create a new receive port and change the name to NewOrderReceive.
  2. Create a new receive location and change the name to NewOrder. Set the Transport Type property to File, and then select a directory. Set the receive pipeline to the default XMLReceive pipeline.
  3. Create a new send port and change the name to SendOrderUpdategram.
  4. Set the Transport Type to SQL. Select the ellipse button [...] in the Address (URI) property. In the SQL Transport Properties window, set the connection string to the Northwind database. Set the Document Target Namespace property to http://SQLAdapterExplore.IssueSelect to match the namespace of the updategram schema you created.
  5. Set the Response root element name to InsertOrderResponse to match the node of the response section of the updategram schema. Click OK to continue.
  6. Set the send pipeline to the default XMLTransmit pipeline. Click OK to continue.
  7. At this point, you will want to build and deploy your project. Bind the orchestration to the ports you just created. To run the orchestration, copy the CustomerOrder_input.xml file from the example to the receive location. To view the results, examine the Order and Order Details table in the Northwind database.

At this point, you should have a good idea of the capabilities of the Adapter for SQL Server as well as a foundation for how to implement the adapter for your projects. Remember that the examples included with this document are meant to demonstrate some common scenarios for using the adapter, but are not the only uses for the adapter.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.