When adding a derived column to a data flow with ezAPI, I get the following warnings
"Add stuff here.Inputs[Derived Column Input].Columns[ad_zip]" on "Add
stuff here" has usage type READONLY, but is not referenced by an
expression. Remove the column from the list of available input
columns, or reference it in an expression.
I've tried to delete the input columns, but either the method is not working or I'm doing it wrong:
foreach (Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn100 col in derFull.Meta.InputCollection[0].InputColumnCollection)
{
Console.WriteLine(col.Name);
derFull.DeleteInputColumn(col.Name);
}
I have the following piece of code that fixes the problem.
I got it from a guy called Daniel Otykier. So he is propably the one that should be credited for it... Unlesss he got it from someone else :-)
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);
}
}
}
Simple but not 100% Guaranteed Method
Call ReinitializeMetaData on the base component that EzApi is extending:
dc.Comp.ReinitializeMetaData();
This doesn't always respect some of the customizations and logic checks that EzAPI has, so test it carefully. For most vanilla components, though, this should work fine.
100% Guaranteed Method But Requires A Strategy For Identifying Columns To Ignore
You can set the UsageType property of those VirtualInputColumns to the enumerated value DTSUsageType.UT_IGNORED using EzApi's SetUsageType wrapper method.
But! You have to do this after you're done modifying any of the other metadata of your component (attaching other components, adding new input or output columns, etc.) since each of these triggers the ReinitializeMetaData method on the component, which automatically sets (or resets) all UT_IGNORED VirtualInputColumn's UsageType to UT_READONLY.
So some sample code:
// 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());
I was having exactly your problem and found a way to solve it. The problem is that the EzDerivedColumn has not the PassThrough defined in it's class.
You just need to add this to the class:
private PassThroughIndexer m_passThrough;
public PassThroughIndexer PassThrough
{
get
{
if (m_passThrough == null)
m_passThrough = new PassThroughIndexer(this);
return m_passThrough;
}
}
And alter the ReinitializeMetadataNoCast() to this:
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 { }
}
That is the strategy used by other components. If you want to see all the code you can check my EzApi2016@GitHub. I'm updating the original code from Microsoft to SQL Server 2016.
