BizTalk SQL Message Archive component using Forward-Only Streaming

by | Apr 29, 2008 | BizTalk Community Blogs via Syndication | 0 comments

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",
    _connection);

-That’s it! Download the code if you’re interested, but I must say I’m not sure archiving to the database is the best approach. I don’t say it isn’t, but I haven’t got around testing it yet.

Download sample here.

HTH

Submit a Comment