BizTalk Custom Pipeline to Convert Excel file to Xml file

This blog explains how to convert .xls to Xml file in BizTalk using Custom Pipeline.

Below pipeline component is generic one, which can be applied to any excel files for xml conversion.

Sample Input (Excel File):

SampleInput.png

Sample Output (Xml File):

SampleOutput.png

Please follow below steps.

  1. Take a class library project and keep the code mentioned in Download link below.

Download

2) Provide strong name key, build and deploy to GAC.

3) Take a BizTalk receive pipeline and add the pipeline component (ExcelToXmlFileConversion) to Toolbox

 

3.png

 

4) Drag and Drop the Pipeline component from toolbox to Decode stage of Receive pipeline.

4.png

5) Build BizTalk project and deploy.

6) In Admin Console, take one-way receive port and receive location.

7) In Receive pipeline, browse the pipeline “ExcelToXmlFile”

8) Provide the below information in pipeline properties.

8.png

//ConnectionString: Provide the same connection string as in the screenshot above

//DataNodeName: Root name of the repeating node

//NameSpace: BizTalk message namespace.

//SqlStatement: [Sheet1] is the sheet name in the excel sheet.

//TempDropFolderLocation: This is temporary path you need to provide to perform some internal operations by the pipeline code.

9) Take a send port and provide below properties.

 

9.1.png

9.2.png

 

10) Start the BizTalk application and restart host instances.

11) Drop an Excel file in Input location and you can see the Xml file in Output location

Advertisements

Custom Pipeline

Microsoft provides lots of feature in BizTalk Pipeline with having inbuild pipeline components. E.g. JSON Encoder, Decoder, MIME/SMIME Decoder etc.

But sometimes we need add something in context of message then we need to do customization in pipeline, BizTalk provides the facilities to customise with help of Custom pipeline. C# and VB.Net languages are basically used to write code for this.

Below are few scenarios where you need to apply Custom Pipeline

  • Conversion from another format to XML vice-versa:

As we know BizTalk has the facilities to receive file in XML, Text, csv and json (from BizTalk 2013). But if you have any other format e.g. Excel or PDF, then you need to change the format from Excel to xml because BizTalk always plays in xml format. So here in custom pipeline you can write code in C# or VB.Net form.

  • Add namespace (this facility is existed in BizTalk pipeline, when use ESB pipelines)
  • Promote message context property on custom data
  • Set dynamic send port
  • Message archiving
  • Handling large message

Click on below link for coding of  sample of custom pipeline.

https://vkbiztalk.com/category/pipeline/

Requirement To create a Custom Pipeline:

Dlls:

  • BizTalk.ExplorerOM.dll (C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Developer Tools\Microsoft.BizTalk.ExplorerOM.dll)
  • BizTalk.Pipeline.dll (C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Microsoft.BizTalk.Pipeline.dll)
  • BizTalk.Streaming.dll (C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Microsoft.BizTalk.Streaming.dll)

Pipeline Interfaces:

  • IBaseComponent
  • IComponent
  • IComponentUI
  • IPersistPropertyBag
  • IAssemblerComponent
  • IDisassemblerComponent
  • IProbeMessage

IBaseComponent Interface:

Defines properties that provide basic information about the component. Below are methods used in this component.

  • Description: Describe pipeline component description.
  • Name: Define the component name.
  • Version: Define the component version.

    public interface IBaseComponent

    {

        string Description { get; }

        string Name { get; }

        string Version { get; }

    }

IComponent Interface (COM)

Defines the methods used by all pipeline components except assemblers and disassemblers.

  • Execute: Executes a pipeline component to process the input message and get the resulting message.

   public interface IComponent

    {

        IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg);

    }

IComponentUI Interface (COM)

Defines methods that enable pipeline components to be used within the Pipeline Designer environment.

  • Icon: Provides the icon that is associated with this component.
  • Validate: Verifies that all of the configuration properties are set correctly.

     public interface IComponentUI

    {

        IntPtr Icon { get; }

        IEnumerator Validate(object projectSystem);

    }

IDisassemblerComponent:

A disassembling component is a pipeline component that receives one message on input and produces zero or more messages on output. Below are two methods used in this component.

  • Disassemble: Performs the disassembling of the incoming document message.
  • GetNext: Gets the next message from the message set that resulted from disassembler execution. Returns NULL if there are no more messages.

    public interface IDisassemblerComponent

    {

        void Disassemble(IPipelineContext pContext, IBaseMessage pInMsg);

        IBaseMessage GetNext(IPipelineContext pContext);

    }

IAssemblerComponent

An assembling component is a pipeline component that receives several messages on input and produces one message on output. Assembling components are used to collect individual documents into the message interchange batch.

  • AddDocument: Adds the document message to the list of messages that will be included in the interchange.
  • Assemble: Builds the interchange from the messages that were added by the previous method. Returns a pointer to the assembled message.

     public interface IAssemblerComponent

    {

        void AddDocument(IPipelineContext pContext, IBaseMessage pInMsg);

        IBaseMessage Assemble(IPipelineContext pContext);

    }

IProb Message:

Defines the methods and properties for components that need probing functionality. Any pipeline component (general, assembling, or disassembling) can implement the IProbeMessage interface if it must support message probing functionality. A probing component is used in the pipeline stages that have FirstMatch execution mode. The IProbeMessage interface exposes a single method, Probe, which enables the component to check the beginning part of the message. The return value determines whether this component is run.

    public interface IProbeMessage

    {

        bool Probe(IPipelineContext pContext, IBaseMessage pInMsg);

    }

IPersistPropertiesBag Component:

Defines the methods to prepare for, load, and save the properties of pipeline components during design time.

This is responsible for getting the design time properties. If you need to have some properties be set during design time or during the deployment stage, you must add the loading and saving functionalities for those properties.

    public interface IPersistPropertyBag

    {

        void GetClassID(out Guid classID);

        void InitNew();

        void Load(IPropertyBag propertyBag, int errorLog);

        void Save(IPropertyBag propertyBag, bool clearDirty, bool saveAllProperties);

    }

Custom pipeline Components:

You can create three types of pipeline components:

  • General: This component can be fit at any stage of Receive and Send pipeline,
  • Assembling: This component can only fit at assemble stage of Send Pipeline
  • Disassembling; This component can only fit at Disassemble stage of Receive Pipeline

Developing a General Pipeline Component

A general pipeline component is a .NET or COM component that implements the following interfaces:

 Developing an Assembling Pipeline Component

An assembling component must implement the following interfaces:

  • IBaseComponent
  • IAssemblerComponent
  • IComponentUI
  • IPersistPropertyBag

Developing a Disassembling Pipeline Component

 A disassembling pipeline component receives one message on input and produces zero or more messages on output. Disassembling components are used to split interchanges of messages into individual documents. Disassembler components must implement the following interfaces:

  • IBaseComponent
  • IDisassemblerComponent
  • IComponentUI
  • IPersistPropertyBag

 

For more Details please refer below post:

https://vkbiztalk.com/category/pipeline/