Using EAI Bridges and LOB Target to connect to on-premises SQL Server

Like I mention in one of my last posts, I decided to publish all my demos that I have been using in my sessions in BizTalk Innovation Event series. This will be the first demo.

This demo is a basic integration scenario: “Event registration” and intends to show some of the Enterprise Application Integration (EAI) capabilities in the cloud and how we can connect with an on-premise system using LOB Targets and Service Bus Connect. Basically, we will send messages to the cloud, then they will be processed and transformed through a bridge deployed on Service Bus (EAI Bridge), and then inserts the message into an on-premises SQL Server database. Finally, we will get the response back – So a very simple scenario.

Because I’m also a BizTalk Developer, in this post, I will make comparisons between this type of project and BizTalk projects whenever possible. I will try to demystify some preconceptions and fears: I’m a BizTalk developer, do I need to learn a new technology? It will be difficult to use this SDK or to migrate my application to the cloud?

Maybe at the end, you will have your question or doubts answered.

Create Service Bus EAI Project

The first thing, of course, is to create a new Visual Studio project:

  • Open Visual Studio 2010, on the File menu, point to New, and then click Project.
  • Under “Installed Templates”, select “Visual C# –> ServiceBus” template
  • And then select “Enterprise Application Integration” project type
  • Finally, give a name to your project

Enterprise-Application-Integration-project-type

By default “BridgeConfiguration.bcs” is open after we create a new project. For now, we will close this file (window).

Create the input schema and generate the SQL Schemas

If you are a BizTalk developer, you are accustomed using the BizTalk Editor to create, edit, and manage schemas that you will use in your application.

You will have almost the exact same experience when creating schemas to use in your Service Bus EAI projects. Why? Because basically is the same editor Sorriso

So to create a new schema:

  • Right-Click in your EAI Project and select the option “Add –> New Item…”
  • Select “Schema” and give a name to your schema
  • Click “Add”

EAI-Project-Add-Schema

I will not explain all the steps to describe how to create the schema structure, because like I said before it is the exact same experience:

  • Add Records, Attributes, Elements and so on
  • Import schemas and so on
  • Max and min occurs and so on
  • We even have Promote option and this option is not used this type of project 😀

service-bus-Schema-editor

In this demo, our input schema is a basic schema describing persons:

  • A best practice always is to rename the root node, in your sample, rename to “Persons”
  • Create a new record called “Person”
    • Max Occurs: unbounded
    • Min occurs: 1
  • Under “Person” create the following structure:

SB-EAI-Person-Schema

  • The “Phone” record, in this case, occurs one time

The next step is to generate the SQL Schemas. So if you are a BizTalk developer in this step we created the new item using the “Add Generated items…” and then we chose the WCF-SQL adapter…

Note: The source code of the project that is available to download will contain all the scripts to create the database used in this demo.

SB-SQL-Table-Demo1

In this type of project, we don’t have this option. We will have the same functionalities to generate the SQL Schemas automatic but in very different way.

In order to connect to an on-premise LOB application, you must create LOB Relays and LOB Targets as part of your EAI application:

  • In the Enterprise Application Integration project, from “Server Explorer”, expand “ServiceBus Connect Servers”
  • Then expand the server (URL), and the “LOB Types”
  • Right-click SQL type, and select “Add SQL Target”.

add-sql-target

  • The Add a Target wizard starts. In the welcome page, click “Next”.
  • On the Connection Parameters page, specify the details for the SQL Server to connect to (name of the server, the SQL instance and the name of the database (Catalog)) and the credentials to use for the connection (leave the option Use Windows credentials checked.). Click “Next”.

Add-SQL-target-Connection-Parameters

  • On the Operations page we will specify the operations that we want to provide, in this demo I will choose only the insert operation – You will notice also that this step is almost identical to what we do when we use the option “Add Generated items…” in BizTalk project because we are actually using the same adapter:
    • From the left box, expand Tables, expand “Tables à Person”, select Insert, and then click the right arrow. The Insert operation must now be listed under the “Selected operations” section.

Add-SQL-target-operations

  • Click Next.
  • In the Runtime Security page, specify the security type. This security type determines how the client message is authenticated with the LOB Target.
    • In my case, I will use ” Fixed Windows credential” option

Add-SQL-target-Runtime-Security

  • On the Deployment page, choose an existing LOB Relay or create a new LOB Relay.
    • 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.
  • In this demo, we will create a new LOB Relay, so we need to choose the option “Create new” and set the following parameters:
    • Namespace: Enter your service namespace that you specify in your subscription in the Azure Management Portal after you sign-in. For example, my namespace is “sandroazureeaiedibus”
    • Issuer Name: The Service Bus Issuer Name. For now, this is always “Owner”.
    • Issuer Secret: The Service Bus Issuer Secret key. Issuer Secret is available in the Azure Management Portal after you sign-in.
    • Relay Path: Enter the desired name of the relay path. For this tutorial, set this property to “SQL”. This will update the Management address to be:
      • http://MySBConnectServer:8080/SBConnect/ManagementService.svc/myNamepsace/SQL
    • Target Sub-path: Enter a sub-path to make this target unique. In this demo, we will use “SQLDemo”.

Add-SQL-target-deployment

  • Click “Next”.
  • On the Summary page, review the values you specified in the previous steps, and then click “Create”.
  • When the wizard completes, click 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: ServiceBus Connect Service Runtime Components describes the IIS components.

Add-SQL-target-Completion

  • Click “Finish”

Now under the SQL types we will have our LOB Relay and LOB Target created:

sql-target-explorer

But if we look to your Visual Studio project we don’t have the SQL schemas available, to accomplished that we need to import them by:

  • Select the Project name, in my case “SQL Demo”
  • Is your LOB Target created previous, right-click and select the option “Add Schemas to <project name>..”

Add-Schemas-to-project-name

  • This will open a new window where you can define:
    • A file name prefix, in my case I put “sqldemo_”
    • Folder Name, leave the default
    • And set the Lob credentials, leave the default value

Schema-generation-Import-to-project

  • Click “OK”

You will notice that a folder with the SQL Schemas was created in your project.

Create a map

The next step is to create a map that will transform the input message “Person” in the desired message type to send to the SQL:

  • Right-Click in your EAI Project and select the option “Add à New Item…”
  • Select “Map” and give a name to your map
  • Click “Add”

Again if you are a BizTalk developer at first glance the map editor seems to be the same that we used in BizTalk… but although the concept is the same: selection of schemas, grids, pages, schema navigation and so on, this is a very very different editor! For start:

  • We don’t have functoid but instead, we have Operations… And now you think … ok, the name changed… yes is true but we have now:
    • Normal operation that we are used to, like, string manipulation and arithmetic expressions
    • Very different operations like looping or list functionality
    • The lack of others like scripting
    • And amazing operations like “if-then-else expression” and Date/Time operations
  • XSLT and custom code is not supported in this version – so we don’t have scripting operations
  • We can use the Transform Designer, to define relationships between an input XML document and an output XML document using links and Map Operations. But instead of generating XLST code like the BizTalk Mapper Editor, the maps are created using Extensible Application Markup Language (XAML).

In this transformation we need to map all records “Person” to the destination “Person” record, so we need to:

  • Drag MapEach Loop Operation from the Toolbox window to the Transform Designer surface (or map grid)
    • This operation is similar to Looping functoid in BizTalk but with a strange aspect Sorriso

MapEach-Loop-operation

  • Then link the “Person” in the origin to the MapEach Loop Operation and then the MapEach Loop Operation to the record “Person” in the destination schema, like the picture above show.

