Step by Step SSIS–Aggregate Transformation TIP #118

 

Dear Friends,

In the series of step by step SSIS tutorial this is another post. In this post we will see Aggregate Transformation. I am pretty much sure you are aware of aggregation. Although , Just wanted to share that aggregation operation in generally memory expensive operation.

So, whenever you want aggregation & want to use group by function then in such situation you can use Aggregation transformation.

There are different group by option available like MIN, MAX , COUNT, SUM, AVERAGE etc.

Let’s understand how to use Aggregate transformation step by step.

For current example we are using Adventureworks database and we are using below query. Here we are fetching the product data with line total,unit price  & other details.

if you see the records in the table you will find that there are multiple records for same product with different line total.

Our objective is to aggregate or do sum of Line total according to Line Number and export the result in a csv.

Step 1:- So , Now start with package creation add a new package in solution and drag drop source Assistant and configure the database connection as we did earlier in the tutorials

Step 1.1 – Drag drop  Data flow task

Step 1.2 – double click data flow task  and drag drop source assistance control

Step 1.3 – Configure source assistance

Step 2: Once the source assistance is configured with SQL SERVER connection string and specific query we will drag drop Aggregate Transformation control as shown in below figure

Step 3:- Now configure this aggregate control. So Just right click aggregate control and select Edit option. Now as we require Sum of LineTotal so we have selected SUM in operation column’s drop down and rest other has drop down option group by.

 

Step 4:-

Now, drag drop flat file destination and configure it.

Now configure flat file  with mapping as shown in below figures

Step 5: Now run it you will get desire result as shown in below figure

If you see in the result we got 757 rows after processing 121,317

Now see the result in actual as shown in below file

I hope this example might help you to understand Aggregate transformation. Please provide your inputs.

Enjoy !!

RJ!!!

Grouping Sets–Good to know feature TIP# 60

 

Grouping sets is one of the cool feature came in SQL SERVER 2008. Lets understand here with problem and solution.

Problem:-  Suppose , We want  an aggregation result in a query with different groups. Firstly we want aggregated result on first column then combination of First & second column then other column combination.

So, to resolve this problem a basic traditional way is to create 3 separate query and combine there result.

Solution:-  Now in SQL Server 2008 onwards we have a new feature for to achieve such problem which is called GROUPING SETS.

Lets understand this by an example.

I am taking here Adventureworks2012 database. Now we want total due amount on different basis  example

1) total due amount on Territory name and sales person basis

2) Total due amount on  Territory name

3) total due amount on sales person basis

4) total due amount on sales order date  basis

To achieve above  results we write following query

SELECT sod.OrderDate,
st.Name,
p.LastName + ','+ p.FirstName  As SalesMan,
SUM(sod.TotalDue) as totalDue
FROM [Sales].[SalesOrderHeader] sod
INNER JOIN [Sales].[SalesPerson] sp ON sp.BusinessEntityID = sod.SalesPersonID
INNER JOIN [HumanResources].[Employee] emp ON emp.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [Person].[Person] p ON p.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [sales].SalesTerritory st ON st.TerritoryID = sod.TerritoryID
GROUP BY GROUPING SETS (
   (st.Name,p.LastName + ',' +p.FirstName ),
   (st.Name),
   (p.LastName + ',' +p.FirstName ),
   (sod.OrderDate)
  
)
ORDER BY  st.Name,sod.OrderDate

Now when we run the query and we get results which we want.

I hope this may be help you some where.

Thanks !!!

RJ!!!