The Aggregate Transformation applies aggregate functions to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across.
The Aggregate Transformation supports following operations:
GROUP BY, SUM, AVERAGE, COUNT, COUNT DISTINCT, MAXIMUM, MINIMUM
1. SUM: This function Sums up the values present in a column. Only columns with numeric data types can be summed.
2. AVG: This function Returns the average of all the column values present in a column. Only columns with numeric data types can be averaged.
3. MAX: Returns the maximum value present in a group.
4. MIN: Returns the minimum value present in a group.
5. COUNT: Returns the number of items present in a group.
6. COUNT DISTINCT: Returns the number of unique non null values present in a group.
7. GROUP BY: Divides data sets into groups. Columns of any data type can be used for grouping.
Aggregate transformation is a blocked and Asynchronous transformation. With Asynchronous, the Output rows will not be equal to the Input rows. As aggregation is performed on all the column values, it will not release any row until it processes all rows.
1. Create a SSIS package with name “Aggregate1”.
2. In SSDT, drag and drop Data Flow Task (DFT) to control flow window.
3. Double click on Data Flow Task. It will take you to the Data flow tab where you can perform your ETL functions.
4. Drag and drop OLEDB source to Data flow window.
5. Now, configure OLEDB source as per your settings. For this example, I am creating a connection manager to OLEDB source. I have given server name as (local). I am choosing database as AdventureWorks2012. Add this connection manager to OLEDB source.
6. Drag and drop Aggregate Transformation to Dataflow window.
7. Create a connection between OLEDB source and Aggregate Transformation. To create connection, hold green arrow from OLEDB source and drag it onto Aggregate Transformation.
8. To configure Aggregate transformation, double click on it. An Aggregate transformation editor window will pop up asking for settings.
9. Select the required Columns and choose appropriate Operation on that selected column.
10. Finally click on OK button to confirm the end of configuration.
11. Drag and drop OLEDB destination to the Dataflow window.
12. Create a connection between Aggregate transformation and OLEDB destination.
13. Configure OLEDB destination as per your settings.
We are done with the creation of SSIS package which will perform aggregate transformation on data. Now, I am going to run our SSIS package to view the results.
The Aggregate Transformation supports following operations:
GROUP BY, SUM, AVERAGE, COUNT, COUNT DISTINCT, MAXIMUM, MINIMUM
1. SUM: This function Sums up the values present in a column. Only columns with numeric data types can be summed.
2. AVG: This function Returns the average of all the column values present in a column. Only columns with numeric data types can be averaged.
3. MAX: Returns the maximum value present in a group.
4. MIN: Returns the minimum value present in a group.
5. COUNT: Returns the number of items present in a group.
6. COUNT DISTINCT: Returns the number of unique non null values present in a group.
7. GROUP BY: Divides data sets into groups. Columns of any data type can be used for grouping.
Aggregate transformation is a blocked and Asynchronous transformation. With Asynchronous, the Output rows will not be equal to the Input rows. As aggregation is performed on all the column values, it will not release any row until it processes all rows.
1. Create a SSIS package with name “Aggregate1”.
2. In SSDT, drag and drop Data Flow Task (DFT) to control flow window.
3. Double click on Data Flow Task. It will take you to the Data flow tab where you can perform your ETL functions.
4. Drag and drop OLEDB source to Data flow window.
5. Now, configure OLEDB source as per your settings. For this example, I am creating a connection manager to OLEDB source. I have given server name as (local). I am choosing database as AdventureWorks2012. Add this connection manager to OLEDB source.
6. Drag and drop Aggregate Transformation to Dataflow window.
7. Create a connection between OLEDB source and Aggregate Transformation. To create connection, hold green arrow from OLEDB source and drag it onto Aggregate Transformation.
8. To configure Aggregate transformation, double click on it. An Aggregate transformation editor window will pop up asking for settings.
9. Select the required Columns and choose appropriate Operation on that selected column.
10. Finally click on OK button to confirm the end of configuration.
11. Drag and drop OLEDB destination to the Dataflow window.
12. Create a connection between Aggregate transformation and OLEDB destination.
13. Configure OLEDB destination as per your settings.
We are done with the creation of SSIS package which will perform aggregate transformation on data. Now, I am going to run our SSIS package to view the results.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.