c# - 无法将自定义组件的自定义属性保存到 DTSX 文件中

Question

我正在尝试创建我的第一个 SSIS 自定义源组件，但无法将自定义属性保存到 .dtsx 文件中。

根据 https://learn.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/persisting-custom-objects ，我所需要的只是实现 IDTSComponentPersist 接口(interface)，但这不起作用，LoadFromXML 和 SaveToXML 从未被调用。无论是在我保存文件还是在加载包时。

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

当我保存 SSIS 包并查看 XML 内部时，我得到了这个，没有定义数据类型也没有值:

我是不是错过了设置什么？

为了简化，我创建了一个小型测试项目。原始项目尝试保存一个包含 2 个字符串和 1 个整数的结构列表，但两者都有相同的“不正确”行为，从未调用 SaveToXML 和 LoadFromXML。

这是我的代码:

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);
            }
        }

    }
}

更新1:

添加 TypeConverter 和 UITypeEditor。仍然是相同的行为(不保存“复杂”数据类型)。

当我将源组件添加到数据流时，我得到了这个，一切看起来都很好:

我可以编辑属性，没问题

但是当我保存 SSIS 包并查看 xml 时，该属性仍然没有保存并且仍然具有 System.NULL 的数据类型:

谢谢!

Answer 1

重要说明 - 基于 Microsoft definition of IDTSComponentPersist Interface和在 Internet 上找到的 SaveToXML 的代码示例，我怀疑自定义持久性只能在自定义 SSIS 任务、连接管理器和枚举器上实现。

好吧，请自己选择是否真的需要实现自定义对象持久化。您的自定义属性似乎很适合标准数据类型 Int32 和 String。
来自 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.

因此，您确实需要做大量工作来保留样本中组件的所有属性，包括 LocaleID - 以防有人需要更改它。我可能会将 ListBadWords 自定义属性存储为没有自定义 XML 持久性的字符串。

在您的代码中 - System.Null 数据类型问题的最可能原因是 ProvideComponentProperties() 方法在组件初始化时被调用，当它被添加到数据流中。此时属性的数据类型是动态确定的，变量_badwords还没有初始化，是引用类型，所以定义为Null引用。 ProvideComponentProperties() 方法用于定义自定义属性并设置其默认值，解决您的问题——设置

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;
   }    

如果您打算实现自定义 XML 持久性 - 请学习 Microsoft code sample和其他来源。保存是以其他方式完成的。主要区别在于，在组件属性的 elementRoot 内部，每个属性都在其自己的 XML 节点下创建。 Node的InnerText用于存储属性值，可选的Node的属性可以存储额外的信息。