Step 4: Create and Configure the LOB Target

Create LOB Relays and LOB Targets in your BizTalk Services application to connect to an on-premises LOB application. Development and Runtime Architecture: BizTalk Adapter Service describes how LOB Relays and LOB Targets manage connectivity with an on-premises LOB application.

This section lists how to create a SQL Server table where the sales order data is inserted, how to create a LOB Relay and target for the Insert operation on the SQL Server table, and how to generate the schema for the Insert operation on the SQL Server table. Specifically:

Create the SQL Server table

Create a SQL Server LOBTarget

Generate the Schema

Create the SQL Server table

Use these steps to create the OrderDetails table in SQL Server in which the sales order data is inserted. The script is also available in the FTP_EAI_Tutorial sample at https://go.microsoft.com/fwlink/?LinkId=247973. This script assumes that you already have an Orders database created.

1. Open SQL Server Management Studio.

2. Run the following script to create the OrderDetails table:

   USE [Orders]
   GO
   /****** Object:  Table [dbo].[OrderDetails]    Script Date: 04/02/2012 20:35:57 ******/
   IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderDetails]') AND type in (N'U'))
   DROP TABLE [dbo].[OrderDetails]
   GO
   USE [Orders]
   GO
   /****** Object:  Table [dbo].[OrderDetails]    Script Date: 04/02/2012 20:35:57 ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   SET ANSI_PADDING ON
   GO
   CREATE TABLE [dbo].[OrderDetails](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [OrderId] [varchar] (200),
   [QuantityOrdered] [int],
   [TotalAmount] [int]
   PRIMARY KEY CLUSTERED 
   (
   [Id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO
   SET ANSI_PADDING OFF
   GO
   

3. Confirm the table is created in the database.

Create a SQL Server LOBTarget

Create the LOB Relay and the LOB Target for the Insert operation on the OrderDetails table:

1. In the BizTalk Service project, from Server Explorer, right-click BizTalk Adapter Services, and then select Add BizTalk Adapter Service. This prompts for the BizTalk Adapter Service Management URL. Enter the management URL. The BizTalk Adapter Service Management URL is path to the ManagementService.svc WCF service hosted in IIS. Runtime Components: BizTalk Adapter Service provides more information on the BizTalk Adapter Service components within IIS.

   • If the BizTalk Adapter Service Runtime is installed locally with the default settings, enter: https://localhost:8080/BAService/ManagementService.svc/

   • If the BizTalk Adapter Service Runtime is installed remotely with the default settings, enter: https://ServerName:8080/BAService/ManagementService.svc/

   Select OK.

2. Expand the newly added server, expand LOB Types, right-click SQL, and select Add SQL Target. The Add a Target wizard opens.

3. In Before You Begin, select Next.

4. On Connection Parameters, enter the SQL Server details and the credentials to use for the connection. Select Next.

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

5. In Operations, expand Tables, expand OrderDetails, select Insert, and then select the right arrow. The Insert operation is now listed under the Selected operations section.

   Select Next.

6. In Runtime Security, select Fixed Windows credential, enter the credentials, and then select Next.

   This security type determines how the client message is authenticated with the LOB Target. Options include:

   Fixed Username	Using a username and password created locally on the LOB system.
   Fixed Windows credential	Use a Windows domain account.
   Custom SOAP Header	You create a custom SOAP header to include the username and password.
   Message Credential	You are including the logon credentials in the WS-Security header of the message.

7. In Deployment, choose an existing LOB Relay or create a new LOB Relay.

   Tip

   A single LOB Relay can be used with multiple LOB Targets. There are restrictions based on the security model. As a best practice, group the same security method in one LOB Relay. For example, use the same LOB Relay to host the LOB Targets that use Message Credential or Fixed Windows security type.

   To create a new LOB Relay:

   Namespace	Required. Enter your Service Bus namespace; the LOB relay is created in the Service Bus. The namespace name is listed in the Azure classic portal. For example, if myNamespace is the namespace, this updates the Management address to:https://MyServer:8080/BAService/ManagementService.svc/myNamepsace
   Issuer Name	Required. Enter a valid Service Bus Issuer Name.
   Issuer Secret	Required. Enter a validService Bus Issuer Secret key.
   Relay Path	Required. Enter the desired name of the relay path. For this tutorial, set this property to SQLLOBRelay.
   Target Sub-path	Required. Enter a sub-path to make this target unique. For example, you can enter OrderDetails.
   Target runtime URL	This is automatically populated with the namespace name, relay path, and target sub-path you entered. If using these examples, it is populated with something like: https://MyNamespace.servicebus.windows.net/SQLLOBRelay/OrderDetails

   Select Next.

8. In Summary, review your values. Select Create.

When the wizard completes, select Finish. The following activities occur in the background:

• The LOB Target is created in Server Explorer. It can be disabled, started, and deleted. Its configuration can also be exported.

• The 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.

To use the LOB Target:

1. Set the Runtime Security property for the relay endpoint:

   1. Right-click the relay endpoint in Server Explorer and select Properties.

   2. In Properties, select the ellipsis (…) next to the Runtime Security property.

   3. In Edit Security, select Fixed Windows Credentials, and enter username and password to connect to the SQL Server.

   4. Select OK.

2. Drag and drop the LOB Target onto the design area. Note the Entity Name property of the LOB Target. The default value is Relay-Path_target-sub-path. If using the examples above, it is sqllobrelay_orderdetails.

3. Open the .config file for the LOB target, which typically has the YourRelayPath_target-sub-path.config naming convention. Enter the Service Bus issuer name and issuer secret, as shown:

   <tokenProvider>
     <sharedSecret issuerName="owner" issuerSecret="issuer_secret" />
   </tokenProvider>
   

   Save changes to the config file.

Generate the Schema

Generate the schema for the Insert operation on the OrderDetails table:

1. In the BizTalk Service project, in the Server Explorer, right click the LOB Target you just created, and then select Add schemas to FTP_EAI_Tutorial. The Schema Generation dialog opens.

2. Set the file name prefix to FTP_EAI_Tutorial_. Leave the folder name to its default value of LOB Schemas.

3. Select credential type as Windows to use Windows authentication to connect to SQL Server, and then select OK.

   The schemas are added to the FTP_EAI_Tutorial project under the LOB Schemas folder.

See Also

Tutorial: Using BizTalk Bridges to Insert Flat File Messages into an On-premises SQL Server