Building blocks to develop BizTalk applications with the SQL adapter
To perform operations on SQL Server by using the SQL adapter with BizTalk Server, you must perform a set of design-time and run-time tasks using Visual Studio and BizTalk Server Administration console respectively. This section provides an overview of these tasks. All the topics in this section, which demonstrate how to perform specific operations on SQL Server using BizTalk Server, are modeled on these high-level tasks.
Using Visual Studio
Create BizTalk project, and generate schema. You must create a BizTalk project in Visual Studio, and generate schema for the operation that you will perform on SQL Server. For example, if you want to insert records in a SQL Server table, you must generate schema for the Insert operation for that table. To generate schema, you must use the Consume Adapter Service Add-in or Add Adapter Metadata Wizard. For more information, see Retrieving Metadata for SQL Server Operations in Visual Studio using the SQL adapter.
Set up an orchestration. Once you have generated the schema, you must set up an orchestration by using the Orchestration Designer. For a basic orchestration, you add the Send and Receive shapes along with the Send and Receive logical ports. In later steps, you map these logical ports to physical ports by using the BizTalk Server Administration console. The orchestration uses these ports to pick messages that an adapter client sends. The orchestration then passes the messages to SQL Server. Once SQL Server sends a response, the orchestration passes the response back to the adapter client.
Create messages, and link to schema. In your orchestration, you must create messages that will be mapped to the schema you generated in the first step. Typically, you create a request message and a response message. These messages are mapped to the corresponding request and response schemas.
Map message shapes to messages and ports. In your orchestration, you must now map each shape that you added in the second step to messages that you created in the third step. You must also map a message shape to the port on which that message will be sent.
For example, if the first shape in your orchestration is a Receive shape that will receive a message, you map this shape to a request message and the port that sends the request message.
Build and deploy the BizTalk project. After you have set up the orchestration and mapped messages, ports, and schemas, you must build the BizTalk solution. For building a project in Visual Studio, you need an assembly key file. After you successfully build the solution, you must deploy the solution.
More detailed description of these high-level tasks, including procedural information, is provided in various topics of this section.
Once you have successfully built and deployed the BizTalk project, your tasks in Visual Studio are accomplished. You must now perform certain tasks using the BizTalk Server Administration console.
Using the BizTalk Server Administration Console
Configure the application. The BizTalk project you deployed by using Visual Studio shows up in the BizTalk Server Administration console as an orchestration. You must configure this orchestration by mapping the logical ports you created in Visual Studio to physical ports that you must now create using the BizTalk Server Administration console.
On the physical ports, you must specify an "action" or "action mapping". This action corresponds to the operation you want to perform on SQL Server. You need to specify the action if you are not using dynamic actions. For more information about actions, see Configure the SOAP action for the SQL adapter.
Start the application. After the application is configured, you must start the application, and drop request messages at a defined file location. The orchestration consumes the request messages, passes them to SQL Server, and receives a response. This response is available to the adapter client at another defined file location.
To accomplish these high-level tasks, you must also perform other tasks. For example, when you use the Consume Adapter Service Add-in or Add Adapter Metadata Wizard to generate the schema, you must specify a connection URI to connect to SQL Server. This section provides information on such repetitive tasks that you must perform as you develop BizTalk applications using the SQL adapter.