How to Add SQL Adapter Schemas to a BizTalk Project

The Adapter Framework provides the means to add adapter schemas to BizTalk projects. For the SQL adapter, this requires the user to select either an existing endpoint (receive location or send port), or use the Data Link Properties dialog box to select a server to connect to. Then the user needs to enter the information used to generate the schema. When the wizard completes, two schemas are added to the BizTalk project if a send port is used and one schema is added to the project if a receive port is used.

The Add Adapter Wizard enables you to add a SQL adapter to a BizTalk project.

  1. In your Microsoft Visual Studio 2005 BizTalk project, in Solution Explorer, right-click your project, click Add, and then click Add Generated Items.

  2. In the Add Generated Items - <Project name> dialog box, in the Templates section, select Add Adapter, and then click Open.

  3. In the Add Adapter Wizard, on the Select Adapter page, do the following.

    Use this To do this

    Adapter

    Select SQL.

    SQL Server

    Type the BizTalk Server database name or select it from the drop-down list.

    This property is optional. If left blank, you must configure it later in BizTalk Explorer.

    Database

    Displays the list of BizTalk Management databases for the selected server.

    This property is optional. If left blank, you must configure it later in BizTalk Explorer.

    Aa560219.note(en-us,BTS.20).gifNote
    The BizTalk Management database is also referred to as the BizTalk Configuration database.

    Port

    Displays a list of SQL send ports and receive locations previously created and stored in the BizTalk Management database. If selected, this port appears in the Connection String box on the next page in the wizard.

    This property is optional.

  4. Click Next.

  5. On the Database Information page, do the following.

    Use this To do this

    Set button

    Set the connection string information. For information about the Data Link Properties dialog box, see Step 6.

    Connection String

    If you chose a port on the previous page, the connection string information for that port automatically appears in this box. If your connection string is correct, click Next and proceed to step 8.

  6. In the Data Link Properties dialog box, on the Connection tab, do the following.

    Aa560219.note(en-us,BTS.20).gifNote
    For more information, refer to Datalink Help.

    Use this To do this

    Select or enter a server name

    Type the name of the server or select a server name from the list. Use the Refresh button to update the list of servers.

    Use Windows NT Integrated Security

    Use Microsoft Windows NT integrated security when connecting to the SQL Server computer.

    Use a specific user name and password

    Connect to the SQL Server computer using non-integrated security.

    Select the database on the server

    Select the SQL Server database to which you want to connect.

    Attach a database file as a database name

    Type the name of the database.

    Using the file name

    Browse to the database to which you want to connect.

    Test Connection

    Test the connection to the SQL Server computer.

  7. Click OK, and then return to step 5.

  8. On the Schema Information page, do the following.

    Use this To do this

    Target Namespace

    Type the target namespace of the XML documents extracted from the SQL Server.

    Receive Port

    Select this option for a receive port.

    Send Port

    Select this option for a send port.

    Document root element name

    This option is only available when creating a receive port.

    Type the name of the input root element. The element name must be a valid XML element name. This element designates the document going to the SQL Server computer.

    Request root element name

    This option is only available when creating a send port.

    Type the name of the input root element. The element name must be a valid XML element name. This element designates the document going to the SQL Server computer.

    Response root element name

    This option is only available when creating a send port.

    Type the name of the output root element. The element name must be a valid XML element name. This element designates the document coming from the SQL Server computer.

  9. Click Next.

    If you selected Receive Port, proceed to step 10. If you selected Send Port, continue to step 13.

  10. On the Statement Type Information page, do the following.

    Use this To do this

    Select Statement

    Run the SQL statement.

    Stored Procedure

    Run the stored procedure.

  11. Click Next.

    If you selected Select Statement, continue to step 12. If you selected Stored Procedure, continue to step 14.

  12. On the Statement Information page, do the following.

    Use this To do this

    SQL Script

    Run an SQL statement.

    Because the SQL adapter uses SQLXML to render result sets as XML, the for xml auto clause should be included in the SELECT statement.

    Aa560219.note(en-us,BTS.20).gifNote
    Attempting to add generated schemas that contain characters that the System.Xml.XmlConvert class does not support results in a compilation error.

  13. Click Next, and then continue to step 18.

  14. On the Statement Information page, do the following.

    Use this To do this

    Stored Procedure drop-down list

    Select the stored procedure to run from the drop-down list.

    Aa560219.note(en-us,BTS.20).gifNote
    Attempting to add generated schemas that contain characters that the System.Xml.XmlConvert class does not support results in a compilation error.

    Parameter values

    Click a cell in the Value column twice (in two different locations) to enter a value for the property.

    Aa560219.note(en-us,BTS.20).gifNote
    The check box sets the argument to NULL. Clicking twice in one place will toggle the NULL check box.

    Aa560219.note(en-us,BTS.20).gifNote
    The schema generator uses these arguments only for schema generation.

    Generate

    Click this button after you have entered values for all of the properties. This populates the Generated Script field.

  15. Click Next and then continue to step 20.

  16. On the Statement Type Information page, do the following.

    Use this To do this

    Updategram

    Run an updategram.

    Stored Procedure

    Run the stored procedure.

  17. Click Next.

    If you selected Updategram, continue to step 18. If you selected Stored Procedure, go to step 14.

  18. On the Statement Information page, do the following.

    Use this To do this

    Insert

    Select this option to create an insert schema.

    Update

    Select this option to create an update schema.

    Delete

    Select this option to create a delete schema.

    Table name

    Lists the tables available through the connection string.

    Columns to update

    Filled with an enumerated list of table columns from the selected table. The behavior of this field depends on the selection of the Insert, Update, and Delete properties:

    • Insert. Every single column becomes part of the schema that inserts data into the table. Select all the primary keys, unless they are ID fields. Primary keys have PK in the Note column.

    • Update. All columns are available for update. The selected columns will become part of the WHERE clause. If more than one row meets the update criteria, the update operation will fail.

    • Delete. The selected columns will become part of the WHERE clause. If more than one row meets the delete criteria, the delete operation will fail.

  19. Click Next.

  20. Click Finish to complete the wizard.

An XSD schema that describes the messages that the SQL adapter sends and receives is now in the current project. Additionally, an orchestration schedule is now included in the project. The orchestration describes the port types and operations. You can use the orchestration as a template, or in the case where only messaging is used (content-based routing), you can remove the orchestration from the project. You can now build and deploy the project to deploy the schema.

Community Additions

ADD
Show: