Mohammed Atef’s Technical blog

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.

Advertisements

June 7, 2009 - Posted by | Biztalk | , , ,

19 Comments »

  1. Thank you this is excellent

    Comment by test | July 15, 2009 | Reply

  2. Thank you for your comment

    Comment by mohammedatef83 | July 16, 2009 | Reply

  3. Moh,
    Appreciate for your work.
    Do you have any walkthrough for this sample for BTS.

    When i try, am getting the below error. i don’t know where i am doing mistake.

    There was a failure executing the receive pipeline: “Excel.ReceivePipeline_Excel, Excel, Version=1.0.0.0, Culture=neutral, PublicKeyToken=2d9e0f9d6cc115e2” Source: “Excel File decoder” Receive Port: “ReceivePort” URI: “C:\Excel\Excel\In\*.*” Reason: StartIndex cannot be less than zero.
    Parameter name: startIndex

    Thanks,
    Raja
    rajatpk@hotmail.com
    raja.kumaravel@in.ispace.com

    Comment by Raja Kumaravel | August 26, 2009 | Reply

    • Check if the properies connectionstring,datanodename,sqlstatement has been set in the pipeline

      Comment by sandhya | May 5, 2010 | Reply

  4. Thank you for comment,
    I think you need to make sure that Excel File Location is correct
    or please check if the excel file contain sheet or more.
    Regards,
    Mohammed Atef

    Comment by mohammedatef83 | August 26, 2009 | Reply

  5. Just correct me.

    Can i test this component without giving connectionstring etc.,info?

    I applied excel file decoder under Decode stage without give any properties details coz just want to test put excel file in source and want to get xml file in output.

    Comment by Raja Kumaravel | August 26, 2009 | Reply

  6. Sorry,You can’t test this component without connection string and the other properties as these properties are used inside the custom component,but you can review the code and update it like you need.

    Regards,
    Mohammed Atef

    Comment by mohammedatef83 | August 27, 2009 | Reply

  7. Hi,
    I have some problem on receive port when I’m using Your custom pipeline component. There is an error: “There was a failure executing the receive pipeline (…) Reason: Object reference not set to an instance of an object.” I was debugging custom pipeline code and I investigated that line 23. in Execute() method (binaryReader.Close();) causes that issue. I’ve changed that with line fileStream.Close(); and evrything gone OK 🙂
    What was strange – during debug in Visual Studio I didn’t catch this, only on runtime I’ve seen that in BizTalk Administration console.

    Otherwise, Your sample was very helpful for me. Thank You !!! 🙂

    Comment by Boniu | November 30, 2009 | Reply

    • Thanks Boniu for comment and fix

      Comment by mohammedatef83 | December 1, 2009 | Reply

  8. Can you upload a test project to show how this component is actually used?

    Comment by roundel1900 | September 13, 2011 | Reply

    • Hi, there is link to test example

      Comment by mohammedatef83 | October 5, 2011 | Reply

      • Hi, Nice article but I can’t find link to the sample. Can you please providesample values for component properties. Thanks.

        Comment by Ratnakar | December 20, 2013

      • Hi,
        Thanks for your comment regarding source code link it is here https://m.app.box.com/view_shared/1p3iz7dlj2

        Comment by mohammedatef83 | December 20, 2013

      • I can view the source code but not test example with the properties set. Somehow I have managed to set properties of the pipeline component and itis working fine. Thanks.

        Connection String :- Provider=Microsoft.Jet.OLEDB.4.0

        SqlStatement :- select * from [sheet1$]

        In order to run from 64 bit machine (BTS2010 on windows server 2008R2), following additional changes were done by me-

        //this property will change for .xlsx files
        odbcConnectionString += “Extended Properties=’Excel 8.0;HDR=Yes;’;”;

        Also, we require to install 2007 Office System Driver: Data Connectivity Components from http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

        Comment by Ratnakar | December 23, 2013

      • Also, we have to use 32 bit Biztalk host instance and not 64 bit.

        Comment by Ratnakar | December 23, 2013

  9. what should be the SQLStatement?

    Comment by Evan | January 13, 2012 | Reply

  10. please let me know what should be the sqlstatement.

    Comment by Evan | January 13, 2012 | Reply

  11. Hi,
    nice article…we are facing the scenario that have to save the processed EDI File into oracle table.we are saving the BizTalk server error details with processed input file. we are new to BizTalk. Can you please guide me what are all the interfaces like (IcomponentUI,Icomponent). we have to use and how to develop custom pipeline from scratch.

    Comment by archana | September 16, 2013 | Reply

    • Thank you for comment, you can build custom pipeline from scratch as shown in the this post and reviewing the source code.

      Comment by mohammedatef83 | September 21, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: