Create a SQL Server LOB target to insert purchase order
Important
Microsoft Azure BizTalk Services (MABS) is being retired, and replaced with Azure Logic Apps. If you currently use MABS, then Move from BizTalk Services to Logic Appsprovides some guidance on moving your integration solutions to Logic Apps.
If you're brand new to Logic Apps, then we suggest getting started here:
-
Create your first logic app, or quickly get started using a pre-built template
-
View all the available connectors you can use in your logic apps
This topic provides instructions on how to create an SQL Server LOB Target within the BizTalk Service project. You use this LOB Target to insert customer order data into the Orders table in the CloudCarDatabase. You must have already created the database and the table by using the CreateDatabaseSchema_CloudCar.sql script provided with the sample available in the MSDN code gallery at https://go.microsoft.com/fwlink/p/?LinkId=324220.
To create an SQL Server LOB target
Add a BizTalk Adapter Service server. This is the server where you installed the Runtime component of BizTalk Adapter Service. To add a BizTalk Adapter Service server, from the Server Explorer in Visual Studio, right-click BizTalk Adapter Services, and select Add BizTalk Adapter Service. In the Add BizTalk Adapter Service dialog box, enter the URL of the WCF service that monitors that Service Bus relay service, and then select OK.
.jpeg "Add Service Bus Connect Server")
Because you have all the components of BizTalk Adapter Service installed on the same computer, the URL for that service will be https://localhost:8080/BAService/ManagementService.svc/.
Note
If you installed BizTalk Adapter Service Runtime component on a separate computer, you must replace ‘localhost’ in the above URL with the name of that computer.
Expand the newly added BizTalk Adapter Service management service URL, expand LOB Types, right-click SQL, and select Add SQL Target.
.jpeg "Add an SQL LOB Target")
The Add a Target wizard starts.
Read the information on the Before You Begin page, and then select Next.
On the Connection Parameters page, enter the details for the SQL Server to connect to and the credentials to use for the connection.
For the Server field, enter the name of the computer where SQL Server is installed and for the database (Initial Catalog), enter CloudCarDatabase.
Note
You can use the Advanced button to build the SQL Server connection URI and also enter the binding properties for the connection.
The SQL Server Connection URI provides additional information about how to build the URI. For information about binding properties, see Working with BizTalk Adapter for SQL Server Binding Properties.
For this tutorial, leave the default setting as-is for the binding properties.
Enter the credentials to connect to the SQL Server database. You must have already added that user as a system administrator in the SQL Server database.
Select Next.
On the Operations page, from the left box, expand Tables, expand Orders, select Insert, and then select the RIGHT ARROW. Notice that the Insert operation is listed under the Selected operations section. Select Next.
In the Runtime Security page, enter the security type. This security type determines how the client message is authenticated with the LOB Target. Options include:
Fixed Username
Select this option if you are using a username and password created locally on the LOB system.
Fixed Windows credential
Select this option to use a Windows domain account.
Custom SOAP Header
Select this option if you create a custom SOAP header to include the username and password.
Message Credential
Select this option if you are including the logon credentials in the WS-Security header of the message.
For this tutorial use the Fixed Username option, enter the same credentials that you use to connect to the computer running SQL Server, and then select Next.
On the Deployment page, choose an existing LOB Relay or create a new LOB Relay. To create a new LOB Relay, enter the following details:
Namespace
Enter the Service Bus namespace on which the LOB relay endpoint is created.
Issuer Name
Enter the issuer name for the Service Bus namespace.
Issuer Secret
Enter the issuer secret for the Service Bus namespace.
Relay Path
Enter a name for the relay. For this tutorial, enter SQLtarget.
Target Sub-path
Enter a sub-path to make this target unique. For this tutorial, enter orders.
Target runtime URL
This read-only property displays the URL where the relay is deployed on Service Bus. This is the endpoint where you can send a message with a compliant schema to be inserted into the on-premises SQL Server.
Select Next.
On the Summary page, review the values you specified in the previous steps, and then select Create.
When the wizard completes, select Finish. The following activities occur in the background:
An LOB Target is created in Server Explorer. It can be disabled, started, and deleted. Its configuration can also be exported.
An LOB Target is created as an application in IIS. This application uses the Runtime for this specific LOB Target. Runtime Components: BizTalk Adapter Service describes the IIS components.
In the BizTalk Service project, include the schemas for the Insert operation on the Orders table. Make sure the CloudCar_Integration_PurchaseOrder project is selected in the Solution Explorer when you perform the following steps.
Right-click the SQL LOB Target you created for the Orders table, and select the option to add schemas to the project.
For File name prefix, enter PO_.
Retain the folder name as LOB Schemas.
For Credential Type, select Windows, and then select OK.
Finally, set the security property for the relay endpoint.
Right-click the LOB Target in Server Explorer and select Properties.
In the Properties grid, select the ellipsis (…) against the Runtime Security property.
In the Edit Security dialog box, select Fixed Windows Credentials and enter username and password to connect to the SQL Server.
Select OK.
Save changes to the project.