SQL Server Integration Services (SSIS) - Aggregate (SUM) Transformations Control in SSIS

This is part 33 of the series of articles on SSIS. In this article we are going to see how to use an Aggregate (SUM) data flow transformation control in SSIS packaging.


Introduction:


In this article we are going to see how to use an Aggregate (SUM) data flow transformation control in SSIS packaging. With this function operation we can get a summation of a number of rows necessary to get results in a desired format. Let's jump into to the example of how to use this control in real time.
To follow my series of articles on SSIS packages, please refer to my profile.

Steps:


Follow steps 1 to 3 in my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use an aggregate control for summing the rows and give as a single column.
Here we will see the SUM operation in the aggregate control. I have added an OLEDB connection which fetches the data from the database upon which we are going to do summation of a number of rows and then pass it to a file destination as shown in the screen below.

AggreSSIS1.jpg
Here the last 2 controls show a red mark inside the control indicating that the control is not configured. We step forward and configure them. Now double-click on the Aggregate function; that will open a pop-up window as shown in the screen below:
AggreSSIS2.jpg
Here we are selecting the columns that we need a SUM as shown in the screen. And after selecting the columns for summation then click on the OK button to get configured. Now configure the Flat File Destination as shown in the screen below.
AggreSSIS3.jpg
Once everything is configured your screen will look as shown in the screen below:
AggreSSIS4.jpg
Now Hit F5 to run the application and show the output as shown in the screen below:
AggreSSIS5.jpg
Here the numbers of rows are indicated at the bottom of each control as shown in the screen above. And finally the results (SUM of rows resulting in a single column) are loaded to the flat file destination which looks like below:
AggreSSIS6.jpg

Conclusion:

So in this article we have seen how to do a Summation of a rows using an Aggregate function transformation in a data flow tab.