Archive for March, 2011
Posted by Karthikeyan Anbarasan on March 22, 2011
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 (Minimum) data flow transformation control in SSIS packaging.
Introduction:
In this article we are going to see on how to use an Aggregate (Minimum) data flow transformation control in SSIS packaging. With this function operation we can get a number which is a minimum 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 minimum. Let’s jump into to the example on how to use this control in real time.
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 Minimum number in a particular column.
Here we will see on the Minimum 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 minimum 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 minimum as shown in the screen. And after selecting the numbers of columns for getting the minimum 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 (Minimum 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 minimum number in a row using an Aggregate function transformation in data flow tab.
Like this:
Be the first to like this post.
Posted in SSIS | Tagged: Aggregate Function, Aggregate Function Minimum, Average, BI, BIDS, Business Intelligence, Business Intelligence Development Studio, Control flow, Dataflow, Exchange, Flat file database, Flat File Destination, OLE DB, Package, SQL Server 2008, SQL Server Integration Services, SSAS, SSIS, SSMS, SSRS, Transfer | Leave a Comment »
Posted by Karthikeyan Anbarasan on March 22, 2011
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.
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.
Like this:
Be the first to like this post.
Posted in SSIS | Tagged: Aggregate Function, Aggregate Function maximum, Average, BI, BIDS, Business Intelligence, Business Intelligence Development Studio, Control flow, Dataflow, Exchange, Flat file database, Flat File Destination, OLE DB, Package, SQL Server 2008, SQL Server Integration Services, SSAS, SSIS, SSMS, SSRS, Transfer | Leave a Comment »
Posted by Karthikeyan Anbarasan on March 22, 2011
This is part 35 of the series of article on SSIS. In this article we are going to see on how to use an Aggregate (COUNT DISTINCT) data flow transformation control in SSIS packaging
Introduction:
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 look into my series of article on SSIS at the url - http://f5debug.net/all-articles/
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 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
Conclusion:
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.
Like this:
Be the first to like this post.
Posted in SSIS | Tagged: Aggregate Function, BI, BIDS, Business Intelligence, Control flow, Count, Countdistinct, Dataflow, Distinct, Exchange, NULL, OLE DB, Package, SQL, SQL Server 2008, SQL Server Integration Services, SSAS, SSIS, SSMS, SSRS, Transfer | Leave a Comment »
Posted by Karthikeyan Anbarasan on March 22, 2011
This is part 34 of the series of article on SSIS. In this article we are going to see on how to use an Aggregate (COUNT) data flow transformation control in SSIS packaging.
Introduction:
In this article we are going to see on how to use an Aggregate (COUNT) data flow transformation control in SSIS packaging. With this function operation we can get a Count on the number of rows necessary to get result in desired format. Let’s jump into to the example on how to use this control in real time.
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 count of the rows of a particular column or a list of columns.
Here we will see on the COUNT 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 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 Count as shown in the screen. And after selecting the numbers of columns for getting the 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 (COUNT of rows) are loaded to flat file destination which looks like below
Conclusion:
So in this article we have seen on how to get a count of number of rows using an Aggregate function transformation in data flow tab.
Like this:
Be the first to like this post.
Posted in SSIS | Tagged: Aggregate Function, BI, BIDS, Business Intelligence, Count, Exchange, Package, SQL Server 2008, SSAS, SSIS, SSMS, SSRS, Transfer | Leave a Comment »
