使用EZAPI EzDerivedColumn和输入列的警告消息

Question

当使用ezAPI向数据流中添加派生列时，我会收到以下警告

在“在这里添加内容”上的"Add READONLY here.InputsDerived Input.Columnsad_zip“具有用法类型READONLY，但没有被表达式引用。从可用输入列列表中删除该列，或在表达式中引用该列。

我尝试删除输入列，但要么方法无效，要么我做错了：

foreach (Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn100 col in derFull.Meta.InputCollection[0].InputColumnCollection)
{
    Console.WriteLine(col.Name);
    derFull.DeleteInputColumn(col.Name);
}

Answer 1

下面的代码解决了这个问题。

我从一个叫丹尼尔·奥蒂基尔的人那里得到的。所以他很可能是那个应该归功于他的人.除非他是从别人那里得到的-)

static public void RemoveUnusedInputColumns(this EzDerivedColumn component)

    {
        var usedLineageIds = new HashSet<int>();

        // Parse all expressions used in new output columns, to determine which input lineage ID's are being used:
        foreach (IDTSOutputColumn100 column in component.GetOutputColumns())
        {
            AddLineageIdsFromExpression(column.CustomPropertyCollection, usedLineageIds);
        }

        // Parse all expressions in replaced input columns, to determine which input lineage ID's are being used:
        foreach (IDTSInputColumn100 column in component.GetInputColumns())
        {
            AddLineageIdsFromExpression(column.CustomPropertyCollection, usedLineageIds);
        }

        var inputColumns = component.GetInputColumns();

        // Remove all input columns not used in any expressions:
        for (var i = inputColumns.Count - 1; i >= 0; i--)
        {
            if (!usedLineageIds.Contains(inputColumns[i].LineageID))
            {
                inputColumns.RemoveObjectByIndex(i);
            }
        }
    }


    static private void AddLineageIdsFromExpression(IDTSCustomPropertyCollection100 columnProperties, ICollection<int> lineageIds)
    {
        int lineageId = 1;

        var expressionProperty = columnProperties.Cast<IDTSCustomProperty100>().FirstOrDefault(p => p.Name == "Expression");
        if (expressionProperty != null)
        {
            // Input columns used in expressions are always referenced as "#xxx" where xxx is the integer lineage ID.
            var expression = expressionProperty.Value.ToString();
            var expressionTokens = expression.Split(new[] { ' ', ',', '(', ')' });

            foreach (var c in expressionTokens.Where(t => t.Length > 1 && t.StartsWith("#") && int.TryParse(t.Substring(1), out lineageId)))
            {
                if (!lineageIds.Contains(lineageId)) lineageIds.Add(lineageId);
            }
        }
    }

Answer 2

简单但不是100%保证的方法

在ReinitializeMetaData正在扩展的基本组件上调用EzApi：

dc.Comp.ReinitializeMetaData();

这并不总是尊重EzAPI的一些自定义和逻辑检查，所以请仔细测试它。然而，对于大多数普通的组件来说，这应该很好。

100%保证的方法，但需要忽略列的标识策略

可以使用EzApi的UsageType包装器方法将这些VirtualInputColumns的SetUsageType属性设置为枚举值DTSUsageType.UT_IGNORED。

但!您必须在完成修改组件的任何其他元数据(附加其他组件、添加新的输入或输出列等)后执行操作。因为其中每一个都会触发组件上的ReinitializeMetaData方法，该方法会自动设置(或重置)所有UT_IGNORED VirtualInputColumn的UsageType到UT_READONLY。

因此，一些示例代码：

// define EzSourceComponent with SourceColumnToIgnore output column, SomeConnection for destination

EzDerivedColumn dc = new EzDerivedColumn(this);
dc.AttachTo(EzSourceComponent);
dc.Name = "Errors, Go Away";

dc.InsertOutputColumn("NewDerivedColumn");
dc.Expression["NewDerivedColumn"] = "I was inserted!";            

// Right here, UsageType is UT_READONLY
Console.WriteLine(dc.VirtualInputCol("SourceColumnToIgnore").UsageType.ToString());

EzOleDbDestination d = new EzOleDbDestination(f);
d.Name = "Destination";
d.Connection = SomeConnection;
d.Table = "dbo.DestinationTable";
d.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD;
d.AttachTo(dc);

// Now we can set usage type on columns to remove them from the available inputs.
// Note the false boolean at the end.
// That's required to not trigger ReinitializeMetadata for usage type changes.
dc.SetUsageType(0, "SourceColumnToIgnore", DTSUsageType.UT_IGNORED, false);

// Now UsageType is UT_IGNORED and if you saved the package and viewed it,
// you'll see this column has been removed from the available input columns
// ... and the warning for it has gone away!
Console.WriteLine(dc.VirtualInputCol("SourceColumnToIgnore").UsageType.ToString());

Answer 3

我正好遇到了你的问题，找到了解决办法。问题是，EzDerivedColumn没有在它的类中定义PassThrough。

您只需将其添加到类中：

    private PassThroughIndexer m_passThrough;
    public PassThroughIndexer PassThrough
    {
        get
        {
            if (m_passThrough == null)
                m_passThrough = new PassThroughIndexer(this);
            return m_passThrough;
        }
    }

并将ReinitializeMetadataNoCast()修改为：

 public override void ReinitializeMetaDataNoCast()
    {
        try
        {
            if (Meta.InputCollection[0].InputColumnCollection.Count == 0)
            {
                base.ReinitializeMetaDataNoCast();
                LinkAllInputsToOutputs();
                return;
            }

            Dictionary<string, bool> cols = new Dictionary<string, bool>();
            foreach (IDTSInputColumn100 c in Meta.InputCollection[0].InputColumnCollection)
                cols.Add(c.Name, PassThrough[c.Name]);
            base.ReinitializeMetaDataNoCast();
            foreach (IDTSInputColumn100 c in Meta.InputCollection[0].InputColumnCollection)
            {
                if (cols.ContainsKey(c.Name))
                    SetUsageType(0, c.Name, cols[c.Name] ? DTSUsageType.UT_READONLY : DTSUsageType.UT_IGNORED, false);
                else
                    SetUsageType(0, c.Name, DTSUsageType.UT_IGNORED, false);
            }
        }
        catch { }
    }

这是其他组件使用的策略。如果您想查看所有代码，可以检查我的EzApi2016@GitHub。我正在将原始代码从Microsoft更新到Server 2016。