Walkthrough: Updating Multiple Tables with an Updategram

The SQL Transport Schema Generation Wizard does not permit the selection of multiple tables for updating when generating a schema for an updategram. It is possible, however, to manually create an updategram that will update multiple tables.

Aa562031.note(en-us,BTS.20).gifNote
For optimal performance consider using a stored procedure rather than an updategram when updating multiple tables.

In this walkthrough, you will do the following:

  1. Create a database and two tables in SQL Server to be updated with an updategram.

  2. Create a receive port and a receive location that uses the File adapter to receive an updategram.

  3. Create an updategram to drop into the folder monitored by the receive location.

  4. Create a send port that uses the SQL Adapter.

  5. Enable the receive location and start the send port.

  6. Process the updategram with the File adapter and the SQL adapter.

To perform these procedures you must be a member of the BizTalk Administrators group and must have the appropriate permissions in SQL Server.

  1. Execute the following SQL statements from either Query Analyzer or SQL Server Management Studio to create a database and two tables. Query Analyzer is used with SQL Server 2000 to execute SQL statements and SQL Server Management Studio is used with SQL Server 2005 to execute SQL statements.

    CREATE DATABASE Books
    GO
    
    USE Books
    GO
    
    CREATE TABLE Publisher(
                PublisherID bigint IDENTITY(1000, 1) NOT NULL constraint PK_PUBLISHER_ID primary key,
                PublisherName nvarchar(200) NULL)
    CREATE TABLE Book(
                ISBN varchar(20) NOT NULL,
                PublisherID bigint NOT NULL references Publisher(PublisherID),
                BookTitle varchar(200),
                authorName varchar(50))
    
  2. These SQL statements will create a database named Books with two tables, Publisher and Book. The PublisherID field is the primary key for the Publisher table and is also the foreign key for the Book table.

  1. In the BizTalk Server Administration console, double-click the default database <machine_name>.BizTalkMgmtDb.dbo, where machine_name is the name of your computer. Click Applications, and then click BizTalk.Application.1.

  2. Right-click Receive Ports, click New, and then click One-way receive port.

  3. In the Receive Port Properties dialog box, in the Name box, type ReceiveUpdateGram.

  4. Click Receive Locations, and then click New. In the Receive Location Properties dialog box, in the Name box, type ReceiveUpdateGram.

  5. In the Transport Type box, select FILE.

  6. In the Receive Handler box, select BizTalkServerApplication.

  7. In the Receive Pipeline box, select Microsoft.BizTalk.DefaultPipelines.PassThruReceive.

  8. In the Transport box, click the Configure button.

  9. In the FILE Transport Properties dialog box, click Browse to display the Browse For Folder dialog box.

  10. Click to expand My Computer, click to select the C: drive, click Make New Folder, enter the name File Receive, and then click OK.

  11. Click OK on the FILE Transport Properties dialog box, and then click OK on the Receive Location Properties dialog box.

  • Paste the following text into Notepad and save to a local folder as updategram.xml:

    <ReqRoot xmlns="http://SQLAdapter.Test" xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
    <sync>
    <after>
       <Publisher PublisherName="Microsoft Press" updg:at-identity="NewID">
       </Publisher>
       <Book ISBN="1-123-234509-2" BookTitle="SQLXML" PublisherID="NewID" authorName="Jeff">
       </Book>
       <Book ISBN="1-123-234509-3" BookTitle="SQLXML2" PublisherID="NewID" authorName="David">
       </Book>
    </after>
    </sync>
    </ReqRoot>
    

  1. In the BizTalk Server Administration console, right-click Send Ports, click New, and then click Static one-way Send Port.

  2. In the Name box, type UpdateTwoTables.

  3. In the Send Port Properties dialog box, in the Transport Type box, select SQL.

  4. In the Transport box, click the Configure button.

  5. Next to the Connection String box, click the ellipsis [] button to display the Data Link Properties dialog box.

  6. In the DataLink Properties dialog box do the following:

    Use this To do this

    Select or enter a server name

    Type the name of the SQL server that you created the database and tables on.

    Enter information to log on to the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select Books from the drop-down list.

  7. Click OK.

    Aa562031.note(en-us,BTS.20).gifNote
    You have just set the connection string for the SQL adapter.

  8. Type http://SQLAdapter.Test into the Document Target Namespace box and click OK.

  9. Click Filters.

  10. In the Property box, select BTS.ReceivePortName.

  11. In the Value box, type ReceiveUpdateGram, and then click OK.

  1. Right-click the ReceiveUpdateGram receive location, and then click Enable.

  2. Right-click the UpdateTwoTables send port, and then click Start.

  1. Copy the file updategram.xml to the C:\File Receive folder that you created earlier. The File adapter will pick up the file and publish the file to the BizTalk Server MessageBox database. The SQL adapter will retrieve the file from the MessageBox database and update the SQL tables in the Books database.

  2. Execute the following SQL statements from either Query Analyzer or SQL Server Management Studio to verify that the tables were updated:

    USE Books
    GO
    SELECT * from Publisher, Book
    

Community Additions

ADD
Show: