locked
Creating a sample custom pipeline for converting an excel to XML-External table is not in the expected format(Error) RRS feed

  • Question

  • Hi Team,

    Creating a sample custom pipeline for converting an excel to XML. While sending a file into the receive port getting the following error log.

    Log Name:      Application
    Source:        BizTalk Server
    Date:          06/22/2018 16:57:35
    Event ID:      5719
    Task Category: BizTalk Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      DESKTOP-37B2GBB
    Description:
    There was a failure executing the receive pipeline: "Sample_pipeline.ReceivePipeline1, Sample pipeline, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9175e8628e589acb" Source: "ExcelToXmlFileConversion" Receive Port: "PipelineReceivePort" URI: "D:\BizTalk\Sample pipeline\in\*.*" Reason: External table is not in the expected format.
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="BizTalk Server" />
        <EventID Qualifiers="49344">5719</EventID>
        <Level>2</Level>
        <Task>1</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2018-06-22T11:27:35.221012200Z" />
        <EventRecordID>56234</EventRecordID>
        <Channel>Application</Channel>
        <Computer>DESKTOP-37B2GBB</Computer>
        <Security />
      </System>
      <EventData>
        <Data>Sample_pipeline.ReceivePipeline1, Sample pipeline, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9175e8628e589acb</Data>
        <Data>ExcelToXmlFileConversion</Data>
        <Data>PipelineReceivePort</Data>
        <Data>D:\BizTalk\Sample pipeline\in\*.*</Data>
        <Data>External table is not in the expected format.</Data>
      </EventData>
    </Event>

    Thanks in advance.

    Friday, June 22, 2018 11:33 AM

Answers

  • Please share your pipeline code to review.

    This seems to be a general error with respect to Excel.

    There is a component/code you can refer in the below post(s)

    https://vkbiztalk.com/2018/04/27/biztalk-custom-pipeline-to-convert-excel-file-to-xml-file/

    https://stackoverflow.com/questions/28264240/convert-excel-xlsx-to-xml-with-c-sharp-and-biztalk

     

    • Marked as answer by Kiran Raj N Tuesday, June 26, 2018 8:49 AM
    • Unmarked as answer by Kiran Raj N Wednesday, June 27, 2018 11:06 AM
    • Marked as answer by Kiran Raj N Wednesday, June 27, 2018 11:07 AM
    Friday, June 22, 2018 7:05 PM
  • Just restart the visual Studio and try once again, if it doesnt work, can you do a fresh deployment and then try debugging?

    Mandar Dharmadhikari

    • Marked as answer by Kiran Raj N Tuesday, June 26, 2018 8:49 AM
    Monday, June 25, 2018 10:49 AM
    Moderator

