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 to Add DocType in Message

Sometimes we require to add some element, tag, DocType in incoming or outgoing message from BizTalk. We can achieve this by using custom pipeline. But we should use this pipeline component in Decode or encode stage of Pipeline.

In this sample custom pipeline, we convert the message in string. So you can take this code and change according your business requirement.

Below link is defined for Custom Pipeline setup details

https://vkbiztalk.com/2017/08/04/custom-pipeline/

Here in this sample code I have added DocType on header of message when sending the message to send port. DocType will defined at design time when consume this pipeline component on encode stage on send pipeline.

using System;

using System.IO;

using System.Text;

using System.Drawing;

using System.Resources;

using System.Reflection;

using System.Diagnostics;

using System.Collections;

using System.ComponentModel;

using Microsoft.BizTalk.Message.Interop;

using Microsoft.BizTalk.Component.Interop;

using Microsoft.BizTalk.Component;

using Microsoft.BizTalk.Messaging;

using System.IO;

using System.Xml;

 

namespace BizTalk.Custom.PipelineComponents

{

[ComponentCategory(CategoryTypes.CATID_PipelineComponent)]

[ComponentCategory(CategoryTypes.CATID_Any)]

[System.Runtime.InteropServices.Guid(“CB814714-BC81-4BDA-B361-69CA270997E8”)]

public class AddDocFileName : IBaseComponent, IComponentUI, Microsoft.BizTalk.Component.Interop.IComponent, IPersistPropertyBag

{

#region Configuration Properties

private string _DocTypeName;

public string DocTypeName

{

get

{

return _DocTypeName;

}

set

{

_DocTypeName = value;

}

}

#endregion

public string Description

{

get

{

return “Pipeline component To Add DocType in Outgoing XML”;

}

}

public string Name

{

get

{

return “AddDocNameinXML”;

}

}

public string Version

{

get

{

return “1.0.0.0”;

}

}

public IntPtr Icon

{

get

{

return new System.IntPtr();

}

}

public System.Collections.IEnumerator Validate(object projectSystem)

{

return null;

}

public void GetClassID(out Guid classID)

{

classID = new Guid(“CB814714-BC81-4BDA-B361-69CA270997E8”);

}

public void InitNew()

{

}

public virtual void Load(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, int errlog)

{

object val = null;

val = this.ReadPropertyBag(pb, “DocTypeName”);

if ((val != null))

{

this._DocTypeName = ((string)(val));

}

}

public virtual void Save(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, bool fClearDirty, bool fSaveAllProperties)

{

this.WritePropertyBag(pb, “DocTypeName”, this.DocTypeName);

}

#region utility functionality

/// <summary>

/// Reads property value from property bag

/// </summary>

/// <param name=”pb”>Property bag</param>

/// <param name=”propName”>Name of property</param>

/// <returns>Value of the property</returns>

private object ReadPropertyBag(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, string propName)

{

object val = null;

try

{

pb.Read(propName, out val, 0);

}

catch (System.ArgumentException)

{

return val;

}

catch (System.Exception e)

{

throw new System.ApplicationException(e.Message);

}

return val;

}

/// <summary>

/// Writes property values into a property bag.

/// </summary>

/// <param name=”pb”>Property bag.</param>

/// <param name=”propName”>Name of property.</param>

/// <param name=”val”>Value of property.</param>

private void WritePropertyBag(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, string propName, object val)

{

try

{

pb.Write(propName, ref val);

}

catch (System.Exception e)

{

throw new System.ApplicationException(e.Message);

}

}

#endregion

public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)

{

IBaseMessagePart bodyPart = pInMsg.BodyPart;

XmlDocument xDoc = new XmlDocument();

if (bodyPart != null)

{

Stream originalStream = bodyPart.GetOriginalDataStream();

string originalDataString;

try

{

//fetch original message

Stream originalMessageStream = bodyPart.GetOriginalDataStream();

byte[] bufferOriginalMessage = new byte[originalMessageStream.Length];

originalMessageStream.Read(bufferOriginalMessage, 0, Convert.ToInt32(originalMessageStream.Length));

originalDataString = System.Text.ASCIIEncoding.ASCII.GetString(bufferOriginalMessage);

}

catch (Exception ex)

{

ex.Source = “AddDocTypePipelineComponent”;

throw ex;

}

XmlDocument originalMessageXml = new XmlDocument();

if (originalStream != null)

{

originalDataString = originalDataString.Remove(0, 41);

originalDataString = @”<?xml version=’1.0′ encoding=’UTF-8′?>” + DocTypeName + originalDataString;

byte[] outBytes = System.Text.Encoding.ASCII.GetBytes(originalDataString);

MemoryStream memStream = new MemoryStream();

memStream.Write(outBytes, 0, outBytes.Length);

memStream.Position = 0;

bodyPart.Data = memStream;

pContext.ResourceTracker.AddResource(memStream);

 

}

}

return pInMsg;

}

}

}

 

Sample Input File:

<?xml version=”1.0″ encoding=”UTF-8″?>

<Test xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema“><Name> </Name></Test>

Sample Output File:

<?xml version=”1.0″ encoding=”UTF-8″?>

<!DOCTYPE Test SYSTEM “http://localhost/Test/DTD/TestDocType.dtd”&gt;

<Test xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema“><Name> </Name></Test>

 

 

 

BAM in Custom Pipeline

In BizTalk, BAM can be applied on pipeline level and in orchestration. Here we are going to implement BAM in BizTalk through BAM API programming.

For more details of BAM APIs, you can refer to

https://vkbiztalk.wordpress.com/2017/07/17/bam-api/

Normally for BAM Programming below three functions are required under EventStream class.

  • BeginActivity
  • UpdateActivity
  • EndActivity

But if you want some more advance feature in BAM tracking then you can refer below reference:

https://msdn.microsoft.com/en-us/library/microsoft.biztalk.bam.eventobservation.eventstream.aspx

Click on below link for example of BAM activity in custom pipeline

https://vkbiztalk.com/2018/02/25/bam-activity-in-custom-pipeline/

Normally, we write the code to produce BAM events in the Execute method of the pipeline component.

The pipeline context has a GetEventStream method that returns a MessagingEventStream.

Requirements:

Dlls:

  • BizTalk.Bam.EventObservation.dll
  • BizTalk.ExplorerOM
  • BizTalk.Pipeline
  • BizTalk.Streaming

Namespaces:

  • usingBizTalk.Bam.EventObservation;
  • usingBizTalk.Message.Interop;
  • usingBizTalk.Component.Interop;
  • usingBizTalk.Streaming;
  • usingBizTalk.ExplorerOM;

Example:

public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext context, Microsoft.BizTalk.Message.Interop.IBaseMessage inMsg)

{

      EventStream BAMes;

BAMes = context.GetEventStream();

string ActivityID; // it can be a GUID

String operation; // message context properties or any custom value to track in BAM

BAMes.BeginActivity(ActivityName, ActivityID);

BAMes.UpdateActivity(ActivityName, ActivityID, “Data”, operation);

BAMes.EndActivity(ActivityName, ActivityID);

BAMes.Flush();

}

Here ActivityName: BAM Definition File Name

ActivityID: It can be a GUID or any unique id

UpdateActivity function:

public virtual void UpdateActivity( string activityName, string activityInstance,                   params object[] data )

In Update activity, after first two parameters, data items are next parameters which are defined in key value pairs.

These data items can be defined in single UpdateActivity function as well as in separate activity function. E.g. if a BAM table has following data items.

Data1, data2, starttime, endtime, errordetails, status, lastmodified.

Here we can write update function as per requirements, it’s not mandatory to define all data items in one time.

BAMes.UpdateActivity(“BAMSample”, ActivityID, “starttime”, DateTime.Now);

BAMes.UpdateActivity(“BAMSample”, ActivityID, “Status”, “OperationStart”);

You can track info on BAM Portal or BAM definition table in BAM Primary Import Database. You can check data in Bam_<BAM Definition File>_Completed table.

e.g. bam_BAMSample_Completed

BAMTable.jpg

When you defining BAM Definition file then BAM creates five table in BAMPrimaryImport table. you can refer to following reference to create BAM Definition file.

https://vkbiztalk.wordpress.com/2017/06/14/implementation-of-bam-activity-definition-file/

How does EventStream work in pipeline?

MessagingEventStream (MES) is used inside a BizTalk pipeline component to write Bam as part of the messaging transactions ensuring that your BAM event persistence remains in sync with the BizTalk pipeline transactions.

Messaging Event Streams are asynchronous and store tracking data first in the BizTalk MessageBox database. Periodically the data is processed and persisted to the BAM Primary Import database by the Tracking Data Decode Service (TDDS).