.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Ava Aiden
Imran Ghani
Thomas Shaw

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Aggregate (COUNT DISTINCT) Transformations Control-SQL Server Integration Services (SSIS)-Part 35

Posted By:Karthikeyan Anbarasan       Posted Date: April 29, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

In this article we are going to see on how to use an Aggregate (COUNT DISTINCT) data flow transformation control in SQL Server Integration Services (SSIS) packaging.


In this article we are going to see on how to use an Aggregate (COUNT DISTINCT) data flow transformation control in SSIS packaging. With this function operation we can get a Count on the number of distinct rows which are unique and not null and to get result in desired format. Let's jump into to the example on how to use this control in real time.

You can also read my other articles on SSIS here.



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 count of the distinct rows of a particular column or a list of columns.

Here we will see on the COUNT DISTINCT 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 Count of the distinct number of 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 distinct Count as shown in the screen. And after selecting the numbers of columns for getting the distinct count 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 (Distinct COUNT of rows) are loaded to flat file destination which looks like below


So in this article we have seen on how to get a distinct not null count of number of rows using an Aggregate function transformation in data flow tab.
 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend