Walkthrough: Using the SQL Adapter with an Updategram in an Orchestration

Updategrams used with the SQL adapter enable you to run SQL UPDATE, INSERT, or DELETE operations. The process of configuring the SQL adapter is similar to the procedure for using a stored procedure, except that the Updategram only has the Success element in the response schema.

Prerequisites

Before completing this procedure, you must build and initialize the SQL Sample in the BizTalk Server SDK and create the SQL_Adapter_SP project. For more information about the SQL_Adapter_SP project, see Walkthrough: Using the SQL Adapter with a Stored Procedure in an Orchestration. The output of the orchestration in the SQL_Adapter_SP project is the input for the orchestration created in this procedure.
What This Sample Does

This topic shows you how to do the following:

  1. Create an empty BizTalk project.

  2. Add metadata to the BizTalk project and create a SQL adapter port.

  3. Create multiple SQL adapter ports.

  4. Merge orchestrations created by the Add Adapter Wizard.

  5. Modify the UpdateLoanService schema.

  6. Create a map.

  7. Create new messages.

  8. Create an orchestration.

  9. Build the project.

  10. Create a strong name key file.

  11. Deploy the solution.

  12. Create a send port to communicate with the SQL adapter in BizTalk Explorer.

  13. Bind the port to the orchestration.

  14. Start an orchestration.

  15. Run the Loan Assignment project.

By completing this procedure in this topic, you will re-create the Loan Application project provided in the SQL Adapter Loan Application sample. You must build and initialize the SQL Adapter Loan Application sample to see the Loan Acceptance sample. For more information about installing the SQL Adapter Loan Application sample, see SQL Adapter Loan Application.
Where to Find This Sample

Once installed, the Loan Assignment project is located in the <BizTalk installation directory>\SDK\Samples\AdaptersUsage\SQLAdapter\Loan Assignment folder.
To create an empty BizTalk project

  1. Click Start, point to Programs, point to Microsoft Visual Studio 2005, and then click Microsoft Visual Studio 2005.

  2. On the File menu, point to New, and then click Project.

  3. In the New Project dialog box, in the Project Types section, select BizTalk Projects, and then in the Templates section, click Empty BizTalk Server Project.

  4. In the Name box, type SQL_Adapter_Updategram, and then click OK.

    The new SQL_Adapter_Updategram project appears.

To add a required schema to the project

  1. In Solution Explorer, right-click SQL_Adapter_Updategram, point to Add, and then click Add Existing Item.

  2. In the Add Existing Item - SQL_Adapter_Updategram dialog box, navigate to <drive>< <BizTalk installation directory>\SDK\Samples\AdaptersUsage\SQLAdapter\Loan Assignment, select AgentResponse.xsd and then click Open.

  3. In Solution Explorer, select AgentResponse.xsd.

  4. In the Properties window, change the Type Name value to SQLService_1.

  5. In Solution Explorer, double-click AgentResponse.xsd.

  6. In the Properties window, change the Target Namespace value to http://Microsoft.Sample.SQLAdapter.

  7. In Solution Explorer, right-click AgentResponse.xsd, and then click Open With.

  8. In the Open With - AgentResponse.xsd dialog box, select XML Schema Editor.

  9. On the AgentResponse.xsd tab, right-click, and then click View XML Source.

  10. Change the instance of http://Microsoft.BTS2004.Sample.SQL_Adapter to http://Microsoft.Sample.SQLAdapter.

  11. On the File menu, click Save AgentResponse.xsd.
To add metadata to the BizTalk project and create a SQL adapter port

  1. In Visual Studio 2005, in Solution Explorer, right-click SQL_Adapter_Updategram, point to Add, and then click Add Generated Items.

  2. In the Add Generated Items – SQL_Adapter_Updategram dialog box, in the Template section, click Add Adapter, and then click Add.

  3. In the Add Adapter Wizard, on the Select Adapter page, select SQL from the list of registered adapters, and then click Next.

    You can leave the other entries on the page blank or with their default values, unless you are connecting to a remote SQL Server computer. If connecting to a remote SQL Server computer, type the name of the server in the SQL Server box, and the name of the database in the Database box. Leave the Port box empty.

  4. On the Database Information page, click Set.

  5. In the DataLink Properties dialog box, on the Connection tab, do the following.

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select BTS2004_SQL_Adapter_loans from the drop-down list.

  6. Click OK.

    This sets the connection string for the SQL adapter.

  7. Click Next.

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

    Use This To do this

    Target namespace

    Type http://Microsoft.Sample.SQLAdapter.

    Select the port type

    Select Send port.

    Request root element name

    Type UpdateLoanRequest.

    Response root element name

    Type UpdateLoanResponse.

    Aa577899.note(en-us,BTS.20).gifNote
    Write this information down. You will need it later. Take note that everything you type is case-sensitive, for example, SQLAdapter is not the same as SQLadapter.

  9. Click Next.

  10. On the Statement Type Information page, select Updategram, and then click Next.

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

    Use This To do this

    Select the type of updategram

    Select Update.

    Table name

    Select Loans.

    Columns to Update

    Select all columns.

    Aa577899.note(en-us,BTS.20).gifNote
    You should select all the columns at this time. Later, you can edit the schema so you have the <before> and <after> blocks you want.

    Aa577899.note(en-us,BTS.20).gifNote
    The Updategram will have problems if some of the columns have a NULL value (in the XML data, that attribute/element will be omitted). It is best to design the table so that all columns have a default value.

  12. Click Next.

  13. On the final page of the wizard, click Finish.

