BizTalk SQL Message Archive component using Forward-Only Streaming

Listen with webReader
Published 30 April 08 02:00 AM | wmmihaa

A colleague of mine, Christoph Hanser, came to me with the idea you could use the UPDATETEXT command to stream data to a SQL database. This approach would work well with a forward-only streaming manner, which would consume much less (BizTalk) resources then reading the entire message in the pipeline and store it in the database as a BLOB.

Consider the content of this blog as an experiment, as it is not yet tested. -That said, archiving the message to a database could be preferred in many aspects such as security and maintenance

The advantage of forward-only streaming is that the message will only be read once. It relies on the BizTalk Message Engine to “pull” the message from its destination (through the adapter), and letting the pipeline component hook up the read events. For more information about Forward-only streaming see Johan Hedbergs blog post

This is basically done by encapsulating the incoming stream in a CForwardOnlyEventingReadStream and without reading it in the pipeline Execute method:

CForwardOnlyEventingReadStream eventingReadStream = 
new CForwardOnlyEventingReadStream(pInMsg.BodyPart.GetOriginalDataStream());

Now you register the read events:

eventingStream.BeforeFirstReadEvent += new BeforeFirstReadEventHandler(BeforeFirstReadEvent);

eventingStream.ReadEvent += new ReadEventHandler(ReadEvent);

eventingStream.AfterLastReadEvent += new AfterLastReadEventHandler(AfterLastReadEvent);

When the message finally hits the Message Engine, BizTalk will read the encapsulated stream, and trigger the events you’re interested in, such as the ReadEvent. This is where the fun starts. First I had to create the row in the table and get a handle to the column where I would store the message data:

SqlCommand command = new SqlCommand(
                "INSERT INTO [dbo].[ArchiveStore] " +
                "([InterchangeID] " +
                ",[Source] " +
                ",[Message]) " +
                "VALUES(@InterchangeID,@Source,@Message)" +
                "SELECT @Pointer = TEXTPTR(Message) " +
     "FROM [dbo].[ArchiveStore] " +
                "WHERE InterchangeID = @InterchangeID AND " +
                "[Source] = @Source", _connection);

command.Parameters.Add("@InterchangeID", SqlDbType.UniqueIdentifier).Value = _interchangeID;
command.Parameters.Add("@Source", SqlDbType.VarChar, 255).Value = _source;
command.Parameters.Add("@Message", SqlDbType.Image, args.bytesRead).Value = args.buffer;

SqlParameter ptrParm = command.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
ptrParm.Direction = ParameterDirection.Output;

Now that got the handle I can update the [Message] column using the UPDATETEXT command in the ReadEvent:

_appendMessage = new SqlCommand(
    "UPDATETEXT ArchiveStore.Message @Pointer @Offset 0 @Bytes",

-That’s it! Download the code if you’re interested.

Download sample here(710KB).


Filed under: , ,


# What I Learned Yesterday 12 « Ben Runchey’s Integration Dev Blog said on May 27, 2008 10:14 PM:

Pingback from  What I Learned Yesterday 12 « Ben Runchey’s Integration Dev Blog

# alidgicasar said on October 7, 2008 05:49 AM:

thats for sure, bro

# alidgicasar said on October 9, 2008 07:31 AM:

nice work, guy

# Gordon said on December 24, 2008 02:17 AM:

Hi. Good news.

# Scott said on January 30, 2009 06:25 PM:

Excellent Post.

I have been looking for a good way to handle this.

Im porting it over to my project and am getting an object reference not set on my connectionstring property in the designer.

Any ideas?



# Imminewek said on March 5, 2009 06:53 AM:

Excellent site and I am really pleased to see you have what I am actually looking for here and this this post is exactly what I am interested in. It's taken me literally 1 hours and 45 minutes of searching the web to find you (just kidding!) so I shall be pleased to become a regular visitor :)

# Bruno said on June 2, 2009 08:51 PM:

Excellent Post.

But I have a doubt.....How can I insert the message body in BAM database to see in BAM Portal?


# wmmihaa said on June 3, 2009 10:37 PM:

The thought never crossed my mind, and I'm not sure it's a good idea. We've used Reporting Services in favor of the BAM Portal. This, of course, gives us more freedom.


# skr said on April 20, 2011 02:30 PM:


Thanks for sharing excellent post.

I am using EDI DIsassembler component after the custome pipeline component and facing error "Object reference not set to instanc eof an object".

Any help??



# Kishore said on September 28, 2011 04:32 PM:

well I have developed these pipelines the receive pipeline works fine, but how do I use the send pipeline? when I try to configure the send port to use this pipeline I have to set the Transport type as well

I'm unable to figure out which transport type I have select on the send port.

Could you please advicse me on this.

This Blog


    MVP - Microsoft Most Valuable Professional BizTalk User Group Sweden BizTalk blogdoc

    Follow me on Twitter Meet me at TechEd


    Locations of visitors to this page


    The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.