January 18, 2021
Hot Topics:

Building a BizTalk Pipeline Content Enricher with SQL Server 2005

  • By Jeffrey Juday
  • Send Email »
  • More Articles »

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 jjuday@crowechizek.com.

Page 2 of 2

This article was originally published on November 5, 2007

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date