The Add Adapter Wizard generated and added two files to your project, UpdateLoansService.xsd and BizTalkOrchestration.odx. UpdateLoansService.xsd is the schema for the Updategram for the table (Loans). BizTalk Orchestration.odx is a blank orchestration with preconfigured port type named UpdateLoansPortType, viewable in the Orchestration View tab in the Types section, in the Port Types node. The orchestration also contains two multi-part message types, UpdateLoansRequest and UpdateLoansResponse, also viewable on the Orchestration View tab in the Multi-part Message Types node. Currently, these message types have red exclamation points in front of them indicating that they are not configured properly. Leave them unconfigured for now. They will be configured later in the procedure.

Figure showing the files added to the BizTalk project
Files added to the BizTalk project.
Aa577899.note(en-us,BTS.20).gifNote
If you know that the stored procedure only returns one row (as in this example), adjust properties of elements in the response schema from the default: "Max Occurs=unbounded ; Min Occurs=0"

Next, you need to add two additional SQL port types to this project using the same Add Adapter Wizard. These new port types use stored procedures. Just as you changed the stored procedure before using the Add Adapter Wizard in "Using the SQL Adapter with a Stored Procedure" topic, you also have to add , xmldata to these stored procedures before running the Add Adapter Wizard again.
To modify the stored procedures in SQL Server 2000

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. In Enterprise Manager, in the left pane, expand Microsoft SQL Servers, expand SQL Server Group, expand (LOCAL) Windows NT, expand Databases, expand BTS2004_SQL_Adapter_loans, and then click Stored Procedures.

  3. In the right pane, double-click SP_monitor_for_Loan_to_Assign.

  4. In the Stored Procedure Properties - SP_monitor_for_Loan_to_Assign dialog box, in the Text box, at the end of the if clause, following and Loans.LoanID = @LoanID for xml data, type , xmldata.

  5. At the end of the else clause, following select * from Customers,Loans where 1 = 0 for xml auto, type , xmldata.

  6. Click OK.

  7. In the right pane, double-click SP_monitor_for_new_customers so that you can make the same modifications to this stored procedure.

  8. In the Stored Procedure Properties - SP_monitor_for_new_customers dialog box, in the Text box, at the end of the if clause, following select * from Customers where CustomerID = @CustomerID for xml auto, type , xmldata.

  9. At the end of the else clause, following select * from Customers where 1 = 0 for xml auto, type , xmldata.

  10. Click OK.

    Now that the stored procedures have been modified, you can run the Add Adapter Wizard to create the additional port types and message types.

To modify the stored procedures in SQL Server 2005

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. When prompted, connect to the SQL Server instance that contains the BTS2004_SQL_Adapter_Loans database.

  3. In SQL Server Management Studio, in the left pane, expand Databases, expand BTS2004_SQL_Adapter_Loans, expand Programmability and then click Stored Procedures.

  4. In the right pane, right-click SP_monitor_for_Loan_to_Assign and click Script Stored Procedure As, ALTER To, New Query Editor Window.to edit the stored procedure text.

  5. Add the text , xmldata to the end of the if clause so that this statement reads as follows:

    and Loans.LoanID = @LoanID for xml data, xmldata

  6. Add the text , xmldata to the end of the else clause so that this statement reads as follows:

    select * from Customers,Loans where 1 = 0 for xml auto, xmldata

  7. On the Query menu, click Execute to modify the stored procedure.

  8. Click the Summary tab to display the list of stored procedures again.

  9. In the right pane, double-click SP_monitor_for_new_customers and click Script Stored Procedure As, ALTER To, New Query Editor Window.to edit the stored procedure text.

  10. Add the text , xmldata to the end of the if clause so that this statement reads as follows:

    select * from Customers where CustomerID = @CustomerID for xml auto, xmldata.

  11. Add the text , xmldata to the end of the else clause so that this statement reads as follows:

    select * from Customers where 1 = 0 for xml auto, xmldata.

  12. On the Query menu, click Execute to modify the stored procedure.

    Now that the stored procedures have been modified, you can run the Add Adapter Wizard to create the additional port types and message types.

To create a second SQL adapter port

  1. To open the Add Adapter Wizard, in Solution Explorer, right-click SQL_Adapter_Updategram, point to Add, and then click Add Generated Items.

  2. In the Add Generated Items – SQL_Adapter_Updategram dialog box, in the Template section, click Add Adapter Metadata, and then click Add.

  3. In the Add Adapter Wizard, on the Select Adapter page, select SQL from the list of registered adapters, and then click Next.

  4. On the Database Information page, click Set.

  5. In the DataLink Properties dialog box, on the Connection tab, do the following.

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select BTS2004_SQL_Adapter_loans from the drop-down list.

  6. Click OK.

    This sets the connection string for the SQL adapter.

  7. Click Next.

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

    Use This To do this

    Target namespace

    Type http://Microsoft.Sample.SQLAdapter.

    Select the port type

    Select Receive Port.

    Document root element name

    Type LoanRoot.

  9. Click Next.

  10. On the Statement Type Information page, select Stored Procedure, and then click Next.

  11. On the Statement Information page, select SP_monitor_for_Loan_to_Assign from the drop-down list.

  12. Click Generate to generate the EXEC statement in the Generated Script box, and then click Next.

    If you receive an error that the SQL statement is incorrect you did not modify the stored procedure correctly.

  13. Click Finish to complete the wizard.

The Add Adapter Wizard generated and added two files to your project, SQLService.xsd and BizTalkOrchestration_1.odx. SQLService.xsd is the schema for the SP_monitor_for_Loan_to_assign stored procedure. BizTalk Orchestration_1.odx is a blank orchestration with preconfigured port type named SQLServiceExec, viewable in the Orchestration View tab, in the Types section, in the Port Types node. The orchestration also contains a multi-part message type named procedureRequest, also viewable on the Orchestration View tab in the Multi-part Message Types node.
To create the third SQL adapter port

  1. To open the Add Adapter Wizard, in Solution Explorer, right-click SQL_Adapter_Updategram, point to Add, and then click Add Generated Items.

  2. In the Add Generated Items – SQL_Adapter_Updategram dialog box, in the Template section, click Add Adapter, and then click Add.

  3. In the Add Adapter Wizard, on the Select Adapter page, select SQL from the list of registered adapters, and then click Next.

  4. On the Database Information page, click Set.

  5. In the DataLink Properties dialog box, on the Connection tab, do the following.

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select BTS2004_SQL_Adapter_loans from the drop-down list.

  6. Click OK.

    This sets the connection string for the SQL adapter.

  7. Click Next.

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

    Use This To do this

    Target namespace

    Type http://Microsoft.Sample.SQLAdapter.

    Select the port type

    Select Send port.

    Request root element name

    Type SPROCRequest.

    Response root element name

    Type SPROCResponse.

  9. Click Next.

  10. On the Statement Type Information page, select Stored Procedure, and then click Next.

  11. On the Statement Information page, select SP_Monitor_for_New_Customers from the drop-down list.

  12. Click Generate to generate the EXEC statement in the Generated Script box, and then click Next.

    If you receive an error that the SQL statement is incorrect you did not modify the stored procedure correctly.

  13. Click Finish to complete the wizard.

The Add Adapter Wizard generated and added two files to your project, SQLService_1.xsd and BizTalkOrchestration_2.odx. SQLService_1.xsd is the schema for the SP_monitor_for_new_customer stored procedure. BizTalk Orchestration_2.odx is a blank orchestration with preconfigured port type named SQLServiceExec, viewable in the Orchestration View tab in the Types section, in the Port Types node. The orchestration also contains two multi-part message types, procedureResponse and procedureRequest, also viewable on the Orchestration View tab in the Multi-part Message Types node.

Now that you are done using the Add Adapter Wizard, you need to remove the , xmldata that you added to the stored procedures.
To revert the stored procedures in SQL 2000

  1. In Enterprise Manager, in the left pane, expand Microsoft SQL Servers, expand SQL Server Group, expand (LOCAL) Windows NT, expand Databases, expand BTS2004_SQL_Adapter_loans, and then click Stored Procedures.

  2. In the right pane, double-click SP_monitor_for_Loan_to_Assign.

  3. In the Stored Procedure Properties - SP_monitor_for_Loan_to_Assign dialog box, in the Text box, at the end of the if clause, in the and Loans.LoanID = @LoanID for xml auto, xmldata line, remove , xmldata.

  4. At the end of the else clause, in the line select * from Customers,Loans where 1 = 0 for xml auto, xmldata remove , xmldata.

  5. Click OK.

  6. In the right pane, double-click SP_monitor_for_new_customers so that you can make the same modifications to this stored procedure.

  7. In the Stored Procedure Properties - SP_monitor_for_new_customers dialog box, in the Text box, at the end of the if clause, in the select * from Customers where CustomerID = @CustomerID for xml auto, xmldata line, delete , xmldata.

  8. At the end of the else clause, in the select * from Customers where 1 = 0 for xml auto, xmldata line, delete , xmldata.

  9. Click OK.

  10. On the File menu, click Exit to close Enterprise Manager.

Next, you will merge all three of the orchestrations into one orchestration. Each time you run the Add Adapter Wizard, it creates a new BizTalk orchestration file. Because you have now run the wizard three times, you should have three orchestration files: BizTalk Orchestration.xsd, BizTalk Orchestration_1.xsd, and BizTalk Orchestration_2.xsd.
To revert the stored procedures in SQL 2005

  1. In SQL Server Management Studio, in the left pane, expand Databases, expand BTS2004_SQL_Adapter_Loans, expand Programmability and then click Stored Procedures.

  2. In the right pane, right-click SP_monitor_for_Loan_to_Assign and click Script Stored Procedure As, ALTER To, New Query Editor Window.to edit the stored procedure text.

  3. Remove the text , xmldata from the end of the if clause so that this statement reads as follows:

    and Loans.LoanID = @LoanID for xml data

  4. Remove the text , xmldata from the end of the else clause so that this statement reads as follows:

    select * from Customers,Loans where 1 = 0 for xml auto

  5. On the Query menu, click Execute to modify the stored procedure.

  6. Click the Summary tab to display the list of stored procedures again.

  7. In the right pane, double-click SP_monitor_for_new_customers and click Script Stored Procedure As, ALTER To, New Query Editor Window.to edit the stored procedure text.

  8. Remove the text , xmldata from the end of the if clause so that this statement reads as follows:

    select * from Customers where CustomerID = @CustomerID for xml auto

  9. Remove the text , xmldata from the end of the else clause so that this statement reads as follows:

    select * from Customers where 1 = 0 for xml auto

  10. On the Query menu, click Execute to modify the stored procedure.

Next, you will merge all three of the orchestrations into one orchestration. Each time you run the Add Adapter Wizard, it creates a new BizTalk orchestration file. Because you have now run the wizard three times, you should have three orchestration files: BizTalk Orchestration.xsd, BizTalk Orchestration_1.xsd, and BizTalk Orchestration_2.xsd.
To merge the orchestrations together

  1. In Visual Studio 2005, in Solution Explorer, double-click Orchestration_1.odx.

  2. On the Orchestration View tab, in the Types section, in the Port Types node, right-click SQLServiceExec and click Copy.

  3. In Solution Explorer, double-click BizTalk Orchestration.odx.

  4. On the Orchestration View tab, in the Types section, right-click the Port Types node, and then click Paste to add the port type to the combined orchestration.

  5. In the Properties window, in the Identifier value box, type SQLServiceExec1.

  6. In Solution Explorer, double-click Orchestration_1.odx.

  7. On the Orchestration View tab, in the Types section, in the Multi-part Message Types node, right-click procedureRequest and click Copy.

  8. In Solution Explorer, double-click BizTalk Orchestration.odx.

  9. On the Orchestration View tab, in the Types section, right-click the Multi-part Message Types node, and then click Paste to add the multi-part message type to the combined orchestration.

  10. In the Properties window, in the Identifier value box, type procedureRequest1.

  11. In Solution Explorer, right-click BizTalk Orchestration_1.odx, and then click Exclude From Project.

    Aa577899.note(en-us,BTS.20).gifNote
    If you make an error and need to copy and paste the port type or message type again, you can add the orchestration file back to the project using the Add Existing Item option on the Project menu.

  12. Repeat the copy and paste operations for the SQLServiceExec port type, procedureRequest and procedureResponse message types in BizTalk Orchestration_2.odx file. The following table provides the new names for each item.

    Item Previous name New name

    Port Type

    SQLServiceExec

    SPROCServiceExec

    Multi-part Message Type

    procedureRequest

    SPROCRequest

    Multi-part Message Type

    procedureResponse

    SPROCResponse

  13. In Solution Explorer, right-click BizTalk Orchestration_2.odx, and then click Exclude From Project.

  14. In Solution Explorer, right-click SQLService_1.xsd, and then click Exclude From Project.

To update the port types and multi-part messages types

  1. In Orchestration View, expand Port Types, expand SQLServiceExec1, expand procedure, and then expand Request.

  2. In the Properties window, change the Message Type to SQL_Adapter_Updategram.procedureRequest1.

  3. In Orchestration View, expand SPROCServiceExec, procedure, Request.

  4. In the Properties window, change the Message Type value to SQL_Adapter_Updategram.SPROCRequest.

  5. In Orchestration View, expand SPROCServiceExec, procedure, Response.

  6. In the Properties window, change the Message Type value to SQL_Adapter_Updategram.SPROCResponse.

  7. In Orchestraion View, expand Multi-Part Message Types, SPROCRequest, parameters.

  8. In the Properties window, change the Type to SQL_Server_Updategram.AgentResponse.AgentRequest.

  9. In Orchestraion View, expand Multi-Part Message Types, expand SPROCResponse, and then expand parameters.

  10. In the Properties window, change the Type to SQL_Server_Updategram.AgentResponse.AgentResponse.

  11. In Orchestraion View, expand Multi-Part Message Types, expand UpdateLoansRequest, and then expand parameters.

  12. In the Properties window, change the Type to SQL_Server_Updategram.UpdateLoansService.MyRequestRoot.

If any of the Port Types or Multi-part Message Types still have a red exclamation point in front of them, refer to the BizTalk Orchestration.odx file in the Loan Assignment project located at <drive>:\Program Files\Microsoft BizTalk Server 2006\SDK\Sample\Adapter\SQLAdapter\Loan Assignment for the correct properties and modify your orchestration appropriately.
To modify the UpdateLoanService schema

  1. In Solution Explorer, double-click UpdateLoanService.xsd.

  2. Expand all of the nodes in the schema. Your schema should look like the one in the following figure:

    Figure showing the initial schema
    The initial schema.

  3. From the before node, select all of the columns except the LoanID column, and delete them.

  4. From the after node, select and delete the CustomerID and RequestAmt column.

    Your schema should now look like the one in the following figure:

    Figure showing the final schema
    The final schema.

    All columns are deleted from the before node except for the key column.

  5. Select the sync node, and then in the Properties window, do the following.

    Use this To do this

    Max Occurs

    Change the value to 1.

    Min Occurs

    Ensure that the value is 1.

  6. Select the after node in the schema.

  7. In the Properties window, do the following.

    Use this To do this

    Max Occurs

    Change the value to 1.

    Min Occurs

    Change the value to 1.

  8. Select the Loans node located under the after node in the schema.

  9. In the Properties window, do the following.

    Use this To do this

    Max Occurs

    Change the value to 1.

    Min Occurs

    Change the value to 1.

  10. To make some of the fields distinguished, right-click the Schema node, point to Promote, and then click Show Promotions.

  11. In the Promote Properties dialog box, in the left pane, expand Schema, expand MyRequestRoot, expand sync, expand after, and then expand Loans.

  12. In the left pane, select AgentID, and then click Add.

  13. Repeat step 12 for Interest, and Status, and then click OK.

  14. On the File menu, click Save UpdateLoanService.xsd.
To create new messages

  1. In Solution Explorer, double-click BizTalk Orchestration.odx.

  2. On the Orchestration View tab, right-click Messages, and then click New Message.

  3. In the Properties window, do the following.

    Use this To do this

    Identifier

    Type Assignable_loan_msg.

    Message Type

    Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.procedureRequest1.

  4. On the Orchestration View tab, right-click Messages, and then click New Message.

  5. In the Properties window, do the following.

    Use this To do this

    Identifier

    Type UpdategramInputMsg.

    Message Type

    Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.UpdateLoansRequest.

  6. On the Orchestration View tab, right-click Messages, and then click New Message.

  7. In the Properties window, do the following.

    Use this To do this

    Identifier

    Type UpdategramResultMsg.

    Message Type

    Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.UpdateLoansResponse.

  8. On the Orchestration View tab, right-click Messages, and then click New Message.

  9. In the Properties window, do the following.

    Use this To do this

    Identifier

    Type AgentInterestSPROCInputMsg.

    Message Type

    Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram. SPROCRequest.

  10. On the Orchestration View tab, right-click Messages, and then click New Message.

  11. In the Properties window, do the following.

    Use this To do this

    Identifier

    Type AgentInterestSPROCResultMsg.

    Message Type

    Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.SPROCResponse.
To add shapes to the orchestration

  1. In Solution Explorer, double-click BizTalk Orchestration.odx to open the orchestration in the Visual Studio 2005 design surface.

  2. From the BizTalk Orchestrations Toolbox, drag a Port onto the right Port Surface on the design surface.

  3. In the Port Configuration Wizard, on the Welcome page, click Next.

  4. On the Port Properties page, in the Name box, type UpdateLoanAssignment_port, and then click Next.

  5. On the Select a Port Type page, do the following.

    Use this To do this

    Select the port type to be used for this port

    Select Use an existing Port Type.

    Available Port Types pane

    Select SQLAdapterUpdategram.UpdateLoansTypePort.

  6. Click Next.

  7. On the Port Binding page, do the following.

    Use this To do this

    Port direction of communication

    Select I'll be sending a request and receiving a response.

    Port binding

    Select Specify later.

  8. Click Next.

    Aa577899.note(en-us,BTS.20).gifNote
    You can only use Specify Later (Late Binding) on SQL adapter ports.

  9. On the Completing the Port Wizard page, click Finish to complete and close the wizard.

  10. Add additional shapes and ports to the orchestration by dragging the following shapes onto the orchestration design surface, and then set the properties as specified in the following table.

    Shape Location Property Settings

    Receive

    Between the Begin (green circle) and the End (red octagon)

    Name: Get_Assignable_loan

    Message: Assignable_loan_msg

    Activate: True

    Construct Message

    Below the Get_assignable_loan shape

    Name: Form_SPROC_Arguments

    Messages Constructed: AgentIntrestSPROCInputMsg

    Transform

    Inside the Form_SPROC_Arguments shape

    Name: Transform_SPROC_Input

    Map Name: Click the ellipsis (…) button.

    In the Transform Configuration dialog box, in the left pane, select Source, and then in the right pane, select Assignable_loan_msg.parameters as the Variable Name.

    In the Transform Configuration dialog box, in the left pane, select Destination, and then in the right pane, select AgentInterestSPROCInputMsg.parameters as the Variable Name.

    Clear the When I click OK, launch BizTalk Mapper box.

    Click OK, and then return to the BizTalk Orchestration.odx tab.

    Send

    Below the Form_SPROC_Arguments shape

    Name: Call_SPROC

    Message: AgentInterestSPROCInputMsg

    Leave the Operation property blank. A red exclamation point appears on the shape in the orchestration indicating that this property must be set.

    Receive

    Below the Call_SPROC shape

    Name: Get_SPROC_Result

    Message: AgentInterestSPROCResultMsg

    Again, leave the Operation property blank.

    Construct Message

    Below the Save_Result shape

    Name: Form_Updategram

    Message Constructed: UpdategramInputMsg

    Transform

    Inside the Form_updategram shape

    Name: Transform_Updategram

    Map Name: Click the ellipsis (…) button.

    In the Transform Configuration dialog box, in the left pane, select Source, and then in the right pane, select Assignable_loan_msg.parameters as the Variable Name.

    In the Transform Configuration dialog box, in the left pane, select Destination, and then in the right pane, select UpdategramInputMsg.parameters as the Variable Name.

    Clear the When I click OK, launch BizTalk Mapper box.

    Click OK, and then return to the BizTalk Orchestration.odx tab.

    Message Assignment

    Inside the Form_Updategram shape, below the Transform_Updategram shape

    Name: Set_Agent_and_Interest

    Expression: Click the ellipsis (…) button.

    In the BizTalk Expression Editor dialog box, type the following:

    UpdategramInputMsg.parameters.sync.after.Loans.AgentID = AgentInterestSPROCResultMsg.parameters.row.AgentID;
UpdategramInputMsg.parameters.sync.after.Loans.Interest = AgentInterestSPROCResultMsg.parameters.row.InterestRate;
UpdategramInputMsg.parameters.sync.after.Loans.Status = "Assigned";

    Click OK.

    Send

    Below the Set_Agent_and_Interest shape

    Name: Send_Updategram

    Message: UpdategramInputMsg

    Leave the Operation property blank.

    Receive

    Below the Send_Updategram shape

    Name: Get_Updategram_Result

    Message: UpdategramResultMsg

    Leave the Operation property blank.

    Send

    Below the Get_UpdateGram_Result

    Name: Save_Results_to_File

    Message: UpdategramResultMsg

    Leave the Operation property blank.

    Port

    On the left Port Surface, next to the Get_Assignable_loan shape

    Name: ReceiveAssignableLoan_port

    Use an existing Port Type

    Available Port Types: SQL_Adapter_Updategram.SQLServiceExec1

    Port Direction of communication: I'll always be receiving messages on this port.

    Port binding: Specify later.

    Port

    On the right Port Surface, next to the Call_SPROC shape

    Name: CallLoanAssignmentSPROC_port

    Use an existing Port Type

    Available Port Types: SQL_Adapter_Updategram.SPROCServiceExec

    Port direction of communication: I'll be sending a request and receiving a response.

    Port binding: Specify later.

    Port

    On the left Port Surface, next to the Save_Results_to_File shape

    Name: Save_LoanAssignment_Port

    Create a new Port Type

    Port Type name: PortType_SaveLoanAssignment

    Communication Pattern: One-Way

    Access Restrictions: Internal

    Port direction of communication: I'll always be sending messages on this port.

    Port binding: Specify later

  11. On the right port surface, drag the UpdateLoanAssignment_port next to the SendUpdategram shape.

  12. Connect the ports to the shapes as specified in the following table.

    Port Name Connect To

    ReceiveAssignableLoan_Port

    Request

    Get_Assignable_loan

    Call_LoanAssignmentSPROC_Port

    Request

    Call_SPROC

    Call_LoanAssignmentSPROC_Port

    Response

    Get_SPROC_result

    UpdateLoanAssignment_port

    Request

    Send_Updategram

    UpdateLoanAssignment_port

    Response

    Get_Updategram

    SaveLoanAssignment_port

    Request

    Save_Results_to_File

To add links to the maps

  1. In Solution Explorer, double-click Transform_Updategram.btm.

  2. Connect the following source schema nodes to the destination schema nodes, by dragging and dropping from the source schema node to the destination schema node.

    Source Schema Node Destination Schema Node

    LoanID

    LoanID (before)

    and

    LoanID (after)

    AgentID

    AgentID

    Interest

    Interest

    Status

    Status

  3. In Solution Explorer, double-click Transform_SPROC_Input.btm.

  4. Connect the following source schema nodes to the destination schema nodes, by dragging and dropping from the source schema node to the destination schema node.

    Source Schema Node Destination Schema Node

    State

    State

    Rating

    Rating

To build the project

  1. On the File menu, click Save All.

  2. On the Build menu, click Build SQL_Adapter_Updategram.

    If you receive error BEC2017, you did not change the Type Name of the AgentResponse.xsd file to SQLService_1. Change the type name and rebuild.

