SQL Pipeline Component

SQL Archive Pipeline Component

Published on : Apr 5, 2022

Category : BizTalk Server



Indeed, there is another message archive pipeline component on my BizTalk Pipeline Components Extensions Utility Pack project, that is available on GitHub!

This time, I decided to update an old component, the SQL Archive Pipeline Component that was created by Johan Hedberg on his project Blogical.Shared.PipelineComponents. Unfortunately, like many other resources, this component was not available anymore, but recently I migrated a solution using this component and I decided to migrate this amazing component to Microsoft BizTalk Server 2020.

For those who aren’t familiar with it, the BizTalk Pipeline Components Extensions Utility Pack project is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in receive and sent pipelines. Those pipeline components provide extensions of BizTalk’s out-of-the-box pipeline capabilities.

SQL Archive Pipeline Component

SQL Archive Pipeline Component

The BizTalk Archive Pipeline Component, which was initially created by Johan Hedberg, is a component that enables you to archive incoming/outgoing messages from any adapters to a SQL Server database.

When doing custom pipeline component development, you need to be aware of the forward-only streaming best practice. In short, this means developing your pipeline components so that they do their logic, either as a custom stream implementation or by reacting to the events available to you, through the Microsoft.BizTalk.Streaming.dll stream classes. Without ever keeping anything, except the small stream buffer in Memory, and without ever seeking the original stream. This is a best practice from the perspective of resource utilization, both memory and processor cycles.

Well, this component applies all of these best practices and that was just another reason why I decided to migrate it. Another reason is that this component uses the UPDATETEXT command to stream data to a SQL Server database. This approach would work well with a forward-only streaming manner, which would consume much less (BizTalk) resources than reading the entire message in the pipeline and storing it in the database as a BLOB. 

The project also includes a SQL script file called Create ArchiveStore Table.sql that you can use to create the supported resources in SQL Server, to be used with this component:

  • Table named ArchiveStore

This is the list of properties that you can set up in the archive pipeline component:

Property Name Description Sample Values
Enabled Define if the archive is enabled or not true/false
ConnectionString ConnectionString to the archive database Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

How to install it

As always, you just need to add these DLLs in the Pipeline Components folder that, in BizTalk Server 2020, is by default:

  • C:\Program Files (x86)\Microsoft BizTalk Server\Pipeline Components

In this particular component, we need to have this  DLL:

  • Blogical.Shared.PipelineComponents.dll

How to use it

To use the pipeline component, I recommend you to create a generic or several generic pipelines that can be reused by all your applications, and add the SQL Archive Pipeline Component in the stage you desire. The component can be used in a stage of the receive and send pipelines.

SQL Archive Pipeline Component

Deploy the pipeline to your environment and configure it accordingly.



You can download the SQL Archive Pipeline Component from GitHub: