Robust Cloud Integration with Azure

For the last year I have been busy co-authoring this book on Azure Cloud Integration with my follow co-authors Abhishek Kumarm, Martin Abbott, Gyanendra Kumar Gautam, James Corbould and Ashish Bhanbhani.

Image result for robust cloud integration with azure

It is available on the Packt website here: https://www.packtpub.com/virtualization-and-cloud/robust-cloud-integration-azure

This book will teach you how to design and implement cloud integration using Microsoft Azure. It starts by showing you how to build, deploy, and secure the API app. Next, it introduces you to Logic Apps and helps you quickly start building your integration applications. We’ll then go through the different connectors available for Logic Apps to build your automated business process workflow. Its packed with a lot of information spanning just under 700 pages.

Don’t forget to check out another publication I co-authored back in 2015 with with Mark Brimble, Johann Cooper and Colin Dijkgraaf called SOA Patterns with BizTalk Server 2013 and Microsoft Azure.

SOA Patterns with BizTalk Server 2013 and Microsoft Azure - Second Edition Book Cover

And it is still available from the Packt website here: https://www.packtpub.com/networking-and-servers/soa-patterns-biztalk-server-2013-second-edition

Hope you enjoy reading it, just as I enjoyed writing the content.

Using SQL2008 Data Table Type & BizTalk to insert parent-child rows

I wanted a way to insert a parent row with multiple child rows in one single transaction into a database using BizTalk 2010.

The solution I came up with uses the table data type in SQL 2008 to insert parent-child relationship records and the BizTalk WCF SQL Adaptor. The data I will be inserting will consist of a Sales Order header record with several sales item lines.

Below are the steps I used to accomplish this.

1. Create the tables for the Sales header record and the Sales Item records.

CREATE TABLE [dbo].[SalesHeader]
(
    [SalesHeader] [int] IDENTITY(1,1) NOT NULL,
    [OrderNumb] [nvarchar](50) NOT NULL,
    [OrderDate] [datetime] NOT NULL
)

CREATE TABLE [dbo].[SalesItem]
(
    [SaleItem] [int] IDENTITY(1,1) NOT NULL,
    [SalesHeaderID] [int] NOT NULL,
    [ItemCode] [nvarchar](255) NOT NULL,
    [Qty] [int] NOT NULL,
    [Price] [money] NOT NULL
)

go
--add the FK Cconstraint
ALTER TABLE [dbo].[SalesItem]  WITH CHECK ADD  CONSTRAINT [FK_SalesItem_SalesHeader] FOREIGN KEY([SalesHeaderID])
REFERENCES [dbo].[SalesHeader] ([SalesHeader])
ALTER TABLE [dbo].[SalesItem] CHECK CONSTRAINT [FK_SalesItem_SalesHeader]
go

2. Create the table data types in SQL. One for the header record and another for the order lines. These new table types in SQL2008 allow you to pass multiple rows in as a single parameter. In the old  days you would have to pass the data in as a XML document or use updategrams.

CREATE TYPE [dbo].[SalesHeaderTableType] AS TABLE(
    [OrderNumb] [nvarchar](255) NULL,
    [OrderDate] [datetime] NULL
)
go
CREATE TYPE [dbo].[SalesItemTableType] AS TABLE(
    [ItemCode] [nvarchar](255) NULL,
    [Qty] [int] NULL,
    [Price] [money] NULL
)
go

3. Create the stored procedure to insert the data into the tables using the table data types as the input parameters. Also I am using the scope_identity function to get the parent header id. Note table parameters can only be used as read only.

CREATE procedure [dbo].[InsertSalesOrder]
(
    @SalesHeader as dbo.SalesHeaderTableType READONLY,
    @SalesItem as dbo.SalesItemTableType READONLY
)
as

declare @SalesHeaderID int

--add the sales header record
insert into dbo.SalesHeader(OrderNumb,OrderDate)
select OrderNumb, OrderDate from @SalesHeader
select @SalesHeaderID = scope_identity()

--add the line items
insert into dbo.SalesItem (SalesHeaderID,ItemCode,Qty,Price)
select @SalesHeaderID,ItemCode,Qty,Price from @SalesItem

