Announcing the T-Connect EDI Viewer!

EDI files offer many advantages in business and health care messaging, and BizTalk offers a premier suite for handling EDI interchanges and agreements.  At the same time, EDI files are notoriously difficult to read, even for EDI experts and highly skilled Business Analysts. The T-Connect EDI Viewer is designed to address this gap.  It supports converting the HIPAA EDI 837 transaction sets (for Professional, Institutional, and Dental) as well as the HIPAA EDI 834 Enrollment transaction set to a human-readable and printable PDF format.

The T-Connect EDI Viewer offers a library and a pipeline component that can be used to convert EDI messages sent to BizTalk into a PDF version of their Paper Form equivalents.  This conversion could be done as part of an existing integration, or using archived/historical data on a case by case basis.  The PipelineComponent offers a very simple way of adding this functionality to an existing BizTalk application with little to no development effort.  The library offers a simple but powerful API for more fine grained control over the process for BizTalk application developers.

A fully functional 30-day trial is available here.

Keep reading to see a sample conversion!

Read More »

Posted in 0-Uncategorized | Tagged , , , | 2 Comments

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.

Read More »

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