Friday, October 02, 2015

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

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

Step 1.1

Step 1.3 – Configure source assistance

Step 1.2

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 4.1

Step 4.2

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


Monday, September 21, 2015

Step by Step SSIS - Conditional Split Transformation TIP #117

In the series of Zero to Hero in SSIS this is our next post. In this post we will see Conditional Split transformation.

I am pretty much sure that by the name you got some impression what it would be.

So, a Conditional Split Transformation is a way by which you can conditionally split an input into multiple output.

Suppose, You are a food supplier and you cook both veg & non veg food. Now according to orders by client you need to move those order requests to particular kitchen to prepare those orders.

Let’s understand it by following step by step execution example.

In this example , I am using AdventureWorks database as a source database connection. We are fetching products along with categories. We need Bikes products in different file and rest other product in different file. You can see products with categories shown below in this image Accessories , bikes are the categories.


Step 1:- Create a  new package and drag drop data flow task control and double click it. You will get a new screen which data flow task. Now drag drop Source Assistance. from SSIS controls tool bar.


Now as usual we have to configure the database connection string and set the database to adventureworks.


Step 2:- Once the database is configured right click on control and choose edit property. You will get below screen where you need to specify the query which we shown earlier in figure.


Once you done with above steps you need to configure columns as well so, click on Columns and configure it.


Step 3:- Now drag drop conditional split and connect input arrow to it as shown in figure


Step 4:- Now choose edit option by right clicking the conditional split and configure it.  When you click you will get screen like below. You will find different condition operator. One important point to remember here you need to drag drop column name to condition textbox.  As our aim is to have product with Bikes category in a different file and other categories in different file.So we use following condition as shown in below figure.


Step 5:-Now drag drop a flat file destination and assign the output of the above conditional split to File destination. You will get following screen. As you see we are assigning Bike condition output to this file which means all the products which belongs to bikes categories should be available in this.


Step 6: Now Configure the flat file destination by choosing the edit option.




Step 7:- In similar way we have to capture the output which not belongs to Bikes category as shown in below figure


Step 8: In nutshell we will get following screen


We almost done here. Now to check whether this conditional split is working or not. To check this hit F5 or run the package. So, if you see below everything working fine. We got 32 rows in bike category and 73 rows in other categories.


To cross check result we will see the file output as well


So , If you  go through all the above steps we have achieved Conditional split example.

I hope this post might help you to understand conditional split.

In next step we will go for next step in SSIS.

Enjoy !!!