All replies

  • Hi,

    I am getting following as a error information.

    There was a failure executing the receive pipeline: "BizTalkKCAFIN.ExcelReceivePipeline, BizTalkKCAFIN, Version=1.0.0.0, Culture=neutral, PublicKeyToken=d85ea826ef104240" Source: "ExcelToXmlFileConversion" Receive Port: "InputReceivePort" URI: "D:\BizTalk\BizTalkKCAFIN\In\*.xlsx" Reason: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data. 

    Can anybody give a sample for a custom pipeline to convert an excel file to convert to XML?

    Thanks in advance. 


    Thursday, June 21, 2018 7:07 AM
  • There is a lot of caveats using excel automation as a service
    This might help you in your quest (you need special DCOM configuration)
    https://bharathkumaran.wordpress.com/2011/10/25/running-excel-from-a-windows-service/

    There's a few of licensed products, search for [biztalk adapter excel]

    /Peter

    Thursday, June 21, 2018 7:45 AM
  • Can you not use any other format than the excel file? Processing excel in BizTalk is a rather messy affair, so evaluate if you switch to any other file type than excel, if you have the freedom my suggestion would be to give up the excel approach.

    If you cant you can take a look at following for inspiration.

    Also evaluate how you are saving the file? Are you using FileStream class to save the file to stream then you might want to use the FileShare option to lock other users from accessing the files.

    Refer: FileStream Constructor (String, FileMode, FileAccess, FileShare, Int32) use the value FileShare.None to disable other processes to access the file.


    Excel Reading Custom Pipeline for BizTalk Server



    Mandar Dharmadhikari

    Friday, June 22, 2018 3:25 AM
    Moderator
  • Log Name:      Application
    Source:        BizTalk Server
    Date:          06/22/2018 13:06:03
    Event ID:      5753
    Task Category: BizTalk Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Description:
    A message received by adapter "FILE" on receive location "InputReceiveLocation" with URI "D:\BizTalk\BizTalkKCAFIN\In\*.*" is suspended. 
     Error details: There was a failure executing the receive pipeline: "BizTalkKCAFIN.ExcelReceivePipeline, BizTalkKCAFIN, Version=1.0.0.0, Culture=neutral, PublicKeyToken=d85ea826ef104240" Source: "ExcelToXmlFileConversion" Receive Port: "InputReceivePort" URI: "D:\BizTalk\BizTalkKCAFIN\In\*.*" Reason: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.  
     MessageId:  {A106B734-DC1B-4AA6-BF6D-ED4C9679B1AC}
     InstanceID: {36A9302D-240D-4953-AF86-1F8861F49DDF}
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="BizTalk Server" />
        <EventID Qualifiers="49344">5753</EventID>
        <Level>2</Level>
        <Task>1</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2018-06-22T07:36:03.182343100Z" />
        <EventRecordID>56142</EventRecordID>
        <Channel>Application</Channel>
        <Computer>DESKTOP-37B2GBB</Computer>
        <Security />
      </System>
      <EventData>
        <Data>{A106B734-DC1B-4AA6-BF6D-ED4C9679B1AC}</Data>
        <Data>{36A9302D-240D-4953-AF86-1F8861F49DDF}</Data>
        <Data>FILE</Data>
        <Data>D:\BizTalk\BizTalkKCAFIN\In\*.*</Data>
        <Data>InputReceiveLocation</Data>
        <Data>There was a failure executing the receive pipeline: "BizTalkKCAFIN.ExcelReceivePipeline, BizTalkKCAFIN, Version=1.0.0.0, Culture=neutral, PublicKeyToken=d85ea826ef104240" Source: "ExcelToXmlFileConversion" Receive Port: "InputReceivePort" URI: "D:\BizTalk\BizTalkKCAFIN\In\*.*" Reason: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data. </Data>
      </EventData>
    </Event>
    Friday, June 22, 2018 10:04 AM
  • Please share your pipeline code to review.

    This seems to be a general error with respect to Excel.

    There is a component/code you can refer in the below post(s)

    https://vkbiztalk.com/2018/04/27/biztalk-custom-pipeline-to-convert-excel-file-to-xml-file/

    https://stackoverflow.com/questions/28264240/convert-excel-xlsx-to-xml-with-c-sharp-and-biztalk

     

    • Marked as answer by Kiran Raj N Tuesday, June 26, 2018 8:49 AM
    • Unmarked as answer by Kiran Raj N Wednesday, June 27, 2018 11:06 AM
    • Marked as answer by Kiran Raj N Wednesday, June 27, 2018 11:07 AM
    Friday, June 22, 2018 7:05 PM
  • Have you taken a look at the suggestion provided earlier? Please let us know if that works for you.


    Mandar Dharmadhikari

    Monday, June 25, 2018 3:01 AM
    Moderator
  • using System;
    using System.Collections.Generic;
    using System.Text;
    using Microsoft.BizTalk.Message.Interop;
    using Microsoft.BizTalk.Component.Interop;
    using System.ComponentModel;
    using System.Resources;
    using System.Reflection;
    using System.Drawing;
    using System.IO;
    using System.Data.OleDb;
    using System.Data;
    
    namespace ExcelToXmlFileConvertion
    {
        [ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
        [ComponentCategory(CategoryTypes.CATID_Decoder)]
        [System.Runtime.InteropServices.Guid("7B684775-8957-4D78-A7E6-838380F9FA69")]
        public class Class1 :
    
             IBaseComponent,
            Microsoft.BizTalk.Component.Interop.IComponent,
            Microsoft.BizTalk.Component.Interop.IPersistPropertyBag,
            IComponentUI
        {
    
    
            #region Properties
    
            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; }
            }
    
    
            #endregion
    
            #region IBaseComponent Members
    
            [Browsable(false)]
            string IBaseComponent.Description
            {
                get { return "BizTalk Receive Pipeline Component for Decoding Excel Files"; }
            }
    
            [Browsable(false)]
            string IBaseComponent.Name
            {
                get { return "ExcelToXmlFileConversion"; }
            }
    
            [Browsable(false)]
            string IBaseComponent.Version
            {
                get { return "1.0"; }
            }
    
            #endregion
    
            #region IPersistPropertyBag Members
    
            void IPersistPropertyBag.GetClassID(out Guid classID)
            {
                classID = new Guid("7B684775-8957-4D78-A7E6-838380F9FA69");
    
            }
    
            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);
    
    
            }
    
            #endregion
    
            #region IComponentUI Members
    
            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;
            }
    
            #endregion
    
            #region IComponent Members
    
            IBaseMessage Microsoft.BizTalk.Component.Interop.IComponent.Execute(IPipelineContext pContext, IBaseMessage pInMsg)
            {
    
                IBaseMessagePart bodyPart = pInMsg.BodyPart;
                Stream originalStream = pInMsg.BodyPart.GetOriginalDataStream();
                Stream OutStream = originalStream;
                // if (bodyPart != null)
                // {
                try
                {
    
                    // First write the ODBC file to disk so can query it.
                    BinaryReader binaryReader = new BinaryReader(bodyPart.Data);
                    string folderName = this.TempDropFolderLocation;
                    if (folderName.Substring(folderName.Length - 1, 1) != "\\")
                        folderName += "\\";
                    string extension = ".xlsx";
                    string filename = System.IO.Path.GetRandomFileName();
                    filename = filename.Remove(8);
                    filename += extension;
                    string folderNameAndFileName = folderName + filename;
                    FileStream fileStream = new FileStream(folderNameAndFileName, FileMode.CreateNew);
                    BinaryWriter binaryWriter = new BinaryWriter(fileStream);
                    binaryWriter.Write(binaryReader.ReadBytes(Convert.ToInt32(binaryReader.BaseStream.Length)));
                    binaryWriter.Close();
                    binaryReader.Close();
    
                    // Create the Connection String for the ODBC File
                    string dataSource;
                    dataSource = "Data Source=" + folderNameAndFileName + ";";
                    string odbcConnectionString = this.connectionString;
                    if (odbcConnectionString.Substring(odbcConnectionString.Length - 1, 1) != ";")
                        odbcConnectionString += ";";
                    odbcConnectionString += dataSource;
                    System.Diagnostics.EventLog.WriteEntry("odbcConnectionString", odbcConnectionString);
                    OleDbConnection oConn = new OleDbConnection();
                    oConn.ConnectionString = odbcConnectionString;
    
                    // Create the Select Statement for the ODBC File
                    OleDbDataAdapter oCmd;
                    // Get the filter if there is one
                    string whereClause = "";
                    if (Filter.Trim() != "")
                        whereClause = " Where " + Filter.Trim();
                    oCmd = new OleDbDataAdapter(this.SqlStatement.Trim() + whereClause, oConn);
                    System.Diagnostics.EventLog.WriteEntry("BeforeConnectionOpen", "");
                    oConn.Open();
                    // Perform the Select statement from above into a dataset, into a DataSet. 
                    DataSet odbcDataSet = new DataSet();
                    oCmd.Fill(odbcDataSet, this.DataNodeName);
                    oConn.Close();
                    System.Diagnostics.EventLog.WriteEntry("AfterConnectionClose", "");
                    // Delete the message 
                    if (this.DeleteTempMessages)
                        System.IO.File.Delete(folderNameAndFileName);
    
                    // Write the XML From this DataSet into a String Builder
                    System.Text.StringBuilder stringBuilder = new StringBuilder();
                    System.IO.StringWriter stringWriter = new System.IO.StringWriter(stringBuilder);
                    odbcDataSet.Tables[0].WriteXml(stringWriter);
                    System.Diagnostics.EventLog.WriteEntry("DataSetWrite", "");
    
                    System.Xml.XmlDocument fromDataSetXMLDom = new System.Xml.XmlDocument();
                    fromDataSetXMLDom.LoadXml(stringBuilder.ToString());
    
                    // Create the Final XML Document. Root Node Name and Target Namespace
                    // come from properties set on the pipeline
                    System.Xml.XmlDocument finalMsgXmlDom = new System.Xml.XmlDocument();
                    System.Xml.XmlElement xmlElement;
                    xmlElement = finalMsgXmlDom.CreateElement("ns0", this.RootNodeName, this.NameSpace);
                    finalMsgXmlDom.AppendChild(xmlElement);
    
                    // Add the XML to the finalMsgXmlDom from the DataSet XML, 
                    // After this the XML Message will be complete
                    finalMsgXmlDom.FirstChild.InnerXml = fromDataSetXMLDom.FirstChild.InnerXml;
    
                    Stream strm = new MemoryStream();
                    // Save final XML Document to Stream
                    finalMsgXmlDom.Save(strm);
                    strm.Position = 0;
                    bodyPart.Data = strm;
                    pContext.ResourceTracker.AddResource(strm);
                    IBaseMessage outMsg = pInMsg;
                    outMsg.BodyPart.Data = strm;
                    return outMsg;
                }
    
                catch (System.Exception ex)
                {
                    throw ex;
                }
            }
        }
        #endregion
    }
    

    Monday, June 25, 2018 4:35 AM
  • Hi

    Instead of 

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

    You should use

    FileStream fileStream = new FileStream(folderNameAndFileName, FileMode.CreateNew, FileAccess.Write, FileShare.None);


    Mandar Dharmadhikari

    Monday, June 25, 2018 4:42 AM
    Moderator
  • I am getting an error like following after giving the file stream access and file share,
    Message Instance: {9A2A4409-17AF-4007-9D58-4EA28E6E589A}
    Instance ID: {76327D1B-314D-4D3B-87F0-ABB2D57569CF}
    FILE
    D:\BizTalk\BizTalkKCAFIN\Input\*.*
    InputReceiveLocations
    The Messaging Engine encountered an error during the processing of one or more inbound messages. 
    Monday, June 25, 2018 6:00 AM
  • Have you debugged the pipeline to see where it is throwing an exception?

    Mandar Dharmadhikari

    Monday, June 25, 2018 6:02 AM
    Moderator
  • How can debug a custom pipeline using Visual Studio?
    Monday, June 25, 2018 6:37 AM
  • Refer: 

    https://docs.microsoft.com/en-us/biztalk/core/debugging-custom-pipelines


    Mandar Dharmadhikari

    Monday, June 25, 2018 6:40 AM
    Moderator
  • Hi Krian,

    Looks like it is the same issue as mentioned in the thread https://social.msdn.microsoft.com/Forums/en-US/947609b7-a087-426f-88e1-65cff02f303f/using-a-new-custom-pipeline-for-convert-excel-data-to-xml-in-biz-talk-pipeline-get-suspended-while?forum=biztalkgeneral 

    Can you please confirm if both the threads are same?


    Mandar Dharmadhikari

    Monday, June 25, 2018 7:49 AM
    Moderator
  • Yes, Both are same thread.
    Monday, June 25, 2018 9:22 AM
  • I have merged the threads together so that the issue can be updated at a single place. Please let us know if you are able to resolve your issue.


    Mandar Dharmadhikari

    Monday, June 25, 2018 9:26 AM
    Moderator
  • I have tried the above link for debugging. but I can't get into the breakpoint in the source.
    Monday, June 25, 2018 9:27 AM
  • I hope the code deployed and the code open in Visual Studio are same?

    You need to attach the code to the Host Instance which is bound to the receive location. In case of debugging for ease attach the code to all the btsntsvc.exe entries that you see. The VS debugger will attach it to the correct process when the code gets executed.


    Mandar Dharmadhikari

    Monday, June 25, 2018 9:36 AM
    Moderator
  • Hi,

    I tried, but I can't step over into the pipeline execute method. Any other method is available for debugging?

    Thanks.

    Monday, June 25, 2018 10:41 AM
  • Weird, it should work like a charm

    Please describe the steps you have done so far, thanks

    Please note: To activate the debugger you need a message to trigger the pipeline

    /Peter

    Monday, June 25, 2018 10:45 AM
  • Just restart the visual Studio and try once again, if it doesnt work, can you do a fresh deployment and then try debugging?

    Mandar Dharmadhikari

    • Marked as answer by Kiran Raj N Tuesday, June 26, 2018 8:49 AM
    Monday, June 25, 2018 10:49 AM
    Moderator
  • I will try with a new deployment.

    Thanks for your fast reply.


    Monday, June 25, 2018 11:24 AM
  • Seems the most important question hasn't been asked.  Exactly what format is the file?

    .xls or .xlsx

    And as mentioned, Excel is the absolute last resort and the worst possible choice for message content.  Guaranteed, the Pipeline Component will not be your biggest problem.

    Monday, June 25, 2018 11:36 AM
    Moderator
  • Thanks, It works.
    Tuesday, June 26, 2018 2:00 PM
  • Well...

    For the benefit of future readers, this is not a recommendable way to do this.  :(  Office Automation within BizTalk is chronically unreliable.

    Two options are:

    1. A Script that invokes Excel's own Save As to convert the content to native Xml.  This can be run using a Scheduled Task.

    2. SSIS, which has native support for Excel formats.

    Tuesday, June 26, 2018 5:21 PM
    Moderator
  • I'd use SSIS or other automation tools build to run under a service account
    Also there's quite a few licensed BTS adapters out there

    /Peter


    Tuesday, June 26, 2018 5:25 PM