Aggregate Transformation in SSIS

21
4196

AimThis post is specifically for those geeks who wants to learn about SSIS Aggregate Transformation. Microsoft provides a vast list of Data Flow transformations in SSIS, and this (aggregate transformation) is one of the most used transformations in SSIS.

DescriptionAggregate transformation is a blocked and Asynchronous transformation. With Asynchronous, I mean to say that Output rows will not be equal to the Input rows. Also, this is categorized as Blocked transformation because aggregation is performed on all the column values. So, it will not release any row until it processes all rows. For more information on Dataflow transformation categories and examples, visit this – Dataflow transformations in SSIS

It will work in the same way as the aggregate functions perform in the SQL server. Generally, we have different types of Aggregate functions (MIN, MAX, SUM, AVG, COUNT and COUNTDISTINCT). The aggregate transformation also provides GROUP BY clause, which you can use to specify groups to aggregate across.

Now we are going to learn the functionality of each function in detail. You can visit, Source – Microsoft’s website to learn more on this.

  1. SUM: Used as SUM(). This function Sums up the values present in a column. Only columns with numeric data types can be summed.
  2. AVG: Used as 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: Used as MAX(). Returns the maximum value present in a group. In contrast to the Transact-SQL MAX function, this operation can be used only with numeric, date, and time data types.
  4. MIN: Used as MIN(). Returns the minimum value present in a group. In contrast to the Transact-SQL MIN function, this operation can be used only with numeric, date, and time data types.
  5. COUNT: Used as COUNT(). Returns the number of items present in a group.
  6. COUNT DISTINCT:Returns the number of unique nonnull values present in a group.
  7. GROUP BY: Used as GROUP BY(). Divides data sets into groups. Columns of any data type can be used for grouping.

The SSIS Aggregate transformation will handle null values in the same way as the SQL Server relational database engine handles. The behavior is defined in the SQL-92 standard. Following are the rules which are applied:

  • In a GROUP BY clause, nulls are treated like other column values. If the grouping column contains more than one null value, the null values are put into a single group.
  • In the COUNT (column name) and COUNT (DISTINCT column name) functions, nulls are ignored and the result excludes rows that contain null values in the named column.
  • In the COUNT (*) function, all rows are counted, including rows with null values.

I am sure this information provided by Microsoft must have helped you to clear your basics. Let’s go ahead to my favorite session i.e. Step by Step Example to show working of Aggregate transformation in SSIS.

Step by Step example on SSIS Aggregate transformation

Step 1. SSIS Package Configuration.

  1. Create a SSIS package with name “Aggregate”.
  2. In your SQL Server Data tool (abbreviated as SSDT is known as BIDS of 2012), 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 i.e. E – Extract data from heterogeneous source, T – Transform it with Data flow transformations and L – Load transformed data to Data warehouse.

Step 2. OLEDB Source Configuration.

  1. Drag and drop OLEDB source to Data flow window.
  2. 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 AdventureWorks2008. Add this connection manager to OLEDB source. I am selecting a table named [Sales].[SalesOrderDetail].
  3. Here, I have not shown you the configuration steps of OLEDB source. I hope you all are very familiar with it. Trust me it is as simple as shopping from flipkart.

Step 3. Aggregate Dataflow Transformation Configuration.

  1. Drag and drop Aggregate transformation to Dataflow window.
  2. Create a connection between OLEDB source and Aggregate transformation. To create connection, hold path (green arrow) from OLEDB source and drag it onto Aggregate transformation.
  3. To configure Aggregate transformation, double click on it. An Aggregate transformation editor window will pop up asking for settings.
  4. Select the required Columns and choose appropriate Operation on that selected column.
  5. In this example, I am going to find total rows in the table by using COUNT function and maximum modified date by using MAX function, the sum of unit price by using SUM function and the minimum line total by using MIN function.
  6. Below screen shot states that I have implemented all the mentioned operations in my aggregate transformation.
  7. Finally click on OK button to confirm the end of configuration for “SSIS Aggregate Transformation”.

Aggregate Transformation editor in SSIS

Step 4. OLEDB Destination Configuration.

  1. Drag and drop OLEDB destination to the Dataflow window.
  2. Create a connection between Aggregate transformation and OLEDB destination.
  3. Configure OLEDB destination as per your settings.
  4. I am attaching Data viewer to view the output while executing the SSI package.
  5. Finally it will look like picture shown below.

Note :- Create a “Data Viewer” between Aggregate transformation and OLEDB destination for observing the flow of data as well as Output data too.

SSIS Aggregate data flow transformation

Kudos!!! 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.

output of SSIS Aggregate transformation Data viewer

ConclusionBy observing about screen shot, we can conclude our SSIS aggregate transformation operations on given table ([Sales].[SalesOrderDetails]) is performed successfully. Final output is – One single row with Four columns. Below is the complete analysis of this result.

  • First column tells us the total count of rows in given table.
  • Second column is showing the maximum Modified date.
  • Third column is showing the sum of Unit price.
  • Fourth column gives the minimum Line total.

With this we come to an end of article on Aggregate transformation in SSIS. I hope you all must have enjoyed this step by step example on aggregate transformation. Please drop/leave your valuable feedback below as comments. It is highly appreciated.

 

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.2/10 (31 votes cast)
VN:F [1.9.22_1171]
Rating: +23 (from 25 votes)
Aggregate Transformation in SSIS, 9.2 out of 10 based on 31 ratings

21 COMMENTS

  1. HI Avinash,
    you are doing great job in SSIS. Really its appreciated. Can you explain Aggregate Advanced options like Keys and Key scale, Count distinct and count distinct keys, Auto extend factor. I know that aggregate transformation may decrease the performance of a package. How can we upgrade the performance by using aggregate transformation.

    • Hi,

      Thanks a ton for appriciating my efforts. Happy to see!!

      coming to your question….I can write one more article on them on tomorrow.
      Yep…Agg transfermation can degrade the performace..solution is better to aggregate the data in SQL server it self by using queries rather than going for aggregate transfermation.

      I hope i answered all of your question.

      Let me know if i missed anything.

      Thanks,
      Avi

  2. Hi! I Know this is an old post, but I wonder if you know how to use this transformation step to obtain 2 different aggregations in the same output. This is the problem: I have sales and inventory information in the same table, in a daily basis, one record per day, product and store. I want to summarize the data in a weekly basis, so I can group by week, product and store, then sum the sales, but the inventory is not a sum nor an average, I need the last non null value for each product and store.

    Hope you can help me… any kind of help is very appreciated.
    Best Regards!

    • Hi Mounika,
      Your feedback matters a lot and will put in more efforts to make our valuable visitors experience better. Thanks for the Compliment !!!

      Thanks,
      Chander Sharma

Comments are closed.