return @SalesHeaderID
go

4. The next step is to generate the schema for the stored procedure created above using the “Consume Adapter Service” wizard.

image

This will create the following 3 files Sales.DataSetSchema.xsd, Sales.Procedure.dbo.xsd and Sales.TableType.dbo.xsd

5. Create the schema for the input data which is just a simple Header – Detail  data structure as shown below. Note the Detail node ‘Max Occurs’ is set to unbound as there can be multiple detail line items.

image

6. Create map from the input data schema to the generated schema for the stored procedure. The functiod in the map connecting the Detail node to the SaleItemTableType is just a Looping shape.

image

7. Create a simple orchestration to write the sales data to the database. This orchestration just simply picks up the xml sales data from a pickup folder and inserts the data using a static resquest/response port. The response is simply the identity id returned from the stored procedure.  The response is then written to a drop folder as xml.

image

8. Compile and deploy the application to BizTalk. The request/response port is configured as a WCF SQL.

image

The SOAP Action header of the WCF SQL Transport Properties are configured as below

image

That’s pretty much it. Just drop a test message and check the tables have been populated.

Below is the results after dropping the test message in the pickup folder a few times.

image

 

Enjoy.

Creating a composite response schema for the BizTalk SQL WCF Adapter

I had a scenario where I was requesting several different complex xml documents from a SQL database to a BizTalk application. Returning xml from the stored procedures were used because of the relational structure of the data and to avoid having to use multiple maps to get to the final xml structure.

Instead of having to create new send ports for every query because of the response schemas where different for each request, I decided to use a composite schema that allowed me to use a single send port.

Below are the steps I used to go about developing the solution for BTS2009 and SQL2008. I will use the sample database AdventureWorks and will use a simple xml structure for each entity involved.

1. Create the required stored procedures

Create procedure [BTS].[Sel_Contacts_XML]

as

WITH XMLNAMESPACES ('http://BT.Composite.Schemas.WcfSQL/ContactResponse' as nsql1)

select Title,FirstName,LastName,EmailAddress

from Person.Contact

for XML PATH('nsql1:Contact')

 

Create procedure [BTS].[Sel_Products_XML]

as

WITH XMLNAMESPACES ('http://BT.Composite.Schemas.WcfSQL/ProductResponse' as nsql2)

select ProductNumber,Name,ListPrice

from Production.Product

for XML PATH('nsql2:Product')

 

2. Create the schemas for each stored procedure in the BizTalk project. Ensure that the target namespace matches the namespace defined in the stored procedure. For this example I called them ContactResponse and ProductResponse.

3. Create a new schema that will import the above schemas. This schema will be used as the common composite response schema for each of the WCF SQL requests.

image

Below is the final composite schema with the imported ContactResponse and ProductResponse schemas. Note that the target namespace and the root element of this composite schema will be specified in the WCF adaptor properties latter on.

image

4. Create the orchestration to call the stored procedures. Note there is only one send port but there is a separate operation for each stored procedure.  Each operation was created manually and the name of the operation is important as it is used in the soap action header configuration latter.

For this demonstration the orchestration requested all the contacts first from the database and wrote the response to a file, then a second request was made to the database for the products and the response written to a file also.

image

5. Once the application is deployed to BizTalk, the WCF Custom adaptor must be configured.

When using stored procedures that output XML, the action property must be defined as following. XmlProcedure/<schema_name>/<procedure_name>

The operation name must match the name of the operation inside the orchestration

image

 

Below is the Action operation. Note the two operations that match port operation in the orchestration.

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <Operation Name="SelContacts_OP" Action="XmlProcedure/BTS/Sel_Contacts_XML" />

  <Operation Name="SelProducts_OP" Action="XmlProcedure/BTS/Sel_Products_XML" />

</BtsActionMapping>

Next the binding properties for using stored procedures outputting xml must be set to the target namespace and the root name of the composite schema that was created.

image

 

This is sample output extract of the first request for the Contacts.

image

And this is the output from the second request for the products.

image