Building a BizTalk Pipeline Content Enricher with SQL Server 2005

Allocating resources until you need them often improves your software’s responsiveness and reduces runtime footprint. Allocating just before you use something, though, may not always be the best approach. Balancing allocating early or just-in-time is a decision we all face.

Luckily, we’re not alone. Design Patterns are approaches you can turn to when you need guidance. In the messaging and business process management world, Design Patterns are aptly captured in the book Enterprise Integration Patterns by Gregor Hohpe and Bobby Wolf. The book describes a just-in-time resource allocation pattern called the Content Enricher.

The Content Enricher is, like all other Design Patterns, platform agnostic. Simply understanding a Content Enricher is not sufficient. Somehow, you must superimpose the pattern onto your development tool. In this article, I’m going to share how I approached implementing the Content Enricher in BizTalk 2006.

First, I’m going to introduce you to the requirements that led me to the Content Enricher.

The Requirements

I work for a CPA/Consulting firm where airline travel is an important part of how we deliver our services. Provisioning requirements for a new online Travel management application required a PGP-encrypted, comma-delimited file full of employee data. In addition, an email log returns the provisioning results. Most of the time, provisioning will be successful; therefore, we needed an automated review of the file (humans tend to dislike repetition). I also wanted to give the Travel department the ability to initiate the whole process.

The PGP encryption, the automated log review, and the fact that two other existing integration applications had similar requirements, led me to a BizTalk solution rather than a SQL Server Integration Services (SSIS) implementation. I wanted to build a single solution for all three. Although SSIS would’ve worked, I would’ve needed three separate SSIS packages or some custom .NET code working inside of SSIS. I also liked the control over the FTP Adapter and error routing capabilities BizTalk offered.

Once I knew my requirements and platform, I turned to Enterprise Integration Patterns. In particular, I looked at the Content Enricher.

The Content Enricher Implementation Overview

The Content Enricher pattern works on the premise that the system originating the message does not have all the required data for the business process or integration scenario. Instead, the pattern dictates that you store keys to external data sources in your message. Systems involved in the process use the keys in the message to look up data in the other data sources.

The pattern works well when your process is done inside the firewall where the data sources are available. Just-in-time lookups reduce the size of the message and eliminate the need for every system in the business process to harbor a full set of each other’s data.

The pattern was perfect for my process. Once initiated, the process routes a message and creates the employee data file just before the file is transmitted by the BizTalk FTP adapter. The whole process is depicted below.

My “Employee Data” external data source was a SQL Server 2005 database.

My message schema was simple and adaptable to all three applications. In the message, I pass the following pieces of data.

  • Connect string to the external data source
  • The query or stored procedure to invoke
  • Some message identifying information
  • The flexibility to add additional information as needed for each solution

As you may have guessed, there are a few ways to implement the requirements in BizTalk. I’m going to look at each of the options and explain why I chose a BizTalk Pipeline.

Solution Options

I narrowed the options to an Orchestration, a Pipeline, and a hybrid approach using Orchestrations and Pipelines.

I eliminated an Orchestration mostly because using an Orchestration meant moving the entire data file into and out of the BizTalk Messagebox more than the Pipeline approach. Also, my process was not that complicated. There was no human workflow and little system workflow.

I considered an Orchestration and Pipeline together. I would’ve opted for this if there was something like a human workflow approval process requirement.

I picked the Pipeline approach for the following reasons:

  • Unlike Orchestration, the Pipeline was less taxing on the BizTalk Messagebox.
  • I considered the RAM intensiveness of the process. If needed, I could’ve put the Pipeline on its own machine.
  • I was using a PGP encryption .NET class library. The class library moves data into Streams. Pipelines work well with Streams.

More by Author

Must Read