2

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

            }
Hadi
  • 36,233
  • 13
  • 65
  • 124
sam
  • 345
  • 2
  • 4
  • 18
  • There is a built in transformation (Aggregate) on the data flow level. Why are you not using that? – rvphx Apr 12 '19 at 18:11
  • Yes thats what i am using but i am trying to use SSIS API to generate Aggregate transform programmatically(i.e via code and not UI) – sam Apr 12 '19 at 19:26
  • I am sorry, but I am obviously missing your point. What are you trying to achieve by rewriting the same stuff? – rvphx Apr 12 '19 at 21:09
  • We can generate SSIS packages manually by going into visual studio, drag and drop aggregate component and then manually selecting aggregation for each column, alternatively we can call SSIS API and pass all that manual work as parameters, and then we can generate the package automatically. see below link for how to do it https://learn.microsoft.com/en-us/sql/integration-services/building-packages-programmatically/creating-a-package-programmatically?view=sql-server-2017 – sam Apr 13 '19 at 18:53

0 Answers0