Microsoft & .NET.NETBuilding a BizTalk Pipeline Content Enricher with SQL Server 2005

Building a BizTalk Pipeline Content Enricher with SQL Server 2005 content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

Pipeline Overview

I leveraged code from my Pipeline Channel Stack applications. For more details on the Pipeline Channel Stack, check out the article “BizTalk Pipeline Dreams Become Reality.”

I changed the Channel assembly from the article above and added the following classes to the assembly:

  • ContentEnricherMessageReader handles reading the XML passed to the Pipeline.
  • StandardFileStreamBodyHandler is a new BodyHandler for the Channel Stack Pipeline classes.
  • StreamFromSQLQueryBuilder works with the StreamWritingHandler class to query the SQL Server database and write the data to a Stream.
  • StreamWritingHandler handles writing the Stream to a temporary file.

The entire Stream creation process is captured in the RetrieveMessage function of the TestPipelineChannel class. Below is the RetrieveMessage function with some declarations and debug code removed.

ContentEnricherMessageReader cr =
   new ContentEnricherMessageReader(_currentMessage);

StreamFromSQLQueryBuilder qry;
StandardFileStreamBodyHandler streamBH;
Stream stream;
string pathToFile = "";
PipelineMessage msg;
string fileDir = "";



qry = new StreamFromSQLQueryBuilder(cr.SQLConnectString,
   cr.SQLQuery, pathToFile);

streamBH = new StandardFileStreamBodyHandler();

stream = qry.MakeStream();

msg = _currentMessage.BizTalkContext.CreateMessage(streamBH);


ContentEnricherMessageReader wraps access to the incoming XML message Stream. Using the .NET XmlDocument class, this class exposes a set of properties for use by other classes hiding the messy details of accessing the underlying XML.

Most of the work is done in the StreamFromSQLQueryBuilder, so take a closer look at this class.


The MakeStream function in StreamFromSQLQueryBuilder creates the Stream consumed later by BizTalk. The body of the function appears below.

SqlDataReader dr;
SqlCommand cmd;
SqlConnection conn = new SqlConnection(_sqlConnectString);


cmd = new SqlCommand(_sqlQuery, conn);

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);


while (dr.Read())



return (_writeHandler.TheStream);

SqlDataReader is the recommended way to return and iterate through a group of SQL records quickly. As stated earlier, StreamWritingHandler wraps access to the underlying FileStream.

With the Stream created, the Pipeline returns the Stream to BizTalk.

Sample Caveats

The included sample code is a prototype I created to test the architectural ideas.

Earlier, I mentioned PGP encryptions requirements. In my actual solution, I had a separate encryption Channel that encrypted the file and returned the encrypted Stream back to BizTalk.


Allocating resources just before you use them often results in better application performance. In the messaging and business process management world, the Content Enricher Design Pattern epitomizes the just-in-time allocation philosophy. Along with SQL Server 2005 and the BizTalk FTP Adapter, I showed you how to implement the Content Enricher in a BizTalk Pipeline.


About the Author

Jeffrey Juday is a software developer with Crowe Chizek in South Bend, Indiana. He has been developing software with Microsoft tools for more than 12 years in a variety of industries. Jeff currently builds solutions using BizTalk 2004, ASP.NET, SharePoint, and SQL Server 2000. You can reach Jeff at

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories