I am working on programmatically creating Aggregate transform with aggregation type as count distinct and i am able to create other aggregations like min,max,count.. but when it comes to count distinct i am getting below error
The component has detected potential metadata corruption during validation. Error at Data Flow Task - Load Count Dist [Aggregate - All [2]]: The "Aggregate - All.Outputs[Aggregate Output 1].Columns[col1]" is missing the required property "CountDistinctScale". The object is required to have the specified custom property.
I am unable to find "CountDistinctScale" custom property as this custom property doesn't exit for other aggregation and magically appears when count distinct is selected,is there a method which i need to call to create new custom property?
I understand there are not a lot of people who know how to programmatically create package, please help me find someone with knowledge or suggest me how i can get some help.
IDTSComponentMetaData100 Aggregate = pipeline.ComponentMetaDataCollection.New();
Aggregate.ComponentClassID = app.PipelineComponentInfos["Aggregate"].CreationName;
// Get the design time instance of the derived column
var DesignAggregate = Aggregate.Instantiate();
DesignAggregate.ProvideComponentProperties(); //design time
Aggregate.Name = "AggregateComponent";
IDTSPath100 AggregatePath = pipeline.PathCollection.New();
AggregatePath.AttachPathAndPropagateNotifications(pipeline.ComponentMetaDataCollection[Prev_Transform.Transformation_Name].OutputCollection[Prev_Transform.Output_Number], Aggregate.InputCollection[0]);
//update the metadata for the derived columns
DesignAggregate.AcquireConnections(null);
DesignAggregate.ReinitializeMetaData();
DesignAggregate.ReleaseConnections();
// Mark the columns we are joining on
IDTSInput100 AggregateInput = Aggregate.InputCollection[0];
IDTSInputColumnCollection100 AggregateInputColumns = AggregateInput.InputColumnCollection;
IDTSVirtualInput100 AggregateVirtualInput = AggregateInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 AggregateVirtualInputColumns = AggregateVirtualInput.VirtualInputColumnCollection;
IDTSOutput100 AggregateoutputCollection = Aggregate.OutputCollection[0];
// Note: input columns should be marked as READONLY
foreach (IDTSVirtualInputColumn100 vColumn in AggregateVirtualInputColumns)
{
int sourceColumnLineageId = AggregateVirtualInput.VirtualInputColumnCollection[vColumn.Name].LineageID;
DesignAggregate.SetUsageType(AggregateInput.ID, AggregateVirtualInput, sourceColumnLineageId, DTSUsageType.UT_READONLY);
// create a new output column
IDTSOutputColumn100 newOutputColumn = DesignAggregate.InsertOutputColumnAt(AggregateoutputCollection.ID, 0, vColumn.Name, string.Empty);
// set the data type porperties to the same values as these of the input column
newOutputColumn.SetDataTypeProperties(AggregateVirtualInput.VirtualInputColumnCollection[vColumn.Name].DataType, AggregateVirtualInput.VirtualInputColumnCollection[vColumn.Name].Length, 0, 0, AggregateVirtualInput.VirtualInputColumnCollection[vColumn.Name].CodePage);
newOutputColumn.MappedColumnID = 0;
for (int i = 0; i < newOutputColumn.CustomPropertyCollection.Count; i++)
{
IDTSCustomProperty100 property = newOutputColumn.CustomPropertyCollection[i];
switch (property.Name)
{
case "AggregationColumnId":
property.Value = sourceColumnLineageId;
break;
case "AggregationType":
property.Value = 3;
break;
case "IsBig":
property.Value = 1;
break;
case "AggregationComparisonFlags":
property.Value = 0;
break;
}
}
}