To create a strong name key file

  1. Click Start, point to Programs, point to Visual Studio 2005, point to Visual Studio 2005 Tools, and then click Visual Studio 2005 Command Prompt.

  2. At the command prompt, browse to the folder where your project resides, select Loan Assignment.dll, on the command line, type sn -k SQL_Adapter_Updategram.snk, and then press ENTER.

    You should receive a message at the command prompt stating that the key pair has been written to SQL_Adapter_Updategram.snk.

  3. Assign the strong name to the project. For instructions on assigning the strong name key, see How to Configure a Strong Name Assembly Key File.
To deploy the solution

  • In Solution Explorer, right click SQL_Adapter_Updategram and click Deploy.
To create two SQL send ports using BizTalk Explorer

  1. On the View menu, click BizTalk Explorer.

    You may have to right-click the root node and select Refresh to ensure that you are seeing the latest data.

  2. In BizTalk Explorer, expand BizTalk Configuration Databases, <BizTalk Configuration Database>, right-click Send Ports, and then click Add Send Port.

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

  3. In the Create New Send Port dialog box, select Static Solicit-Response Port, and then click OK.

  4. On the Static Solicit-Response Send Port Properties - Configurations - Transport - Primary dialog box, do the following.

    Use this To do this

    Name

    Type MyLoan_Assignment_SPROC_Port.

    Transport Type

    Select SQL.

    Address (URI)

    Click the ellipsis (...) to open the SQL Transport Properties dialog box.

  5. In the SQL Transport Properties dialog box, do the following.

    Use this To do this

    Connection String

    Click the ellipsis (...) to open the Data Link Properties dialog box, and proceed to step 7.

    Document Target Namespace

    Type http://Microsoft.Sample.SQLAdapter.

    Response Root Element Name

    Type AgentResponse.

  6. Click OK, and then proceed to step 9.

  7. On the Connection tab, do the following.

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select BTS2004_SQL_Adapter_loans from the drop-down list.

  8. Click OK, and then go back to step 5.

  9. On the Static Solicit-Response Send Port Properties - Configurations - Transport - Primary dialog box, in the left pane, expand the Send folder, and then click General, as shown in the following figure.

    Figure showing the General page
    The General page.

  10. On the Static Solicit-Response Send Port Properties - Configurations - Send - General dialog box, do the following.

    Use this To do this

    Send Pipeline

    Select Microsoft.BizTalk.DefaultPipelines.XMLTransmit.

    Receive Pipeline

    Select Microsoft.BizTalk.DefaultPipelines.XMLReceive.

  11. Click OK.

  12. Repeat steps 1 through 11 to create another send port, except in step 4 name the send port MyLoan_Assignment_Updategram_Port, and set the Response Root Element Name to UpdateLoanResponse.
To add a File send port

  1. In BizTalk Explorer, expand BizTalk Configuration Databases, <BizTalk Configuration Database>, right-click Send Ports, and then click Add Send Port.

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

  2. In the Create New Send Port dialog box, select Static One-Way Port, and then click OK.

  3. On the Static One-Way Properties - Configurations - Transport - Primary dialog box, do the following:

    Use this To do this

    Name

    Type MyLoan_Assignment_Result_Port.

    Transport Type

    Select FILE.

    Address (URI)

    Click the ellipsis (...) to open the FILE Transport Properties dialog box.

  4. In the File Transport Properties dialog box, do the following:

    Use this To do this

    Destination Folder

    Type the directory location of the Result_folder, for example C:\Documents and Settings\<username>\My Documents\Visual Studio 2005\Projects\SQL_Adapter_SP\Result_folder.

    File name

    Type Assigned_Loan_%MessageID%.xml.

    Copy mode

    Type Create New.

  5. Click OK, and then proceed to step 9.

  6. On the Connection tab, do the following:

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select BTS2004_SQL_Adapter_loans from the drop-down list.

  7. Click OK, and then go back to step 5.

  8. On the Static Solicit-Response Send Port Properties - Configurations - Transport - Primary dialog box, in the left pane, expand the Send folder, and then click General, as shown in the following figure.

  9. On the Static Solicit-Response Send Port Properties - Configurations - Send - General dialog box, do the following:

    Use this To do this

    Send Pipeline

    Select Microsoft.BizTalk.DefaultPipelines.XMLTransmit.

  10. Click OK.
