Adding “Related Items” Field to Your List in SharePoint 2013 and SharePoint Online

SharePoint 2013 Task forms have a pretty nifty field called Related Items. It allows you to add a reference to another document or site asset to a Task. This is mainly used to display the related item that the task is created for (During a Workflow). Clicking on Add Related Item link and you get a dialog that allows you to

  1. Upload content from your desktop
  2. Select an item from a SharePoint list (yes you can link to a list item as well)

RelatedItems

Read More »

Posted in Lists and Libraries, SharePoint, SharePoint Server | Tagged , , | Leave a comment

Inserting into Multiple Parent-Child SQL Tables in a Single Stored Procedure Call using the BizTalk Server WCF-SQL Adapter

This post covers how to load records into several tables that have parent-child relationships, via a single stored procedure call and the use of SQL user-defined table types. You may have seen how to use table types for BizTalk SQL loads before (such as in this post), but the approach detailed below shows how to load multiple parent and child records within the same stored procedure, while also executing no more than one insert statement for each table being loaded. (Note that user-defined table types is only available in SQL Server 2008 or later).

Having BizTalk make just one SQL stored procedure call for all records has multiple benefits:

  • Reduces the number of BizTalk messages going through the BizTalkMsgBoxDb database, as well as the number of roundtrips between BizTalk and SQL Servers, reducing overhead and improving speed of loads
  • All record loads can be performed in the same transaction
  • No need to de-batch and create separate messages (for each root parent record) within BizTalk

In addition, the stored procedure calls just one insert for each table (avoiding having to loop through records), resulting in significant performance gains.

Read More »

Posted in BizTalk | Tagged , , , , , , , | Leave a comment

Managed Properties (Limitation) in SharePoint Online

With the new SharePoint Online being touted as the SharePoint platform for the future a number of organizations are planning to move to the cloud and leverage SharePoint online for collaboration and enterprise content management. One of the powerful features in SharePoint 2013 (and SharePoint online) is the new search engine. When you start working with SharePoint online you will encounter a number of differences between how on-premises SharePoint and SharePoint online are administered. One of these is how you work with Managed Properties.

Read More »

Posted in SharePoint, SharePoint Foundation, SharePoint Server | Tagged , , , | Leave a comment

Capturing and Debugging a SQL Stored Procedure call from BizTalk

So you design your strongly typed stored procedure to take table types from BizTalk and it’s running great with your test cases.  It works well through the unit testing, but then you start running larger jobs and suddenly SQL is choking on it.

Ever wish you could just run that SQL call directly in SSMS with those exact several thousand rows for the table type parameters, and step through it using the debugger?  Well, you can using SQL Server Profiler (and/or Server Traces).  I used this technique recently to help a client resolve a particularly thorny issue that came up when they tried to process some larger messages.

To walk through the process of doing this, I’ll use a database named BTSTrainDb with a stored procedure (dbo.usp_DemoTableTypeSP) that takes a user-defined Table Type (dbo.DemoTableType) as a parameter and then just selects * from it (echoing it back to the caller).

Read More »

Posted in 0-Uncategorized, BizTalk, Biztalk Tutorial, Business Intelligence, Enterprise .NET, Microsoft, Performance Tuning, SQL Server | Tagged , , , , , , | Leave a comment

BizTalk: Slow Performance while viewing or restarting host instances

Recently, while troubleshooting performance issues at a client, I came across a peculiar issue.  The environment in question was a multi- server environment with multiple BizTalk Servers (2010) connected to a SQL Server cluster.  The issue was extremely slow performance when trying to view, refresh, start or stop the host instances through the Administration Console.  Now usually it takes a few seconds for host instances to start, stop, or restart, however, in this case just refreshing the status of the host instances was taking over a minute.

The problem was that one of the BizTalk Servers in the group was offline for some time, causing the host instances tied to that BizTalk Server, within the same group, to become unresponsive.  This caused slowdown in viewing the host instances for any of the other servers in the BizTalk Group.

There are two ways to resolve this:

