April 2008 - Posts

BizTalk SQL Message Archive component using Forward-Only Streaming
30 April 08 02:00 AM | wmmihaa | 10 comment(s)

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: , ,
Database lookup functoid with caching feature
28 April 08 12:11 PM | wmmihaa | 26 comment(s)

Nor the out-of-the-box Database lookup functiod or the XRef functoid come with any caching capabilities, which might produce a tremendous amount database traffic, and eventually cause transformations to run poorly. If you are repeatedly executing similar queries you might gain on using these functoids instead.

The ExecuteQuery and ExtractData works pretty much like the corresponding functoids that ships with BizTalk. However these functoids caches the result, and will therefor prevent the same query from being executed more than once.




This functoid can be used like the Database lookup functoid that ships with BizTalk. It will execute a query against a given database and cache the result using the System.Web.Caching.Cache with a sliding expiration of one minute. This enables the functoid to reuse the data across mapping boundaries, executed on the same host. In other words the same resultset can be used even from within different mapping transformations.

The result from the executed queries are stored in a HashTable, where the key is the concatenation of the database connection string and the actual SQL query. Its value is the System.Data.DataTable returned from executing the query or stored procedure. This makes it possible to query for more then one row, which you may later extract using the ExtractData functoid. You should use this approach with caution; -however this is where you get the best performance effect.

parameters Type Optional Description
1 String  

The connection string or a name of a connection string in the BTSNtSvc.exe.config file. Samples:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
2 Boolean   Indicates whether to search for cached data or not
3 String   The name of the source that is providing the data (table or stored procedure)
4 String X Column name or stored procedure parameter
5 String X Lookup value

The functoid can take a maximum of 6 pairs of either [column name and value] or [sp parameter, parameter value].




This functiod is used together with the ExecuteQuery functoid, and functions similar to the Value Extractor functiod that ships with BizTalk. The ExtractData functiod gets the value from the result of the query executed by the ExecuteQuery functoid. The optional Column/Value pair can be used when the result from the ExecuteQuery functoid holds more than one row.

Parameters Type Optional Description
1 String   Link from the ExecuteQuery functoid
2 String   The name of the column from which you like to extract its value
3 String X Column name to filter *
4 String X Column value filter *

The functoid can take a maximum of 6 pairs of either [column name and value] or [sp parameter, parameter value].

* When the optional parameters are used the Execute Query functoid is forced to have the whole query result cached.


I share any creds (or criticism) with Johan Hedberg and Niklas Häggström

All downloads are available through CodePlex.

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.