Aggregate Values in a Dataset by Using the Aggregate Transformation
To add and configure an Aggregate transformation, the package must already include at least one Data Flow task and one source.
To aggregate values in a dataset
In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
In Solution Explorer, double-click the package to open it.
Click the Data Flow tab, and then, from the Toolbox, drag the Aggregate transformation to the design surface.
Connect the Aggregate transformation to the data flow by dragging a connector from the source or the previous transformation to the Aggregate transformation.
Double-click the transformation.
In the Aggregate Transformation Editor dialog box, click the Aggregations tab.
In the Available Input Columns list, select the check box by the columns on which you want to aggregate values. The selected columns appear in the table.
Note
You can select a column multiple times, applying multiple transformations to the column. To uniquely identify aggregations, a number is appended to the default name of the output alias of the column.
Optionally, modify the value in the Output Alias columns.
To change the default aggregation operation, Group by, select a different operation in the Operation list.
To change the default comparison, select the individual comparison flags listed in the Comparison Flags column. By default, a comparison ignores case, kana type, non-spacing characters, and character width.
Optionally, for the Count distinct aggregation, specify an exact number of distinct values in the Count Distinct Keys column, or select an approximate count in the Count Distinct Scale column.
Note
Providing the number of distinct values, exact or approximate, optimizes performance, because the transformation can preallocate an appropriate amount of memory to do its work.
Optionally, click Advanced and update the name of the Aggregate transformation output. If the aggregations include a Group By operation, you can select an approximate count of grouping key values in the Keys Scale column or specify an exact number of grouping key values in the Keys column.
Note
Providing the number of distinct values, exact or approximate, optimizes performance, because the transformation can preallocate an appropriate amount of memory to do its work.
Note
The Keys Scale and Keys options are mutually exclusive. If you enter values in both columns, the larger value in either Keys Scale or Keys is used.
Optionally, click the Advanced tab and set the attributes that apply to optimizing all the operations that the Aggregate transformation performs.
Click OK.
To save the updated package, click Save Selected Items on the File menu.