BizTalk Custom Excel Pipeline Component

Introduction
Did you tried to process excel file using BizTalk before? i was trying to convert Excel file to XML file using BizTalk, really we have a lot of ways around to do this but i think the best way is to do this using custom Pipeline component.
Create Custom Excel Pipeline
Here i will explain to you how to create custom Pipeline component from A to Z.
your first task is to add reference Microsoft.BizTalk.Pipeline.dll after that you must consider the implementation of the custom pipeline component. There are three logical areas to a custom pipeline component to consider:
• Attributes and class declaration
• Design-time properties
• Implementation of the four pipeline interfaces: IBaseComponent, IComponentUI,
IPersistPropertyBag, and IComponent

I will describe each one now.
Attributes and Class Declaration
Here is the header section from the sample code in Listing 1-1

[ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
[ComponentCategory(CategoryTypes.CATID_Decoder)]
[System.Runtime.InteropServices.Guid("71A3FBC6-F5D6-4fd6-A17D-1664A58C7E68")]
public class DecodeExcelC :
    IBaseComponent,
    Microsoft.BizTalk.Component.Interop.IComponent,
    Microsoft.BizTalk.Component.Interop.IPersistPropertyBag,
    IComponentUI

in the previous code we determine the component category is pipelinecomponent and decode component then we will inherit the pipeline interfaces, i will describe each interface later in this post.
Design-Time Properties
Custom component design-time properties are exposed via public declarations and appropriate get/set methods. The following is the section of Listing 1-2 that demonstrates how two design-time properties are exposed.

private string connectionString = null;
[System.ComponentModel.Description("Excel Connection String")]
public string ConnectionString
{
    get { return connectionString; }
    set { connectionString = value; }
}
private string filter = null;
[System.ComponentModel.Description("Filter for Select Statement")]
public string Filter
{
    get { return filter; }
    set { filter = value; }
}
private string sqlStatement = null;
[System.ComponentModel.Description("Select Statement to Read ODBC Files.")]
public string SqlStatement
{
    get { return sqlStatement; }
    set { sqlStatement = value; }
}
private string tempDropFolderLocation = null;
[System.ComponentModel.Description("Temp Folder for Dropping ODBC Files.")]
public string TempDropFolderLocation
{
    get { return tempDropFolderLocation; }
    set { tempDropFolderLocation = value; }
}
private bool deleteTempMessages;
[System.ComponentModel.Description("Delete Temp Messages after processing")]
public bool DeleteTempMessages
{
    get { return deleteTempMessages; }
    set { deleteTempMessages = value; }
}
private string fnamespace = null;
[System.ComponentModel.Description("NameSpace for resultant XML Message, for example:")]
public string NameSpace
{
    get { return fnamespace; }
    set { fnamespace = value; }
}
private string rootNode = null;
[System.ComponentModel.Description("Root Node Name for resultant XML Message")]
public string RootNodeName
{
    get { return rootNode; }
    set { rootNode = value; }
}
private string dataNode = null;
[System.ComponentModel.Description("Data Node Name for resultant XML Message rows")]
public string DataNodeName
{
    get { return dataNode; }
    set { dataNode = value; }
}

Implementing the Pipeline Interfaces
i will explain how to use each interface in my custom pipeline as follows
IBaseComponent
IBaseComponent contains three read-only properties that return the description, version, and name of the component to the design-time environment and other tools interested in basic component information. Implementing the IBaseComponent is straightforward and requires implementing only the three read-only properties. Here is the section of the code in Listing 1-3.

[Browsable(false)]
string IBaseComponent.Description
{
    get { return "BizTalk Receive Pipeline Component for Decoding Excel Files"; }
}
[Browsable(false)]
string IBaseComponent.Name
{
    get { return "Excel File decoder"; }
}
[Browsable(false)]
string IBaseComponent.Version
{
    get { return "1.0"; }
}

IComponentUI
IComponentUI serves to present the component icon in the design-time tool set. The two methods implemented in the IComponentUI are Icon and Validate. The Icon method provides a pointer to the graphic icon displayed in the design-time user interface. If no icon is specified,Visual Studio will display the default icon in the BizTalk Pipeline Components section of the Toolbox. The Validate method allows processing of any design-time properties. The following portion of Listing 1-4 shows both the Validate and Icon methods.

IntPtr IComponentUI.Icon
       {
           get
           {
               ResourceManager rm = new ResourceManager("ODBCPipelineComponent.Resource", Assembly.GetExecutingAssembly());
               Bitmap bm = (Bitmap)rm.GetObject("odbc");
               return bm.GetHicon();
           }
       }
       System.Collections.IEnumerator IComponentUI.Validate(object projectSystem)
       {
           return null;
       }

IPersistPropertyBag
The purpose of the IPersistPropertyBag interface is to provide access to your object to unmanaged code. If you are familiar with .NET, then you may have used property bags in other projects. IPersistPropertyBag also allows access to design-time configuration values. There are four public methods that exist in the IPersistPropertyBag interface: GetClassID, initNew, Load, and Save.

The GetClassID function must return a unique ID that represents the component. The initNew function can be used to establish structures used by the other IPersistPropertyBag methods. The final functions facilitate the loading and saving of property values.The following portion of the code from Listing 1-5 demonstrates the implementation of the four IPersistPropertyBag

void IPersistPropertyBag.GetClassID(out Guid classID)
       {
           classID = new Guid("71A3FBC6-F5D6-4fd6-A17D-1664A58C7E68");

       }
       void IPersistPropertyBag.InitNew()
       {

       }
       void IPersistPropertyBag.Load(IPropertyBag propertyBag, int errorLog)
       {

           object valConnectionString = null,
                   valtempDropFolderLocation = null,
                   valSqlStatement = null,
                   valDeleteTempMessages = null,
                   valRootNodeName = null,
                   valNameSpace = null,
                   valDataNodeName = null,
                   valFilter = null;                     

           try
           {
               propertyBag.Read("ConnectionString", out valConnectionString, 0);
               propertyBag.Read("TempDropFolderLocation", out valtempDropFolderLocation, 0);
               propertyBag.Read("SqlStatement", out valSqlStatement, 0);
               propertyBag.Read("DeleteTempMessages", out valDeleteTempMessages, 0);
               propertyBag.Read("RootNodeName", out valRootNodeName, 0);
               propertyBag.Read("NameSpace", out valNameSpace, 0);
               propertyBag.Read("DataNodeName", out valDataNodeName, 0);
               propertyBag.Read("Filter", out valFilter, 0);

           }
           catch (ArgumentException argEx)
           {
              // throw argEx;
           }
           catch (Exception ex)
           {
               throw new ApplicationException("Error reading propertybag: " + ex.Message);
           }

           if (valFilter != null)
               Filter = (string)valFilter;
           else
               Filter = "";

           if (valConnectionString != null)
               ConnectionString = (string)valConnectionString;
           else
               ConnectionString = "";

           if (valtempDropFolderLocation != null)
               TempDropFolderLocation = (string)valtempDropFolderLocation;
           else
               TempDropFolderLocation = "";

           if (valSqlStatement != null)
               SqlStatement = (string)valSqlStatement;
           else
               SqlStatement = "";

           if (valDeleteTempMessages != null)
               DeleteTempMessages = (bool)valDeleteTempMessages;
           else
               DeleteTempMessages = true;

           if (valRootNodeName != null)
               RootNodeName = (string)valRootNodeName;
           else
               RootNodeName = "";

           if (valNameSpace != null)
               NameSpace = (string)valNameSpace;
           else
               NameSpace = "";

           if (valDataNodeName != null)
               DataNodeName = (string)valDataNodeName;
           else
               DataNodeName = "";

       }
       void IPersistPropertyBag.Save(IPropertyBag propertyBag, bool clearDirty, bool saveAllProperties)
       {

           object valConnectionString = (object)ConnectionString;
           propertyBag.Write("ConnectionString", ref valConnectionString);

           object valtempDropFolderLocation = (object)TempDropFolderLocation;
           propertyBag.Write("TempDropFolderLocation", ref valtempDropFolderLocation);

           object valSqlStatement = (object)SqlStatement;
           propertyBag.Write("SqlStatement", ref valSqlStatement);

           object valDeleteTempMessages = (object)DeleteTempMessages;
           propertyBag.Write("DeleteTempMessages", ref valDeleteTempMessages);   

           object valRootNodeName = (object)RootNodeName;
           propertyBag.Write("RootNodeName", ref valRootNodeName);

           object valNameSpace = (object)NameSpace;
           propertyBag.Write("NameSpace", ref valNameSpace);

           object valDataNodeName = (object)DataNodeName;
           propertyBag.Write("DataNodeName", ref valDataNodeName);

           object valFilter = (object)Filter;
           propertyBag.Write("Filter", ref valFilter);

       }

IComponent
IComponent is the most important interface in the component, as it contains the processing logic for messages. This interface contains a single method, Execute, which takes two parameters.BizTalk calls the Execute method to process the message, and then passes the message and the context of the message as the two parameters. The following outlines the Execute method declaration and the two required parameters in Listing 1-6

   1: IBaseMessage Microsoft.BizTalk.Component.Interop.IComponent.Execute(IPipelineContext pContext, IBaseMessage pInMsg)

   2:         {

   3:             System.Diagnostics.Debug.WriteLine("At top of Execute method for DBASE pipeline");

   4:             IBaseMessagePart bodyPart = pInMsg.BodyPart;

   5:             if (bodyPart != null)

   6:             {

   7:                 try

   8:                 {

   9:                     // First write the ODBC file to disk so can query it.

  10:                     BinaryReader binaryReader = new BinaryReader(bodyPart.Data);

  11:                     string folderName = this.TempDropFolderLocation;

  12:                     if (folderName.Substring(folderName.Length - 1, 1) != "\\")

  13:                         folderName += "\\";

  14:                     string extension =".xls";

  15:                     string filename = System.IO.Path.GetRandomFileName();

  16:                     filename = filename.Remove(8);

  17:                     filename += extension;

  18:                     string folderNameAndFileName = folderName + filename;

  19:                     FileStream fileStream = new FileStream(folderNameAndFileName, FileMode.CreateNew);

  20:                     BinaryWriter binaryWriter = new BinaryWriter(fileStream);

  21:                     binaryWriter.Write(binaryReader.ReadBytes(Convert.ToInt32(binaryReader.BaseStream.Length)));

  22:                     binaryWriter.Close();

  23:                     binaryReader.Close();

  24:                     // Create the Connection String for the ODBC File

  25:                     string dataSource;

  26:                     dataSource = "Data Source=" + folderNameAndFileName + ";";                    

  27:                     string odbcConnectionString = this.connectionString;

  28:                     if (odbcConnectionString.Substring(odbcConnectionString.Length - 1, 1) != ";")

  29:                         odbcConnectionString += ";";

  30:                     odbcConnectionString += dataSource;

  31:                     OleDbConnection oConn = new OleDbConnection();

  32:                     oConn.ConnectionString = odbcConnectionString;

  33:                     // Create the Select Statement for the ODBC File

  34:                     OleDbDataAdapter oCmd;

  35:                     // Get the filter if there is one

  36:                     string whereClause = "";

  37:                     if (Filter.Trim() != "")

  38:                         whereClause = " Where " + Filter.Trim();

  39:                     oCmd = new OleDbDataAdapter(this.SqlStatement.Trim() + whereClause, oConn);

  40:                     oConn.Open();

  41:                     // Perform the Select statement from above into a dataset, into a DataSet.

  42:                     DataSet odbcDataSet = new DataSet();

  43:                     oCmd.Fill(odbcDataSet, this.DataNodeName);

  44:                     oConn.Close();

  45:                     // Delete the message

  46:                     if (this.DeleteTempMessages)

  47:                         System.IO.File.Delete(folderNameAndFileName);

  48:                     // Write the XML From this DataSet into a String Builder

  49:                     System.Text.StringBuilder stringBuilder = new StringBuilder();

  50:                     System.IO.StringWriter stringWriter = new System.IO.StringWriter(stringBuilder);

  51:                     odbcDataSet.Tables[0].WriteXml(stringWriter);

  52:                     System.Xml.XmlDocument fromDataSetXMLDom = new System.Xml.XmlDocument();

  53:                     fromDataSetXMLDom.LoadXml(stringBuilder.ToString());

  54:                     // Create the Final XML Document. Root Node Name and Target Namespace

  55:                     // come from properties set on the pipeline

  56:                     System.Xml.XmlDocument finalMsgXmlDom = new System.Xml.XmlDocument();

  57:                     System.Xml.XmlElement xmlElement;

  58:                     xmlElement = finalMsgXmlDom.CreateElement("ns0", this.RootNodeName, this.NameSpace);

  59:                     finalMsgXmlDom.AppendChild(xmlElement);

  60:                     // Add the XML to the finalMsgXmlDom from the DataSet XML, 

  61:                     // After this the XML Message will be complete

  62:                     finalMsgXmlDom.FirstChild.InnerXml = fromDataSetXMLDom.FirstChild.InnerXml;

  63:                     Stream strm = new MemoryStream();

  64:                     // Save final XML Document to Stream

  65:                     finalMsgXmlDom.Save(strm);

  66:                     strm.Position = 0;

  67:                     bodyPart.Data = strm;

  68:                     pContext.ResourceTracker.AddResource(strm);

  69:                 }

  70:                 catch (System.Exception ex)

  71:                 {

  72:                     throw ex;

  73:                 }

  74:             }

  75:             return pInMsg;

  76:         }

I will explain this method now because this method contain all logic for my pipeline component. in line 3 i am getting the message body from the messagebase object by saving bodypart in Ibasemessagepart object.

from Line 10 to 23 i am saving the file content in memory to use it query it easily.

from Line 25 to 44 i am generating connection string to the excel file then read the data from the excel file into data set using OLEDB provider.

from Line 46 to 75 i am generate the new XML.

Now we have finished the custom pipeline component.so you can download the full pipeline component project by click Here .
I hope this help.