SQL Server Integration Services (SSIS) – Part 36 – Aggregate (MAXIMUM) Transformations Control in SSIS

Karthikanbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 13775 red flag

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

Introduction:


In this article we are going to see on how to use an Aggregate (Maximum) data flow transformation control in SSIS packaging. With this function operation we can get a number which is a maximum of a column. To start with this we should have the column to be of integer data type as specified in order to get the maximum. Let’s jump into to the example on how to use this control in real time.

To follow my series of articles on SSIS packages, please click here.

Steps:

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 for getting the maximum number in a particular column. 

Here we will see on the Maximum 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 get the maximum of the rows 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 a maximum as shown in the screen. And after selecting the numbers of columns for getting the maximum number now 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 (Maximum number in a column) are loaded to flat file destination which looks like below



Conclusion:


So in this article we have seen on how to get a maximum number in a row using an Aggregate function transformation in data flow tab.
Page copy protected against web site content infringement by Copyscape

About the Author

Karthikanbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Posted by: Omniitstudent on: 2/29/2012 | Points: 25
hi ,sir
i need help
can we use either DML & DDL statement with Sql function

Login to post response

Comment using Facebook(Author doesn't get notification)