Aggregate Transformation Editor (Aggregations Tab)

Use the Aggregations tab of the Aggregate Transformation Editor dialog box to specify columns for aggregation and aggregation properties. You can apply multiple aggregations. This transformation does not generate an error output.

Note

The options for key count, key scale, distinct key count, and distinct key scale apply at the component level when specified on the Advanced tab, at the output level when specified in the advanced display of the Aggregations tab, and at the column level when specified in the column list at the bottom of the Aggregations tab.

In the Aggregate transformation, Keys and Keys scale refer to the number of groups that are expected to result from a Group by operation. Count distinct keys and Count distinct scale refer to the number of distinct values that are expected to result from a Distinct count operation.

To learn more about the Aggregate transformation, see Aggregate Transformation.

Options

Advanced / Basic
Display or hide options to configure multiple aggregations for multiple outputs. By default, the Advanced options are hidden.

Aggregation Name
In the Advanced display, type a friendly name for the aggregation.

Group By Columns
In the Advanced display, select columns for grouping by using the Available Input Columns list as described below.

Key Scale
In the Advanced display, optionally specify the approximate number of keys that the aggregation can write. By default, the value of this option is Unspecified. If both the Key Scale and Keys properties are set, the value of Keys takes precedence.

Value Description
Unspecified The Key Scale property is not used.
Low Aggregation can write approximately 500,000 keys.
Medium Aggregation can write approximately 5,000,000 keys.
High Aggregation can write more than 25,000,000 keys.

Keys
In the Advanced display, optionally specify the exact number of keys that the aggregation can write. If both Key Scale and Keys are specified, Keys takes precedence.

Available Input Columns
Select from the list of available input columns by using the check boxes in this table.

Input Column
Select from the list of available input columns.

Output Alias
Type an alias for each column. The default is the name of the input column; however, you can choose any unique, descriptive name.

Operation
Choose from the list of available operations, using the following table as a guide.

Operation Description
GroupBy Divides datasets into groups. Columns with any data type can be used for grouping. For more information, see GROUP BY.
Sum Sums the values in a column. Only columns with numeric data types can be summed. For more information, see SUM.
Average Returns the average of the column values in a column. Only columns with numeric data types can be averaged. For more information, see AVG.
Count Returns the number of items in a group. For more information, see COUNT.
CountDistinct Returns the number of unique nonnull values in a group. For more information, see COUNT and Distinct.
Minimum Returns the minimum value in a group. Restricted to numeric data types.
Maximum Returns the maximum value in a group. Restricted to numeric data types.

Comparison Flags
If you choose Group By, use the check boxes to control how the transformation performs the comparison. For information on the string comparison options, see Comparing String Data.

Count Distinct Scale
Optionally specify the approximate number of distinct values that the aggregation can write. By default, the value of this option is Unspecified. If both CountDistinctScale and CountDistinctKeys are specified, CountDistinctKeys takes precedence.

Value Description
Unspecified The CountDistinctScale property is not used.
Low Aggregation can write approximately 500,000 distinct values.
Medium Aggregation can write approximately 5,000,000 distinct values.
High Aggregation can write more than 25,000,000 distinct values.

Count Distinct Keys
Optionally specify the exact number of distinct values that the aggregation can write. If both CountDistinctScale and CountDistinctKeys are specified, CountDistinctKeys takes precedence.

See Also

Integration Services Error and Message Reference
Aggregate Transformation Editor (Advanced Tab)
Aggregate Values in a Dataset by Using the Aggregate Transformation