SSIS – programmatically add an aggregate component


Intro

In this part of my series of blogs about dynamically modifying SSIS Packages I describe how to programmatically add or modify an Aggregate component.

Sample

The following code snippet shows how to add and modify an aggregate component by implementing the following steps

1. Add a new aggregate component to a data flow tasks.
2. Link the newly created component to another component in your data flow (this step is not included in the code example)
3. Delete the input and output column collection. This step is not necessary if you created a new aggregate component as mentioned in step 1. Do this step only in the case you modify an existing data flow, with an existing
ggregate component where you want to change the behavior by exchanging the columns in the aggregate.
4. Add input columns to the input column collection from the virtual input collection
5. Create output columns.
6. Modify the output columns properties

// Step 1: create aggregate task
IDTSComponentMetaData100 aggregate =
        dataFlowTask.ComponentMetaDataCollection.New();
      aggregate.ComponentClassID = "DTSTransform.Aggregate.2";

// Step 2:
// TODO:
// link the component to the upstream component here
CManagedComponentWrapper componentWrapper = component.Instantiate();
componentWrapper.AcquireConnections(null);

// Step 3:
// do this if you modify an existing aggregate task. You don't have
// to delete the input and output collection if you just created the
// component with the code above.
component.InputCollection[0].InputColumnCollection.RemoveAll();
component.OutputCollection[0].OutputColumnCollection.RemoveAll();

// Step 4:
// get the input/output and virutal column collection of the component
IDTSVirtualInput100 virtualInput = component.InputCollection[0].GetVirtualInput();
IDTSOutput100 outputCollection = component.OutputCollection[0];
IDTSInput100 inputCollection = component.InputCollection[0];
 

// add all the columns witch are involved in the aggregation to the
// component by retrieving the columns linage id from the virtual input
// collection and adding the column to the input column collection by calling
// the SetUsageType method.

int sourceColumnLineageId = virtualInput.VirtualInputColumnCollection[name].LineageID; 

componentWrapper.SetUsageType(
              inputCollection.ID,
              virtualInput,
              sourceColumnLineageId,
              DTSUsageType.UT_READONLY);

// Step 5:
// create a new output column
IDTSOutputColumn100 newOutputColumn = componentWrapper.InsertOutputColumnAt(outputCollection.ID, 0, "ColumnName", string.Empty);        

// set the data type porperties to the same values as these of the input column   newOutputColumn.SetDataTypeProperties(virtualInput.VirtualInputColumnCollection[name].DataType, virtualInput.VirtualInputColumnCollection["ColumnName"].Length, 0, 0, virtualInput.VirtualInputColumnCollection["ColumnName"].CodePage);

newOutputColumn.MappedColumnID = 0;

// Step 6
// modify the properties of the newly created column
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 = 0;
                    break;
             case "IsBig":
                    property.Value = 0;
                    break;
             case "AggregationComparisonFlags":
                    property.Value = 0;
                    break;
       }
}
 

After adding all the columns to the component which are involved in the aggregation, you may also want to add a column which holds the number of items in each group. This is done by adding another output column to the aggregate.

IDTSOutputColumn100 newOutputColumn = componentWrapper.InsertOutputColumnAt(outputCollection.ID, 0, "Count all", string.Empty);
newOutputColumn.SetDataTypeProperties(DataType.DT_UI4, 0, 0, 0, 0);
newOutputColumn.MappedColumnID = 0;
 
for (int i = 0; i < newOutputColumn.CustomPropertyCollection.Count; i++)
{
       IDTSCustomProperty100 property = newOutputColumn.CustomPropertyCollection[i];
       switch (property.Name)
       {
             case "AggregationColumnId":
                    property.Value = 0;
                    break;
             case "AggregationType":
                    property.Value = 2;
                    break;
             case "IsBig":
                    property.Value = 0;
                    break;
             case "AggregationComparisonFlags":
                    property.Value = 0;
                    break;
       }
}

 

Aggregate column properties

These are the possible values of the aggregate properties:

Property

Description

AggregationColumnId

LinageID of a column that participates in aggregate functions

AggregateType

Function

Value

Average

0

Count

1

CountAll

2

Count distinct

3

Group by

4

Maximum

5

Sum

6

 

IsBig

A value that indicates whether the column contains a value larger than 4 billion or a value with more precision than a double-precision floating-point value. The value can be 0 or 1. 0 indicates that IsBig is False and the column does not contain a large value or precise value. The default value of this property is 1.

 

AggregationComparisonFlags

 A value that specifies how the Aggregate transformation compares string data in a column.

Function

Value

Ignore case

0

Ignore kana type

1

Ignore character width

2

Ignore nonspacing characters

3

Ignore symbols

4

Sort punctuation as symbols

5

 

 

 

 


Comments (0)

Skip to main content