Cannot save custom property from a custom component into a DTSX file

Question

I'm trying to create my first SSIS custom source component but I can't get it to save the custom properties into the .dtsx file.

According to https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/persisting-custom-objects , all I needed is to implement the IDTSComponentPersist interface, but this doesn't work, the LoadFromXML and SaveToXML are never called. Neither when I save the file nor when I load the package.

However, if your object has properties that use complex data types, or if you want to perform custom processing on property values as they are loaded and saved, you can implement the IDTSComponentPersist interface and its LoadFromXML and SaveToXML methods. In these methods you load from (or save to) the XML definition of the package an XML fragment that contains the properties of your object and their current values. The format of this XML fragment is not defined; it must only be well-formed XML.

When I save the SSIS package and look inside the XML, I get this, no data type defined and no values :

Did I miss to set something?

To simplify, I created a small test project. The original project try to save a list of struct with 2 string and 1 integer, but both has the same "incorrect" behavior, SaveToXML and LoadFromXML are never called.

Here's my code:

using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Runtime;
using System.Xml;
using System.ComponentModel;
using System.Globalization;
using System.Drawing.Design;
using System.Windows.Forms.Design;
using System.Windows.Forms;

namespace TestCase
{
    public class MyConverter : TypeConverter
    {
        public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
        {
            return false;
        }
        public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
        {
            if (destinationType.Name.ToUpper() == "STRING")
                return string.Join(",", ((List<string>)value).ToArray());
            else
                return ((string)value).Split(',');
        }

        public override object ConvertFrom(ITypeDescriptorContext context, CultureInfo culture, object value)
        {
            if (value.GetType().Name.ToUpper() == "STRING")
                return ((string)value).Split(',');
            else
                return string.Join(",", ((List<string>)value).ToArray());
        }
    }

    class FancyStringEditor : UITypeEditor
    {
        public override UITypeEditorEditStyle GetEditStyle(ITypeDescriptorContext context)
        {
            return UITypeEditorEditStyle.Modal;
        }
        public override object EditValue(ITypeDescriptorContext context, IServiceProvider provider, object value)
        {
            var svc = (IWindowsFormsEditorService)provider.GetService(typeof(IWindowsFormsEditorService));
            List<string> vals = (List<string>)value;
            string valsStr = string.Join("\r\n", vals.ToArray());
            if (svc != null)
            {
                using (var frm = new Form { Text = "Your editor here" })
                using (var txt = new TextBox { Text = valsStr, Dock = DockStyle.Fill, Multiline = true })
                using (var ok = new Button { Text = "OK", Dock = DockStyle.Bottom })
                {
                    frm.Controls.Add(txt);
                    frm.Controls.Add(ok);
                    frm.AcceptButton = ok;
                    ok.DialogResult = DialogResult.OK;
                    if (svc.ShowDialog(frm) == DialogResult.OK)
                    {
                        vals = new List<string>();
                        vals.AddRange(txt.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries));
                        value = vals;
                    }
                }
            }
            return value;
        }
    }

    [DtsPipelineComponent(ComponentType = ComponentType.SourceAdapter,
                            CurrentVersion = 0,
                            Description = "Test class for saving",
                            DisplayName = "Test class",
                            IconResource = "None",
                            NoEditor = false,
                            RequiredProductLevel = Microsoft.SqlServer.Dts.Runtime.Wrapper.DTSProductLevel.DTSPL_NONE,
                            SupportsBackPressure = false,
                            UITypeName = "None")]
    public class TestSave : PipelineComponent, IDTSComponentPersist
    {
        private string _NbBadWordProperty = "NbBadWord";
        private string _ListBadWordsProperty = "ListBadWords";
        private List<string> _badWords;

        public IDTSCustomProperty100 _nb;
        public IDTSCustomProperty100 _list;

        public TestSave()
        {
            _badWords = new List<string>();
            _badWords.Add("Word1");
            _badWords.Add("Word2");
            _badWords.Add("Word3");
        }

        public void LoadFromXML(System.Xml.XmlElement node, IDTSInfoEvents infoEvents)
        {
            System.Windows.Forms.MessageBox.Show("Oh god! we're inside LoadFromXML!!");
        }

        public void SaveToXML(System.Xml.XmlDocument doc, IDTSInfoEvents infoEvents)
        {
            System.Windows.Forms.MessageBox.Show("Oh god! we're inside SaveToXML!!");
            XmlElement elementRoot;
            XmlNode propertyNode;

            // Create a new node to persist the object and its properties.  
            elementRoot = doc.CreateElement(String.Empty, "NBElement", String.Empty);
            XmlAttribute nbEl = doc.CreateAttribute("Nbelement");
            nbEl.Value = _badWords.Count.ToString();
            elementRoot.Attributes.Append(nbEl);

            // Save the three properties of the object from variables into XML.  
            foreach (string s in _badWords)
            {
                propertyNode = doc.CreateNode(XmlNodeType.Element, "BadWord", String.Empty);
                propertyNode.InnerText = s;
                elementRoot.AppendChild(propertyNode);
            }

            doc.AppendChild(elementRoot);
        }

        private IDTSCustomProperty100 GetCustomPropertyByName(string name)
        {
            foreach (IDTSCustomProperty100 prop in this.ComponentMetaData.CustomPropertyCollection)
                if (prop.Name.ToUpper() == name)
                    return prop;
            return null;
        }

        public override DTSValidationStatus Validate()
        {
            return DTSValidationStatus.VS_ISVALID;
        }
        public override void ProvideComponentProperties()
        {
            try
            {
                base.ProvideComponentProperties();

                // reset the component
                this.ComponentMetaData.OutputCollection.RemoveAll();
                this.ComponentMetaData.InputCollection.RemoveAll();

                // Add custom properties
                if (GetCustomPropertyByName(_NbBadWordProperty) == null)
                {
                    _nb = this.ComponentMetaData.CustomPropertyCollection.New();

                    _nb.Name = _NbBadWordProperty;
                    _nb.Description = "Number of bad word to filter";
                    _nb.State = DTSPersistState.PS_DEFAULT;
                    _nb.Value = _badWords.Count;

                    _nb.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                }

                if (GetCustomPropertyByName(_ListBadWordsProperty) == null)
                {
                    IDTSCustomProperty100 _list = this.ComponentMetaData.CustomPropertyCollection.New();
                    _list.Name = _ListBadWordsProperty;
                    _list.Description = "List of bad words";
                    _list.State = DTSPersistState.PS_DEFAULT;

                    _list.TypeConverter = typeof(MyConverter).AssemblyQualifiedName;
                    _list.Value = _badWords;
                    _list.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                    
                    _list.UITypeEditor = typeof(FancyStringEditor).AssemblyQualifiedName;
                }

                // add input objects
                // none

                // add output objects

                IDTSOutput100 o2 = this.ComponentMetaData.OutputCollection.New();
                o2.Name = "Dummy output";
                o2.IsSorted = false;

                foreach (IDTSCustomProperty100 p in this.ComponentMetaData.CustomPropertyCollection)
                {
                    if (p.Name == _ListBadWordsProperty)
                    {
                        MyConverter c = new MyConverter();
                        List<string> l = (List<string>)p.Value;

                        foreach (string s in l)
                        {
                            IDTSOutputColumn100 col1 = o2.OutputColumnCollection.New();
                            col1.Name = s.Trim();
                            col1.Description = "Bad word";
                            col1.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 500, 0, 0, 0);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Critical error: " + ex.Message);
            }
        }

    }
}

Update1:

Add the TypeConverter and UITypeEditor. Still the same behavior (not saving the "complex" data type).

When I add the source component to a data flow, I got this, everything look fine:

I can edit the property, no problem

But when I save the SSIS package and look at the xml, the property still not saved and still have a datatype of System.NULL:

Thanks!

Answer 1

Important Note - based on Microsoft definition of IDTSComponentPersist Interface and code samples of SaveToXML found on Internet, I suspect that custom persistence can only be implemented on custom SSIS Tasks, Connection Managers and Enumerators.

Well, please choose for yourself whether do you really need to implement custom object persistence. Your custom properties seems to fit well into standard data types Int32 and String.
Important note from Microsoft -

When you implement custom persistence, you must persist all the properties of the object, including both inherited properties and custom properties that you have added.

So, you really have to do a lot of work to persist all properties of component including LocaleID from your sample - in case someone needs to alter it. I would probably do storing ListBadWords custom property as a string without custom XML persistence.

On your code -- the most possible cause of the System.Null data type problem is that ProvideComponentProperties() method is called on initialization of the component, when it is added on the Data Flow. Data type of the property is determined dynamically at this moment, the variable _badwords is not initialized yet and is a reference type, so it is defined as Null reference. The ProvideComponentProperties() method is used to define custom properties and set its default values, to solve your problem - set

if (GetCustomPropertyByName(_ListBadWordsProperty) == null)
   {
   IDTSCustomProperty100 _list = this.ComponentMetaData.CustomPropertyCollection.New();
   _list.Name = _ListBadWordsProperty;
   _list.Description = "List of bad words";
   _list.State = DTSPersistState.PS_DEFAULT;

   _list.TypeConverter = typeof(MyConverter).AssemblyQualifiedName;
   // This is the change
   _list.Value = String.Empty;
   _list.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                    
   _list.UITypeEditor = typeof(FancyStringEditor).AssemblyQualifiedName;
   }    

If you set yourself up on implementing custom XML persistence - please study Microsoft code sample and other sources. Saving is done a little bit other way. The main difference is that inside elementRoot of the component properties, each property is created under its own XML Node. Node's InnerText is used to store property value, and optional Node's attributes can store additional information.