NOTE: You will notice in the MapEach Loop Operation that there is an icon with a blue arrow. By default, when a MapEach Loop is added to the Transform Designer surface the Scope is activated (indicated by the blue arrow), this means that all items added to the Transform Designer surface will perform under the scope of that Loop Map Operation. To work in the parent scope, we need to select the arrow to unset the scope (indicated by a gray arrow). For details on working within the scope of a Loop Map Operation, refer to Working Within the Scope.

  • With the MapEach Loop Operation scope activated:
    • Drag a link from the element “CitizenCard” in the origin schema to the element “CitizenCard” in the destination schema
    • Drag a link from the element “ZipCode” in the origin schema to the element “ZipCode” in the destination schema
    • Drag a link from the element “Email” in the origin schema to the element “Email” in the destination schema
    • Drag 3 String Concatenate Operations to the MapEach Loop Operation (like the picture bellow show)
      • Link the “FirstName” and “LastName” from the origin schema to the first String Concatenate Operation and then link the this String Concatenate Operation the element “FullName” in the destination schema.
      • Link the “AddressLine1” and “AddressLine2” from the origin schema to the second String Concatenate Operation and then link the this String Concatenate Operation the element “Address” in the destination schema
      • Link the “CountryCode”, “Prefix” and “Number” from the origin schema to the third String Concatenate Operation and then link the this String Concatenate Operation the element “PhoneNumber” in the destination schema

SB-EAI-Mapper

    • Edit the first and second String Concatenate Operations adding a space to separate the two elements. This process is exactly equal to BizTalk: double click in the operation, then we need to add a new map operation input with space.

configure-string-concatenate-operation

Configuring and deploying an EAI Bridge

The last point we must do is to create our EAI Bridge (commonly referred to as pipelines). The term ‘bridge’ immediately reminds us of something which connects two end points. In the context of information systems here we are talking about a bridge connecting two or more disparate systems.

The definition of Bridge is a Service Bus EAI and EDI component that processes, transforms, and transports data between two disparate systems.This component applies the VETER pattern that means for: Validate – Enrich – Transform – Enrich – Route (VETER)

Like I mention in the beginner this will be our goal: we will send messages to the cloud, then they will be processed and transformed through a bridge deployed on Service Bus (EAI Bridge), and then inserts the message into an on-premises SQL Server database. Finally, we will get the response back – So a very simple scenario

EAI-Bridges-integrate-with-SQL

To accomplished that we need to:

  • On the solution explorer window, open the bridge configuration surface file: “BridgeConfiguration.bcs
    • If I have to make a comparation between BizTalk, this file, for me, will be like the BizTalk Administration Console where we create our receive port and receive location, define the pipeline associated with this location, where we are able to set a transformation, create filters to subscribe the messages and create send ports – of course without orchestrations.
  • The first thing we need to do is to set our Service Namespace:
    • Right-click anywhere on the bridge configuration surface and click “Properties” and then for the “Service Namespace” property, specify the registered service namespace.
  • If we open the Toolbox windows, we will have almost all the components that we can add (drag) to bridge configuration surface: Bridges, Route Destinations and Sources. The next step is to an XML Request-Reply Bridge:
    • Drag and drop an XML Request-Reply Bridge from toolbox to the Bridge Configuration surface.

EAI-Bridge-XML-Request-Reply

    • Double click the XML Request-Reply Bridge on the bridge configuration surface to configure the bridge.

EAI-Bridge-configure-bridge

    • On the XML Request-Reply Bridge design surface, within the “Message Types” box, click the add icon “plus” (+) to open the “Message Type Picker” dialog box.
    • In the “Message Type Picker” dialog box, from the “Available message types” box, select the “Persons” message type, click the right arrow icon “right arrow” to associate the request schema with the Bridge, and do the same for the Reply Message type but this time you should select the “InsertResponse” message type and then click “OK”.

EAI-Bridge-Message-Type-Picker

  • In this basic scenario with only have one destination, so we don’t need to route the messages based on some properties or conditions, so we don’t need the Enrich the message, we only go to transformation the input message to the schema expect by the SQL Insert operation.
  • Within the Transform stage, select the “Xml Transform” activity, and then from the Properties window click the ellipsis button (…) against the “Maps” property to open the “Map Selection” dialog box.
    • From the list of maps displayed in the dialog box, select PersonsToSQLPersonsInsert.trfm and click “OK”.

EAI-Bridge-Map-Selection

  • Save the bridge configuration and go back to the Bridge Configuration designer surface.
  • Now we need to add to the Bridge Configuration designer surface the SQL LOB Target that we create earlier, to accomplish that we need to:
    • Drag and drop an SQL LOB Target (sandroazureeaiedibus/sql/sqldemo) from Server Explorer windows to the Bridge Configuration surface.

EAI-Bridge-add-SQL-LOB-Target

  • To connect the LOB Target to the XML Request-Reply Bridge we need to go to the Toolbox Windows, select the Connection component from bridges and link the small circles that exist in the components

EAI-Bridge-connection

  • The final step is to set the filter condition and the route action on the connection between the bridge and the SQL LOB Target entity. To set the filter condition we need to:
    • Click the connection between XML Request-Reply Bridge and the SQL LOB Target entity.
    • In the Properties window, click the ellipsis (…) button for Filter Condition.
    • In the Route Filter Configuration dialog box, set the filter condition to “Match All”.
    • Click “OK”.

EAI-Bridge-route-filter-condition

  • To set the Route action so that the outgoing message to the LOB application has a SOAP action header.
    • Open Server Explorer and navigate to the SQL LOB Relay we created earlier. Right click the relay, click Properties, and for the Operations property, copy the value of the first operation.
    • On the Bridge Configuration surface, click the connection between XML Request-Reply Bridge and the SQL LOB Target entity.
    • In the Properties window, click the ellipsis (…) button for Route Action. In the Route Actions dialog box, click Add to open the Add Route Action dialog box. In the Add Route Action dialog box, do the following:
      • Under Property (Read From) section, select Expression, and then paste the value that you copied. Important: You must always specify the value for an expression within single quotes.
      • Under Destination (Write To) section, set the Type to SOAP and the Identifier to Action.
      • Click OK in the Add Route Action dialog box to add the route action. Click OK in the Route Actions dialog box

EAI-bridge-Add-route-Action

  • And then click Save to save changes to an Enterprise Application Integration project.

Now we are ready to build our solution and deploy to the cloud.

The sample code is available for download in Code Gallery:.

Using EAI Bridges and LOB Target to connect to on-premises SQL Server (201.2 KB)
Microsoft Code Gallery

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

13 thoughts on “Using EAI Bridges and LOB Target to connect to on-premises SQL Server”

    1. Hi Fernando,

      I don’t say that in the future these features will also be available in on-premise service bus… but for now these features are only available in Windows Azure Service Bus in lab environment (https://portal.appfabriclabs.com/), so you cannot deploy to an on-premise SB.

      This also means that these features are not yet available in Windows Azure Service Bus, these is a CTP (Community Technology Preview) version.

      1. Bummer 🙂

        But i think Microsoft has stated that everything that is in Windows Azure Service Bus will also be available on-premise as soon as it is fully tested in Windows Azure Service Bus.

        By the way i liked this post.

  1. very nice! I am just now working thru the demo but I am getting an error when I hit the last step to create

    500

    Code: ’13’
    Message: ‘Error occurred while trying to bring up the relay service. Error Message: ‘No DNS entries exist for host btsdemo0597-bts.servicebus.windows.net.

    Server Error in ‘/btsdemo0~_orderproce~_a0e6950a190945b0904deb6ce2fc5df4’ Application.

    No DNS entries exist for host btsdemo0597-bts.servicebus.windows.net.

    Description:
    An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details:
    System.ServiceModel.EndpointNotFoundException: No DNS entries exist for host btsdemo0597-bts.servicebus.windows.net.

    I just wanted to know if anyone had seen this issue before??

    thanks!!!

    1. You probably should check your service issuer name and secret are correct and the DNS server is running and if they are set properly. The error message seems ServiceBus could not find DNS entries.

      There is also a projectname.config that you should configure the transportClientEndpointBehavior element

  2. Sandro thanks very much for the reply! I was able to resolve this by adding a dns entry to my host file on my development VM. my latest issue is that after successfully deploying the project I am getting an object reference not set to an instance of object when testing the deployed project with messagesender.exe, Interestingly enough I am not able to see my deployed bridge in the management console! any thoughts or assistance would be appreciated. as I mentioned the project does build and deploy successfully . thanks again

        1. sandro:
          would you be available for a couple of hours of consulting tomorrow?

          I am still having issues with this and really need to get it resolved.

          please let me know.

          thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top