Create a SQL Server LOB target to insert invoice

 

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:

• What are logic apps

• 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 invoice data into the Invoice 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

1. From the Server Explorer, expand the BizTalk Adapter Service management service URL, expand LOB Types, right-click SQL, and select Add SQL Target.

   

   The Add a Target wizard starts.

   1. Read the information on the Before You Begin page, and then select Next.

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

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

      Click Next.

   3. On the Operations page, from the left box, expand Tables, expand Invoice, select Insert, and then select the RIGHT ARROW. Notice that the Insert operation is listed under the Selected operations section. Select Next.

   4. 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, specify the same credentials that you use to connect to the computer running SQL Server, and then select Next.

   5. On the Deployment page, 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 some 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.

      In the step Create a SQL Server LOB target to insert purchase order, we already created an LOB Relay to insert purchase order messages into the CloudCarDatabase. We can use the same LOB Relay to insert the invoice data as well. Select the option to use existing relay and then select cloudcarsb/sqltarget. Even though you use an existing relay, you must enter a target sub-path to make the target unique. For this tutorial, for creating SQL LOB Target to write to the Invoice table, enter the Target Sub-path as invoice.

      Click Next.

   6. On the Summary page, review the values you specified in the previous steps, and then select Create.

   7. When the wizard completes, click Finish.

2. In the BizTalk Service project, include the schemas for the Insert operation on the Invoice table. Make sure the CloudCar_Integration_Invoice project is selected in the Solution Explorer when you perform the following steps.

   1. Right-click the SQL LOB Target you created for the Invoice table, and select the option to add schemas to the project.

   2. For File name prefix, enter Invoice_.

   3. Retain the folder name as LOB Schemas.

   4. For Credential Type, select Windows, and then select OK.

3. Finally, set the security property for the relay endpoint.

   1. Right-click the LOB Target in Server Explorer and select Properties.

   2. In the Properties grid, select the ellipsis (…) against the Runtime Security property.

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

   4. Click OK.

4. Save changes to the project.

See Also

Create a BizTalk Service project to process invoices