1. Turn on any offline BizTalk Servers

2. Remove the host instances for any BizTalk Servers that are no longer needed.

In our case, the second option was our chosen on, as the offline BizTalk server was no longer needed.  Once we removed the host instances from the group, the Administration Console was a lot snappier and responded quicker when refreshing, starting, or stopping host instances.

Posted in BizTalk, Biztalk Tutorial, Performance Tuning | Tagged , , , | Leave a comment

Typed Polling with WCF Adapters

Typed Polling with WCF Adapters

It is very common to have a requirement in middleware design where we have to query SQL tables and retrieve some data when certein conditions are met.

The WCF-based adapters available with the BizTalk Adapter Pack enable the adapter clients to receive data-change messages from the SQL Server database(s). The adapter(s) support receiving “polling-based” messages wherein the adapter executes a specified SQL statement (SELECT statement or stored procedure), retrieves or updates the data, and provides the result to the adapter client at regular intervals of time. The adapters supports three types of polling:

  • Weakly-typed Polling (also called Polling),
  • Strongly-typed Polling (also called TypedPolling)
  • Xml-Polling polling using statements or procedures that include a FOR XML clause

For more information about the different types of polling, see Polling SQL Server by Using the SQL Adapter with BizTalk Server (http://go.microsoft.com/fwlink/?LinkId=221195).

Assumptions and prerequisites

This document is not intended as an MS SQL Primer and assumes that the reader has basic Database administrative skills to create and manage databases, tables and stored procedures. It is also assumed that the reader has basic knowledge of BizTalk, BizTalk administration and basic deployment methodologies around the same.

 

Create a new Database called WCFSQLTest

For the purposes of our discussion we will be creating a test database. Using SQL Server Management studio right click on the Database object and select new:

CreateWCFSQLTest

 

Create a new table in the WCFSQLTest DB called OrderItem:

Using SQL Server Management Studio and from within the newly created Database object:

CreateNewTableOrderItem

 

Launch Visual Studio

Launch visual studio and create a new empty BizTalk project called BTSDemo4

Add Generated Items:

From a newly  created BTSDemo4 solution/project right click on the project and choose “Add Generated Items” and choose

Consume Adapter Service from the Add Generated Items Wizard.

ConsumeAdapter

After clicking ADD then choose SQLBinding from the “select a binding” drop down and then press the configure button:

ConsumeAdapterWizard

Configure URI:

Choose the URI Properties and fill them out according to your DBName (initial catalog), SQL Instance name and server name. on the Inbound ID use a unique identifier related to the interface:

ConfigureURI

 

 

Binding Properties:

PollingDataAvailableStatement

After filling out the the URI PROPERTIES SECTION select the binding properties TAB and cut and paste the following select statement in the “PollingDataAvailableStatement” to determine if any records are available in our table:

select count(*) FROM [WCFSQLTest].[dbo].[OrderItem] where status = 0 where status =0 is NOT INTERFACED OR Processed by BizTalk.

Note: PLEASE BE SURE the Inbound OperationType is set to TypedPolling

BindingProperties

 

Polling Statement

Place the following SQL in the “Polling Statement” section:

select * from [WCFSQLTest].[dbo].[OrderItem] where status = 0;update [WCFSQLTest].[dbo].[OrderItem] set status =1 where status = 0

PollingStatement

Then click OK

 

Connecting to the Database

Click connect to ensure the URI configuration is correct then be sure and select SERVICE INBOUND in the dropdown for the contract type on left side of screen. Then Choose TypedPolling and click add. Also choose Generate unique Schema type. Then click OK

ConnectingToDatabase

 

This should add two files to your solution:

ConsumeAdapterWizard AddTwoFiles

Please build the solution to be sure nothing has “broken”.

 

Deploy the solution from visual studio to your local BTS instance.

Import the WcfReceivePort_SqlAdapterBinding_Custom.bindinginfo.xml binding file into the currently deployed WCF Test solution on your local BizTalk Instance

 

Restarting Host Instances:

Under platform settings from the BizTalk admin console restart the local host instances:

RestartingHostInstance

Turn on BizTalk Application

Turn on the receive location and then review the event log for errors.

We should find one subscription not found error that illustrated the pub/sub nature of BizTalk interfaces.

We will need to place a filter on a newly created send port to “subscribe” to the message type received in by our new receive location that polls the SQL table based on the schema generated by the Wizard in previous step and now deployed into our application via Visual Studio.

BizTalk Schemas Target Namespaces:

If we review the “schemas” section under our application tree in the BizTalk Admin console we will fine our schema there with a root name of “TypedPolling”:

BizTalkSchemasTargetNameSpace

Subscription Filters:

The target name space and the root name are the two key pieces to creating a filter in a send port that will act as the subscribing artifact in the BizTalk interface:

That is to say:

TargetNameSpace#RootName

In the case of this use case:

http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ShipmentNotification#TypedPolling

Creating the Send Port:

From the BizTalk Administrative tool and in the newly deployed application let’s create a new one way send port and with a filter on BizTalk Message type as follows:

CreatingTheSendPort

 

Filters

Setting the Send Port Filter to create the subscriber

Here is the filter required:

Filters

After creating a send port that grabs all records from this new receive location, Start the application. When we put a new row into my database we should get one file emitted to disk.

If we create three records in the database, then we still get a single message/file out of BizTalk.

SingleFileOut

 

Debatching:

The final step in this exercise is optional and only need to be completed if we want to split this up so that these three records show up as three distinct messages/files

With the WCF adapter, you’ll see that we actually have three root nodes as part of the generated schema.  We can confirm this by looking at the Schemas section in the BizTalk Administration Console:

Debatching

So, this means that we should be able to change the existing schema to support debatching, and it all just works.  Let’s try that now:  Go back to the auto-generated schema, click the topmost Schema node, and changed its Envelope property to Yes.

BodyXPath EnvelopePropertyYES

Next, click the TypedPolling node (which acts as the primary root that comes out of the adapter) and set the Body XPath value to the node ABOVE the eventual leaf node:

BodyXPath

Finally, select the leaf node and set its Max Occurrence from Unbounded to 1. Rebuild the project and then redeployed it to the BizTalk Server.  When we add two records to the database, we end up with two records out on disk.

When the record gets debatched automatically by BizTalk in the XML receive pipeline, the resulting TypedPollingResultSet0 message(which matches a message type known by BizTalk) gets put in the MessageBox and routed around.

Good luck and Happy BizTalking!

 

Posted in BizTalk, Biztalk Tutorial, Visual Studio | Leave a comment

Provisioning BizTalk Server 2013 R2 environment on Microsoft Azure IaaS Platform

Setting up a new BizTalk Server environment on the Azure IaaS with multiple Azure Virtual Machines (VM) involves a lot of steps and prerequisites. I have explained them in this blog.

Firstly, sign up for a new Azure subscription or add your Live ID as the co-admin in the Azure subscription if one already exists.

After the co-admin/sys admin privileges, you can choose to administer the Azure subscriptions in these ways:  a) Azure PowerShell cmdlets  b) Azure Management Portal.