To create a receive location using BizTalk Explorer

  1. Right-click Receive Ports, and then click Add Receive Port.

  2. In the Create New Receive Port dialog box, select One-Way Port, and then click OK.

  3. On the One-Way Receive Port Properties - Configurations - General dialog box, in the Name box, type MyLoan_Assignment_Receive_Port, accept the defaults, and then click OK.

    The MyLoan_Assignment_Receive_Port receive port now appears in BizTalk Explorer.

  4. Expand the new MyLoan_Assignment_Receive_Port node, right-click Receive Locations, and then click Add Receive Location.

  5. In the Receive Location Properties - Configurations - General dialog box, do the following:

    Use this To do this

    Name

    Type MyLoan_Assignment_SQLReceive.

    Transport Type

    Select SQL.

    Address (URI)

    Click the ellipsis (...) button and then proceed to step 7.

    Receive Handler

    Select the host you bound to the orchestration, BizTalkServerApplication by default.

    Receive Pipeline

    Select Microsoft.BizTalk.DefaultPipelines.XMLReceive.

  6. Click OK and then continue to To bind the ports to the orchestration.

  7. In the SQL Transport Properties dialog box, do the following:

    Use this To do this

    Poll While Data Found

    Ensure the value is False.

    Polling Interval

    Ensure the value is 30.

    Polling Unit of Measure

    Ensure the value is Seconds.

    Connection String

    Click the ellipsis (...) button to open the Data Link Properties dialog box, and skip to step 9.

    Document Root Element Name

    Type LoanRoot.

    Document Target Namespace

    Type http://Microsoft.Sample.SQLAdapter.

    SQL Command

    Click the ellipsis (...) to open the Import information from a generated schema dialog box, and skip to step 11.

    Address (URI)

    Change the value to SQL://localhost/BTS2004_SQL_Adapter_Loans/02.

    Aa577899.note(en-us,BTS.20).gifNote
    If necessary, you can edit the SQL Command field. You may charge arguments or add a WHERE clause, but it is highly recommended to not modify the returned format of the command.

    Aa577899.note(en-us,BTS.20).gifNote
    Since this is the only place that BizTalk stores the SQL command (a SELECT or EXEC command), using a stored procedure is preferable for complex SELECT operation. You can debug and test the stored procedure through SQL Query Analyzer first without having to use BizTalk Server.

    Aa577899.note(en-us,BTS.20).gifNote
    The Polling Interval property controls the speed of the SQL command. This forces the maximum document speed to 60 messages per minute. Consider this when designing the system.

    Aa577899.note(en-us,BTS.20).gifNote
    The Poll while Data Found property controls what happens the SQL command returns an empty table. The default setting, False, does not issue messages for empty returns. This is the preferred mode to avoid unnecessary messages.

  8. Click OK, and then proceed to step 13.

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

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select BTS2004_SQL_Adapter_loans from the drop-down list.

  10. Click OK, and then go back to step 7 to configure the Document Root Element Name.

  11. On the Import information from a generated schema dialog box, do the following:

    Use this To do this

    Project

    Select SQL_Adapter_Updategram.

    Schema

    Select SQL_Adapter_Updategram_New.SQLService,SQL_Adapter_Updategram_New, Version=1.0.0.0.

  12. Click OK, and then go back to step 7 to configure the URI.

    Aa577899.note(en-us,BTS.20).gifNote
    If necessary, you can edit the SQL Command field. You may charge arguments or add a WHERE clause, but it is highly recommended to not modify the returned format of the command.

    Aa577899.note(en-us,BTS.20).gifNote
    Since this is the only place that BizTalk stores the SQL command (a SELECT or EXEC command), using a stored procedure is preferable for complex SELECT operation. You can debug and test the stored procedure through SQL Query Analyzer or SQL Server Management Studio (on SQL Server 2005) first without having to use BizTalk Server.

    Aa577899.note(en-us,BTS.20).gifNote
    The Polling Interval property controls the speed of the SQL command. This forces the maximum document speed to 60 messages per minute. Consider this when designing the system.

    Aa577899.note(en-us,BTS.20).gifNote
    The Poll while Data Found property controls what happens the SQL command returns an empty table. The default setting, False, does not issue messages for empty returns. This is the preferred mode to avoid unnecessary messages.

  13. On the Receive Location Properties – Configurations – General dialog box, do the following:

    Use this To do this

    Receive Handler

    Select BizTalkServerApplication.

    Receive Pipeline

    Select Microsoft.BizTalk.DefaultPipelines.XMLReceive (Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35).

  14. Click OK.
To bind the port to the orchestration

  1. In BizTalk Explorer, expand Orchestrations, right-click SQL_Apapter_Updategram.BizTalk Orchestration_1, and then click Bind.

  2. In the Port Binding Properties – SQL_Adapter_Updategram.BizTalk_Orchestration – Configurations – Binding dialog box, do the following:

    Use this To do this

    ReceiveAssignableLoan_port

    Select MyLoan_Assignment_Receive_Port.

    CallLoanAssignmentSPROC_port

    Select MyLoan_Assignment_SPROC_Port.

    UpdateLoanAssignment_port

    Select MyLoan_Assignment_Updategram_Port.

    Save_LoanAssignment_port

    Select MyLoan_Assignment_Result_Port.

  3. In the left pane, click Host.

  4. In the Port Binding Properties – SQL_Adapter_Updategram.BizTalk_Orchestration_1 – Configurations – Host dialog box, select the host for your orchestration, BizTalkServerApplication by default, and then click OK.
To start the orchestration

  1. In BizTalk Explorer, expand the Orchestrations node, and then right-click SQL_Adapter_Updategram.Orchestration_1, and then click Start.

  2. In the BizTalk Explorer - Express Start dialog box, accept the defaults, and then click OK.

When the orchestration starts, the orchestration icon in BizTalk Explorer appears in color.
To run the Loan Assignment project
See Also

Other Resources

SQL Adapter Samples
Using the SQL Adapter

Tags :


Page view tracker