SSO Secret unavailable

Recently we had to restore an environment for a demo. This environment consists of a couple of servers, the main one (obviously) being a BizTalk 2009 server. Here we ran into this error in the SSO console application:

ERROR: Cannot perform encryption or decryption because the secret is not available from the master secret server.

My first attempt was to restore the secret from an old back-up file as described on MSDN: How to Restore the Master Secret. There you have to enter the password you used when configuring SSO and alas we couldn't retrieve that password.

Basically this means you're up the creek. I could find only one solution to this:
1. Export your bindings, and make notes of the host instances you use;
2. In BizTalk Server Configuration unconfigure Enterprise SSO (which will automatically unconfigure most of the other features);
3. In SQL Server Management Studio delete SSODB and the BizTalk databases (deleting just the SSODB and re-using the BizTalk databases won't work);
4. In BizTalk Server Configuration reconfigure all features from scratch (and this time make sure you have a way to retrieve the SSO Secret backup password!);
5. Recreate the host instances using the exact same names as before (they are hardcoded in the bindings file);
6. Redeploy your application(s);
7. Import your bindings.

If done correctly, you'll have this done in an hour or less. Which sure beats the time I had to spend on this...

Receive pipeline component for Excel files

When you want to import an Excel file into BizTalk, you used to have a problem. The old way Excel files used to be 'composed' was almost impossible to translate to an XML structure.

But the times they are a-changin' and nowadays Excel files are stored in the OpenXml format. Download the OpenXML SDK at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en and you can go ahead building your own pipeline components for importing of .xlsx and .xlsm files.

Here is an example of how you might do it. I highlighted the parts you should take special notice to.

A short explanation of the properties I'm using here:

UseColumnNamesFromFirstRow
If you set this property to 'true', the first row of your sheet is used to define the tag-names of your XML output. Say your spreadsheet looks like this:

	A	B	C
1	Name	Department	Salary
2	John	Sales	50000
3	Bill	Development	30000

With this property set to 'false', the resulting XML looks like this:

<ns0:root xmlns:ns0='nonamespace'>
  <ns0:row>
    <ns0:ColA>Name</ns0:ColA>
    <ns0:ColB>Department</ns0:ColB>
    <ns0:ColC>Salary</ns0:ColC>
  </ns0:row>
  <ns0:row>
    <ns0:ColA>John</ns0:ColA>
    <ns0:ColB>Sales</ns0:ColB>
    <ns0:ColC>50000</ns0:ColC>
  </ns0:row>
  <ns0:row>
    <ns0:ColA>Bill</ns0:ColA>
    <ns0:ColB>Development</ns0:ColB>
    <ns0:ColC>30000</ns0:ColC>
  </ns0:row>
</ns0:root>

When you set it to 'true', the XML will look like this:

<ns0:root xmlns:ns0='nonamespace'>
  <ns0:row>
    <ns0:A_Name>John</ns0:A_Name>
    <ns0:B_Department>Sales</ns0:B_Department>
    <ns0:C_Salary>50000</ns0:C_Salary>
  </ns0:row>
  <ns0:row>
    <ns0:A_Name>Bill</ns0:A_Name>
    <ns0:B_Department>Development</ns0:B_Department>
    <ns0:C_Salary>30000</ns0:C_Salary>
  </ns0:row>
</ns0:root>

Note that I'm adding the column as a prefix to make sure that the tags are different, even if there are 2 columns with the same header.

RootElementName
The name of the root-element of the resulting XML. It will default to 'root'.

RowElementName
The name of the row-elements of the resulting XML. It will default to 'row'.

NamespaceName
The namespace that will be used in the resulting XML. It defaults to 'nonamespace'.

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.Collections.Generic;
using System.ComponentModel;
using System.Xml;
using System.Xml.Schema;
using System.Data;
using System.Linq;
using Microsoft.BizTalk.Message.Interop;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Component;
using Microsoft.BizTalk.Messaging;
using Microsoft.BizTalk.Component.Utilities;

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace RonaldLokers.Blogspot.Com.PipelineComponents
{
    [ComponentCategory(CategoryTypes.CATID_PipelineComponent)]
    [System.Runtime.InteropServices.Guid("0022db2c-1599-4008-9312-ec3f9b66c356")]
    [ComponentCategory(CategoryTypes.CATID_Decoder)]
    public class Excel2007XmlExtractor: Microsoft.BizTalk.Component.Interop.IComponent, IBaseComponent, IPersistPropertyBag, IComponentUI
    {
        #region private variables
        private System.Resources.ResourceManagerresourceManager = new System.Resources.ResourceManager("RonaldLokers.Blogspot.Com.PipelineComponents.Excel2007XmlExtractor", Assembly.GetExecutingAssembly());

        // Propertie declarations.

        private bool _UseColumnNamesFromFirstRow;
        public bool UseColumnNamesFromFirstRow
        {
            get { return _UseColumnNamesFromFirstRow; }
            set { _UseColumnNamesFromFirstRow = value; }
        }
        private string _RootElementName;
        public string RootElementName
        {
            get { return string.IsNullOrEmpty(_RootElementName)?"root":_RootElementName; }
            set { _RootElementName = value; }
        }
        private string _RowElementName;
        public string RowElementName
        {
            get { return string.IsNullOrEmpty(_RowElementName)?"row":_RowElementName; }
            set { _RowElementName = value; }
        }
        private string _NamespaceName;
        public string NamespaceName
        {
            get { return string.IsNullOrEmpty(_NamespaceName)?"nonamespace":_NamespaceName; }
            set { _NamespaceName = value; }
        }

        #endregion
 
        #region IBaseComponent members
        /// <summary>
        /// Name of the component
        /// </summary>
        [Browsable(false)]
        public string Name
        {
            get
            {
                returnresourceManager.GetString("COMPONENTNAME", System.Globalization.CultureInfo.InvariantCulture);
            }
        }
        /// <summary>
        /// Version of the component
        /// </summary>
        [Browsable(false)]
        public string Version
        {
            get
            {
                returnresourceManager.GetString("COMPONENTVERSION", System.Globalization.CultureInfo.InvariantCulture);
            }
        }
        /// <summary>
        /// Description of the component
        /// </summary>
        [Browsable(false)]
        public string Description
        {
            get
            {
                return resourceManager.GetString("COMPONENTDESCRIPTION", System.Globalization.CultureInfo.InvariantCulture);
            }
        }
        #endregion
 
        #region IPersistPropertyBag members
        /// <summary>
        /// Gets class ID of component for usage from unmanaged code.
        /// </summary>
        /// <param name="classid">
        /// Class ID of the component
        /// </param>
        public void GetClassID(outSystem.Guid classid)
        {
            classid = newSystem.Guid("0022db2c-1599-4008-9312-ec3f9b66c356");
        }
        /// <summary>
        /// not implemented
        /// </summary>
        public void InitNew()
        {
        }
        /// <summary>
        /// Loads configuration properties for the component
        /// </summary>
        /// <param name="pb">Configuration property bag</param>
        /// <param name="errlog">Error status</param>
        public virtual void Load(Microsoft.BizTalk.Component.Interop.IPropertyBagpb, int errlog)
        {
            object val;
            val = this.ReadPropertyBag(pb, "UseColumnNamesFromFirstRow");
            if (val != null)
            {
                this.UseColumnNamesFromFirstRow = ((bool)(val));
            }
            val = this.ReadPropertyBag(pb, "RootElementName");
            if (val != null)
            {
                this.RootElementName = ((string)(val));
            }
            val = this.ReadPropertyBag(pb, "RowElementName");
            if (val != null)
            {
                this.RowElementName = ((string)(val));
            }
            val = this.ReadPropertyBag(pb, "NamespaceName");
            if (val != null)
            {
                this.NamespaceName = ((string)(val));
            }
        }
        /// <summary>
        /// Saves the current component configuration into the property bag
        /// </summary>
        /// <param name="pb">Configuration property bag</param>
        /// <param name="fClearDirty">not used</param>
        /// <param name="fSaveAllProperties">not used</param>
        public virtual void Save(Microsoft.BizTalk.Component.Interop.IPropertyBagpb, bool fClearDirty, boolfSaveAllProperties)
        {
            this.WritePropertyBag(pb, "UseColumnNamesFromFirstRow", this.UseColumnNamesFromFirstRow);
            this.WritePropertyBag(pb, "RootElementName", this.RootElementName);
            this.WritePropertyBag(pb, "RowElementName", this.RowElementName);
            this.WritePropertyBag(pb, "NamespaceName", this.NamespaceName);
        }
        #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.IPropertyBagpb, string propName)
        {
            object val = null;
            try
            {
                pb.Read(propName, outval, 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.IPropertyBagpb, string propName, object val)
        {
            try
            {
                pb.Write(propName, refval);
            }
            catch (System.Exception e)
            {
                throw new System.ApplicationException(e.Message);
            }
        }
        #endregion
        #endregion
 
        #region IComponentUI members
        /// <summary>
        /// Component icon to use in BizTalk Editor
        /// </summary>
        [Browsable(false)]
        public IntPtr Icon
        {
            get
            {
                return((System.Drawing.Bitmap)(this.resourceManager.GetObject("COMPONENTICON", System.Globalization.CultureInfo.InvariantCulture))).GetHicon();
            }
        }
        /// <summary>
        /// The Validate method is called by the BizTalk Editor during the build
        /// of a BizTalk project.
        /// </summary>
        /// <param name="obj">An Object containing the configuration properties.</param>
        /// <returns>The IEnumerator enables the caller to enumerate through a collection of strings containing error messages. These error messages appear as compiler error messages. To report successful property validation, the method should return an empty enumerator.</returns>
        public System.Collections.IEnumerator Validate(object obj)
        {
            // example implementation:
            // ArrayList errorList = new ArrayList();
            // errorList.Add("This is a compiler error");
            // return errorList.GetEnumerator();
            return null;
        }
        #endregion
 
        #region IComponent members
        /// <summary>
        /// Implements IComponent.Execute method.
        /// </summary>
        /// <param name="pc">Pipeline context</param>
        /// <param name="inmsg">Input message</param>
        /// <returns>Original input message</returns>
        /// <remarks>
        /// IComponent.Execute method is used to initiate
        /// the processing of the message in this pipeline component.
        /// </remarks>

         // Here we go... this is where the real work is done!

        public Microsoft.BizTalk.Message.Interop.IBaseMessageExecute(Microsoft.BizTalk.Component.Interop.IPipelineContextpc, Microsoft.BizTalk.Message.Interop.IBaseMessageinmsg)
        {

            // The 'SpreadsheetDocument.Open' method throws a System.IndexOutOfRange exception when you use the stream from inmsg directly. This is because this is a Microsoft.BizTalk.Message.Interop.StreamViewOfIStream occurence. So first I copy it to a regular MemoryStream.

            Stream instream = CopyStream(inmsg.BodyPart.GetOriginalDataStream());

            //try
            //{
                string xml = string.Empty;
                XmlDocument xmldoc = new XmlDocument();
                XmlElement root = xmldoc.CreateElement(RootElementName, NamespaceName);
                xmldoc.AppendChild(root);
                using (SpreadsheetDocument doc = SpreadsheetDocument.Open(instream, false))
                {

                    // I'm not going into all the details of the OpenXml format. You can find it all in the SDK.

                    WorkbookPart wbp = doc.WorkbookPart;
                    SharedStringTablePart shareStringPart = doc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    SharedStringItem[] items = shareStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();

                    Sheet sheet = wbp.Workbook.Descendants<Sheet>().First();
                    Worksheet worksheet = ((WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id)).Worksheet;
                    Hashtable columnHeaders = new Hashtable();
                    if(_UseColumnNamesFromFirstRow)
                    {

                        // Here is the part where I look for the column headers. I loop through the rows of the sheet to find the top row (RowIndex.Value == 1) and then put all cell values into a Hashtable. (There's probably an easier way to get the first row, but I couldn't figure it out, and this works too ;-)

                        foreach(Row row in worksheet.Descendants<Row>())
                        {
                            if(row.RowIndex.Value == 1)
                            {
                                foreach(Cell cell in row)
                                {
                                    string columnheader = GetCellValue(cell, items).Replace(' ', '_');
                                    if(!string.IsNullOrEmpty(columnheader))
                                    {
                                        string col = GetCellColumn(cell);

                                        // As explained above, I'm adding the column as a prefix.

                                        columnHeaders.Add(col, col + "_" + columnheader);
                                    }
                                }
                                break;
                            }
                        }
                    }
                    foreach (Row row in worksheet.Descendants<Row>())
                    {
                        if(!(_UseColumnNamesFromFirstRow && row.RowIndex.Value == 1))
                        {

                            // And here the XmlDocument is being build.

                            XmlElement rownode = xmldoc.CreateElement(RowElementName, NamespaceName);
                            root.AppendChild(rownode);
                            foreach(Cell cell in row)
                            {
                                string col = GetCellColumn(cell);
                                string nodename;
                                if(columnHeaders.ContainsKey(col))
                                    nodename = (string)columnHeaders[col];
                                else
                                    nodename = "Col" + col;
                                string cellvalue = GetCellValue(cell, items).Trim();
                                if(!string.IsNullOrEmpty(cellvalue))
                                {
                                    XmlElement node = xmldoc.CreateElement(nodename, NamespaceName);
                                    node.InnerText = cellvalue;
                                    rownode.AppendChild(node);
                                }
                            }
                        }
                    }
                }

                // Finally write the XmlDocument contents to a stream and replace the pipeline stream with this new stream.

                MemoryStream memStr = new MemoryStream();
                StreamWriter strOut = new StreamWriter(memStr);
                strOut.Write(xmldoc.OuterXml);
          
 
                strOut.Flush();
                memStr.Position = 0;
                pc.ResourceTracker.AddResource(memStr);
                inmsg.BodyPart.Data = memStr;
            //}
            //catch (Exception exc)
            //{
            // You can do your own error handling here...
            //}

            return inmsg;
        }
        private Stream CopyStream(Streaminput)
        {
            Stream output = new MemoryStream();
            byte[] buffer = new byte[32768];
            int read = 1;
            while (read > 0) {
                read = input.Read(buffer, 0, buffer.Length);
                if (read > 0)          
                    output.Write(buffer, 0, read);
            }
            return output;
        }
        private string GetCellColumn(Cellcell)
        {
            string cellref = cell.CellReference.Value;
            int pos = 0;
            int column;
            while (!int.TryParse(cellref.Substring(pos), out column))
                pos++;
            returncellref.Substring(0, pos);
        }
        private string GetCellValue(Cellcell, SharedStringItem[] items)
        {
            string txt = string.Empty;
            if (cell.CellValue != null)
                txt = cell.CellValue.Text;
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                txt = items[int.Parse(txt)].InnerText;
            return txt;
       }

        #endregion
    }
}