Sql Adapter

Introduction 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.

The Example 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: "OrderId", "OrderDate", "CustomerID", "CustomerName", "CustomerAddress", "CustomerCity", "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. Rightclick 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-0301", and in the field "CustomerId", the value "ALFKI". The XML file should have a similar structure as the file below: <ns0:Order xmlns:ns0="http://OrderManager.IncompleteOrder"> 1 2005-03-01 ALFKI

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 (@CustomerId char(5)) AS SELECT * FROM Customers WHERE CustomerId=@CustomerId FOR XML AUTO, XMLDATA GO

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 Orchestration_1 to ProcessOrderOrch.

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:

Identifier msgCompleteOrder

Type OrderManager.CompleteOrder

msgGetCustomerReq

OrderManager.procedureRequest (no item multipart messages)

msgGetCustomerResp

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: • • •

Name - Receive Incomplete Order Message - msgIncompleteOrder Activate - True

Just below the receive shape, drag a "Construct Message" shape. Use the following properties: • •

Name - Create SQL Request Messages Constructed - msgGetCustomerReq

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: • •

Name - Send SQL Request Message - msgGetCustomerReq

After the Send Shape, create a new Receive Shape with the following properties: • •

Name - Receive SQL Resp Message - msgGetCustomerResp

After the Receive shape, drag a new construct message with the following properties: • •

Name - Construct Response Messages Constructed - msgCompleteOrder

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: • •

Name - Send Response Message - msgCompleteOrder

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! :)