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

Introduction

In this article we are going to see on 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.

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on 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 on 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

Here 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 will open a pop up windows as shown in the below screen

Here we are selecting the columns on which we need an average of as shown in the screen. And after selecting the number of columns for the aggregate then click on the OK button to get configured. Now configure the Flat File Destination as shown in the below screen.

Once everything is configured your screen will look as shown in the below screen

Now Hit F5 will run the application and show the output as shown in the below screen

Here the numbers of rows are indicated at the bottom of the 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

Conclusion:

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

About Author:
Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

Site Counter

Awards

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.