ARTICLE

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

Posted by Karthikeyan Anbarasan Articles | SQL Server April 14, 2011
In this article we are going to see how to use an Aggregate data flow transformation control in SSIS packaging.
Reader Level:

Introduction

In this article we are going to see how to use an Aggregate data flow transformation control in SSIS packaging. Aggregate functions are used to do a list of needed activities like Sum, Average, and Group by etc., on to a transformation output. To follow my series of articles on SSIS packages refer to my profile.

Steps:

Follow steps 1 to 3 on 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 along with the list of available operations. The lists of available aggregate functions are as follows: 
 
Aggregate Operation Description
Average Gives the average values of column values
Group by Divides the dataset into groups
Sum Sums the columns into a value, data types with integers are only taken into account
Count
Gives the number of items in a group
Count distinct Gives the number of unique non null number of items in a group
Minimum Gives the minimum number in a group
Maximum Gives the maximum number in a group
 
Here we will see the AVERAGE operation in the aggregate control. Here I have added an OLEDB connection which fetches the data from the database upon which we are going to do some manipulations and then pass it to a file destination as shown in the below screen.
 
SSIS1.jpg
 
Here the last 2 controls shows a red mark inside the control indicating that the control is not configured. We step forward and configure the same. Now double-click on the Aggregate function; that will open a pop up window as shown in the screen shown below:

SSIS2.jpg
 
Here we are selecting the columns for which we need an average as shown in the screen. After selecting the number of columns for the aggregate, click the OK button to get configured. Now configure the Flat File Destination as shown in the screen below.
 
SSIS3.jpg
 
Once everything is configured your screen will look as shown in the screen below:
 
SSIS4.jpg
 
Now Hit F5 to run the application and show the output as shown in the screen below:
 
SSIS5.jpg
 
Here the numbers of rows are indicated at the bottom of each control as shown in the above screen. And finally the results (AVERAGE of the columns) are loaded to flat file destination which looks like below:
 
SSIS6.jpg

Conclusion:

So in this article we have seen how to do an average of a number of columns using an Aggregate function.

COMMENT USING