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.

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

Reference

Aggregate Transformation Editor (Advanced Tab)

Other Resources

Integration Services Error and Message Reference
How to: Aggregate Values in a Dataset Using the Aggregate Transformation

Help and Information

Getting SQL Server 2005 Assistance