One of the greatest difficulties I found when I started working with BizTalk 2004 was the lack of documentation about the SQL Adapter. In this article, I'm going to demonstrate how we can use this adapter in an Orchestration of BizTalk.
To build this example, we're going to use the Northwind database. We're going to simulate a hypothetical situation where we receive an XML message as a file, containing the order number, a customer ID, and the date of the order. In the orchestration, we will use SQL Server to search the additional information about the customer, using the SQL Adapter.
Creating the Project
We'll start this article by creating a new BizTalk Server project in Visual Studio. In the Visual Studio .NET menu, select the "New Project" option, and for the type of project, select "BizTalk Projects". Select the template "Empty BizTalk Project" and create a project named OrderManager.
Creating the Schemas
Now that we have the project, we're going to create two maps that we'll use in the project, one for the input message and one for the output message.
Right-click on the project in the Solution Explorer, and select the "Add New Item" option, then select the "Schema" item. Create a schema named "IncompleteOrder".
Click on the root element of the schema and change the property "
NodeName" to "
Order". After that, right-click on this node and select the "Insert Schema Node" option. Inside this option, select "Child Field Element". Change the
NodeName property of this new node to
OrderId. Repeat this operation to create two additional elements named "
OrderDate" and "
CustomerId". Your schema should look like this:
Now, we're going to create the schema with the complete information of the order. Right-click on the project in the Solution Explorer and select the "Add New Item" option. Then, select the item "Schema" and name it "CompleteOrder".
When the schema shows up, rename the "
Root" element to "
CompleteOrder". After that, create the child elements: "
CustomerCountry". The complete schema can be viewed in the image below:
Creating the test messages
In order to test our solution, we need to create some test messages. BizTalk Server is capable of creating these messages for us. Right-click on the "IncompleteOrder.xsd" schema in the Solution Explorer and select the "Properties" option. In the Properties, select the property "Output Instance Filename". In this field, type the value "C:\IncompleteOrder.XML". Click OK to close the window. Right-click on the schema file again in Solution Explorer and select the "Generate Instance" option. Open Windows Explorer and check if the file was created in the indicated place.
If you check the file created in Visual Studio, you'll see that the values generated are random. We're going to modify these values to use some valid information. Replace the
OrderId field to the number 1. In the
OrderDate field, type the value "2005-03-01", and in the field "
CustomerId", the value "ALFKI". The XML file should have a similar structure as the file below:
Save the file, we'll need it later.
Creating the structure to have access to the database
In order to use the SQL Server resources, we'll need to create a Stored Procedure capable of returning the data from the customer that we will place in the order.
Open the Enterprise Manager of SQL Server and select the Northwind database. Select the "Stored Procedures" applet, and right-click it, select the option "New Procedure". A new procedure should be created as follows:
CREATE PROCEDURE proc_GetCustomer
SELECT * FROM Customers
WHERE CustomerId=@CustomerId FOR XML AUTO, XMLDATA
Don't forget to include the
XMLDATA parameter in the end of the procedure, this will generate the necessary information for the SQL adapter. This parameter will be removed later.
Adding the structure to call SQL Server
Now that we've created all the necessary structures for the solution, we're going to create the structure to call SQL Server. For this, we will create a new item generated from the Solution Explorer. Right-click on the project in the Solution Explorer and select the "Add Generated Items" option. In the list of items, select the Add Adapter option and click on the Open button. The following screen will show up:
In this screen, select the adapter of type "SQL" and click Next (the other options can stay with default values, unless the database of your BizTalk server is in a remote server).
In the first screen, click on the Set button and provifde the information to connect to your SQL Server instance. Select "Northwind" as the initial catalog. When the connection string is set, click Next.
Th next screen shows information about the schemas that will be used to transport the information to and from SQL. In the Target Namespace option, type "http://nwtraders". In the Port-Type, select "Send Port", since we're going to send a request to SQL Server and receive a response. In the property "Request root element name", type "InCustomer", and in the property "Response root element name", type "OutCustomer". The screen should be as in the picture below:
Click on the Next button. In the screen "Statement type information", select the option "Stored Procedure". Click Next. In the combo box for selecting the procedure, select
proc_GetCustomer. The stored procedure parameters will show up. Click on the first parameter (near the check box... do not check the check box, just click near it until the prompt appears) to enter the parameter information. Type "ANTON", that is a valid customer ID. Click on the Generate button and you will see that the script used to execute the procedure will show up in the bottom of the screen. The result can be observed as in the image below:
This information will be used by the SQL adapter to generate the initial schema, they are not used later in the project. Click on the Next button, and in the next screen, click the Finish button. A new schema and a new orchestration will be created in your project.
The created schema (
SQLService) contains the request and response information for the stored procedure. The orchestration contains some types (port type) used to call the SQL adapter.
Updating the orchestration
Right-click on the orchestration in Solution Explorer and select the "Rename" option. Rename it to "ProcessOrder.odx".
Open the orchestration file and click on the white area in the orchestration designer (between the red and green indicators that indicate the end and beginning of the process). Check the property windows, and change the
TypeName property from
Creating the necessary messages
In order to use our messages within the orchestration, it's necessary to create message variables. To do this, we'll need the Orchestration View window. Click on the "View" menu, select "Other Windows", and select the "Orchestration View" window.
In the orchestration view, right-click on "Messages" folder and select the New Message option. In the identification, type "msgIncompleteOrder" and select the "OrderManager.IncompleOrder" and its schema (the schema is available in the schemas item).
Create three additional messages with the following identifiers/schemas:
OrderManager.procedureRequest (no item multipart messages)
OrderManager.procedureResponse (no item multipart messages)
Creating orchestration elements
Now, we will create the elements used in the orchestration. In the toolbox, search for the "Scope" shape and drag it to just below the green indicator in the designer area. Rename the scope shape to "Do Updates". Change its transaction type to "None".
Now, drag a "Receive" shape from the toolbox to the area inside the scope shape. In the shape properties, set the properties below:
Receive Incomplete Order
Just below the receive shape, drag a "Construct Message" shape. Use the following properties:
Name - Create SQL Request
Messages Constructed -
Now, drag a "Transform" shape inside the empty area inside the "Construct Message" shape. Select the "Input Messages" property and click the (...) button. A new window will open up with the mapping options. In the "Fully Qualified Map Name" box, type "ProcessOrder.IncompleteOrder_To_SQLRequest". Click on the "Source" option and select the "msgIncompleOrder" as the source message. Click on "Destination" and select the "msgGetCustomerReq" as the destination message. Your "Transform Configuration" screen should look like the one below. When you finish, click OK.
In the map editor, drag the
CustomerID field from the
IncompleteOrder schema to the
CustomerID field on the
GetCustomerReq schema. Note that the destination schema represents the parameters used to call the stored procedure. Your map should look like the picture below:
Save the map and go back to the orchestration file. Change the name of the "Transform" shape to "Create Request".
Now, we'll create a "Send" shape that will send our request to the SQL Server Adapter. Drag a new "Send" shape just below the construct message shape and use the following properties:
Send SQL Request
After the Send Shape, create a new Receive Shape with the following properties:
Receive SQL Resp
After the Receive shape, drag a new construct message with the following properties:
Messages Constructed -
Drag a new "Transform" shape to our newly created "Construct" shape. Select the property "Input Messages" and click on the (...) button. The transform configuration window will show up again. In the fully qualified name field, type ProcessOrder.SQL_To_CompleteOrder. In the Source option, select the "msgGetCustomerRep" and the "msgIncompleteOrder" (that is, two messages as source). Select the "Destination" option and select the "msgGetCustomerReq" option. The screen should look like the one below:
Click OK and in the map editor, create a map with the following links:
Close and save the map, and go back to the orchestration. Change the name of the "Transform" shape to "Create Response".
Now, we'll create a new "Send" shape that will send the final response to our customer. Create a new "Send" shape below the Construct Message shape, with the following properties:
At this point, your orchestration should look like the one shown below:
Creating the logical ports
Now that we've created the shapes for our orchestration, we will create the logical ports. Right-click the "Port Surface" (on the left) of the orchestration and select the "New Configured Port" option. The "New Port Wizard" will show up to create the logical port and port types for us, click Next on the first screen. On the next screen, type "rpReceiveIncomple" at the Port Name box and click "Next". In the port information screen, make sure the "Create New Port Type" option is selected, and type rpReceiveIncompleteType as the port type name. Leave the other options in their default values and click Next. In the communication direction options, leave the default "I'll always be receiving messages on this port" option selected, click Next, and the Finish.
Click on the port surface again (now on the right side) and select the "New Configured Port" option again. The wizard will show up again, click Next. On the next screen, type srpGetCustomer as the port name, and click Next. On the next screen, select the "Use Existing Port Type" option and select the "OrderManager.SQLExec" port type. This port-type is created by the SQL adapter and contains the necessary configuration to call our stored procedure. Click Next. In the communication direction option, select the "I'll always be sending a request and receiving a response" option, click Next, and then Finish.
Now, let's create our last port (whew!). Click on the "Port Surface" again (right side now) and select "New Configured Port" option. Our "already-known" wizard will show up again... click Next. In the port name box, type spSendCompleteOrder, click Next. In the port information screen, select the "Create New Port Type" option, and in the port type (on the next screen), type spSendCompleteOrderType. On the "Communication Direction" option, select the "I'll always be sending messages on this port" option. Click Next, Finish, and we're ready!
Connecting the ports to the receive/send shapes
To finish our orchestration we need to connect the receive and send shapes to our ports. To do this, simply drag the connectors from each shape to each logical port on the port surface. The orchestration should look like in the image below:
Configuring the assembly
Now that our orchestration is ready, we need to configure our assembly so that it can be used by BizTalk. To do this, we need to generate a pair of keys for it, using the SN.EXE application. In the Start menu, look for the Visual Studio 2003 folder, and select the "Visual Studio .NET 2003 command prompt". In the command prompt, change the current directory to C:\ and type "sn - k key.snk". This command should generate our key-pair. In the Solution Explorer, right-click on the project in the Solution Explorer and select "Properties". In the properties of the project, select the "Assembly" tab, and in the item Assembly Key File, type C:\key.snk, as in the image below:
Now, right-click on the project and select the "Build" option. If there are no errors, right click on the project again and select the "Deploy" option. Our project should be deployed to BizTalk.
Creating the physical ports
Now, we're going to configure BizTalk to receive the messages and send them to our orchestration. In Visual Studio, select the View menu and select the "BizTalk Explorer" option, a new window will show up with the BizTalk Explorer. Expand your BizTalk server instance name and right-click on "Receive ports", selecting the "Add Receive Port" option. On the Port Type, select "One-way Port". In the Port Name box, type rpReceiveOrder and click Ok. You'll see that our new port will be created.
Now, we'll create our receive location for this port. Expand the port you have created in the previous step and right-click on "Receive Locations". In "Transport Type" property, select "FILE". In the "Address (URI)" option, click on the (...) button and set the receive folder as "C:\Receive" (or the folder of your choice). Click OK. In the Receive Handler property, select "BizTalk Application", and in the "Receive Pipeline" property, select "
Microsoft.BizTalk.DefaultPipelines.XMLReceive". Click OK to create the receive location. It will be necessary to create the C:\Receive folder, in order to get the solution working. After you create the receive location, right-click on it and select "Enable".
Now, we'll create the send port to send information to SQL Server. Right click on the "Send Port" and select the "Add Send Port" option. On the Port Type, select "Static Solicit-Response" port. On the Port Name, type spSQL, and in the Transport Type, select SQL. In the Address (URI), click on the (...) button. Complete the "SQL Transport Properties" screen with the information below. Take care of the "Document Namespace" property and the "Response Root Element Name". These properties should contain the same namespaces used by the generated schema in the earlier steps.
On the Send Port properties, change the Send Pipeline property to
Microsoft.BizTalk.DefaultPipelines.XMLTransmit and the Receive Pipeline property to
Microsoft.BizTalk.DefaultPipelines.XMLReceive. Click OK and Finish.
Now, we'll create the last port. Right-click on "Send Ports" and select the "Add New Port" option. Select "Static One-way port" as the port type, and name it spSendCompleteOrder. In the Transport Type option, select FILE. In the Address (URI) option, click on the (...) button and set C:\Output as the destination (this folder should be created manually). Click OK and set the "Send Pipeline" property to "
Microsoft.BizTalk.DefaultPipelines.XMLTransmit". Click OK to create the port.
Now, right-click on each one of the ports and select the "Start" option.
Binding the Orchestration
Now, we need to bind our orchestration to the physical ports. In the BizTalk Explorer, expand the Orchestrations applet and right-click on our orchestration, selecting the "Bind" option. Set the binding information as in the image below:
On the Host configuration, select the default host (BiztalkApplicationHost). Click OK.
Right-click on our orchestration and click "Start". Click OK on the Express Start window as well.
Testing the Orchestration
Before we start testing our orchestration... do you remember that stored procedure we created in the earlier steps? Well, in order to get our solution working, we need to change some things on it. Go to the Enterprise Manager and edit the procedure, removing the
XMLDATA option in the
SELECT statement. This option is used to generate the schemas for BizTalk. Since our schemas are already created, we don't need this option anymore.
Now, get the input file created in the earlier steps and put it in the C:\Receive folder. The file should be extracted. Go to the C:\Output folder and wait until the complete order message appears. Hope you like the article!