I have used the Azure management portal for provisioning all cloud entities as it is more convenient and a time saver. Below is the architecture diagram that explains the BizTalk environment in Azure IaaS.

arch

BizTalk IaaS environment on Azure

Read More »

Posted in Azure, BizTalk, IaaS | Tagged , , , | 1 Comment

The BizTalk Convergence

May 2014 marks the release date of BizTalk Server 2013 R2. The iteration provides an abundance of new features that includes JSON support, adapter updates, accelerator updates, and a new snap-in application.

 

BizTalk Server 2013 R2 incorporates the BizTalk JSON Schema Wizard and JSON decoder/encoder. The JSON Schema Wizard provides support to generate XSD schema from a JSON instance. The JSON decoder/encoder components are used with custom pipelines. More information can be found here.

 

The Service Bus (SB) Messaging adapter now supports Shared Access Signature (SAS) authentication, this feature permits interaction with on-premises edition of Service Bus Queues, Topics, and Relays. Ultimately, this bridges connectivity between Windows Azure and on-premises BizTalk servers. To learn more visit this link.

 

In addition, the Secure File Transport Protocol (SFTP) adapter now enables two-factor authentication and has the option to specify a temporary folder to support uploading/downloading. Configuration examples can be found here.

 

The updated HL7 Accelerator is capable of 64-bit support and processing of “FreeText” messages. The 64-bit adapter and pipelines will only be enabled on 64-bit computers utilizing 64-bit host instances with the 64-bit package solely installed. Be advised that it is backwards compatible with 32-bit mode. The new “FreeText” data type is not parsed nor validated. More information about HL7 Accelerator can be found here. For “FreeText” examples visit this link.

 

The BizTalk Health Monitor (BHM), akin to the standalone application MsgBoxViewer, is now available as a snap-in to the BizTalk Admin Console. Some of the major features it provides are: monitor multiple BizTalk environments, generate and view reports, dashboard view of BizTalk environment, schedule report collection, Email notifications, performance monitor integration and report management. More details can be found here.

 

Please visit this link to learn more about BizTalk 2013 R2.

Posted in BizTalk, Welcome | Tagged , , , , , | Leave a comment

Muenchian Grouping in BizTalk while keeping Mapper functionality

Muenchian Grouping is a powerful technique for to allow grouping by common values among looping/repeating nodes in an XML document.  BizTalk does not have out of the box support for this, but it can be achieved by adding custom XSLT to a map.  Chris Romp wrote a post about this years ago that serves as an excellent example of the idea in BizTalk: http://blogs.msdn.com/b/chrisromp/archive/2008/07/31/muenchian-grouping-and-sorting-in-biztalk-maps.aspx.  The drawback of his method is that you lose all other Mapper functionality by using completely custom XSLT, and custom XSLT is more difficult to maintain than a BizTalk map.

Enter Sandro Periera’s phenomenal tutorial on Muenchian Grouping in BizTalk maps (https://code.msdn.microsoft.com/windowsdesktop/Muenchian-Grouping-and-790347d2).  His solution is particularly powerful because it allows you to maintain the functionality and simplicity of the BizTalk Mapping engine while extending it to allow for the Muenchian Grouping technique as well.  However, there is still a limitation to this approach; the XSLT functoids will still be responsible for any transformation of child nodes that are grouped.  That poses a problem if your grouping logic requires that a parent (or perhaps even a root) node gets grouped on the criteria and many child nodes must be appended to the proper parent.

I recently faced just this situation while working for a client.  The XML data coming in needed to be extensively transformed, and in particular, duplicate child nodes had to be converted to unique parent nodes, with the original parents being appended to the correct new unique node.  Custom XSLT is clearly required here, but a hybrid approach can be used to still allow a regular BizTalk map to transform the resultant data.

Read More »

Posted in BizTalk | Tagged , , , , | Leave a comment

MABS EAI Bridge LoB Lookup (Part 2 of 2)

Last week month (sorry about that!), I wrote a post about using MABS to access a LoB system (in the example, SQL Server) behind several layers of firewalls (here).

We looked at the following tasks

  1. Creating the BizTalk services
  2. Setting up BizTalk Adapter Services in a local (or IaaS) environment to run a stored procedure in SQL Server
  3. Creating a sample table and stored procedure
  4. Creating a ServiceBus namespace with ACS
  5. Create the Relay to the LOB system
  6. Creating an EAI bridge to access the LoB system

This week, we’ll look at these tasks:

  1. Testing and debugging the bridge with a Visual Studio add on
  2. Writing a custom component to call the LoB adapter in a EAI Bridge Stage and parse the response
  3. Having the component send an email notification using an Office 365 server

Read More »

Posted in .NET Framework, Architecture and Development, Azure, BizTalk, Cloud, Enterprise .NET, SaaS, SQL Server, WCF | Tagged , , , , , , , , , | Leave a comment