Why Do People Think Learn Step by Step PIVOT Transformation is a Good Idea ? TIP #125

In the series of step by step SSIS learning, this is another interesting article. In this article we will try to implement PIVOT transformation in few easy steps.

Let’s understand the PIVOT by below example. Suppose, you have a salary data of employee as shown below.

Employee Name Salary($) Year
Sandeep 110000 2016
Sunil 120000 2016
Shreya 130000 2015
Virendra 140000 2015
Sandeep 120000 2014
Sunil 130000 2014

Now, if our requirement is something like below table then we would require PIVOT functionality.

Employee Name 2016 2015 2014
Sandeep 110000 0 120000
Sunil 120000 0 130000
Shreya 0 130000 0
Virendra 0 140000 0

In general, we say when we want to convert Rows data to Columns then this is called Pivoting.

Now, lets create a sample of PIVOT transformation with few basic steps. In this example, we are again using AdventureWorks database. EmployeePayHistory, Employee, People tables are base of our example. We wrote query which fetch data similar as shown above.

4

Let’s move step by step

Step 1: Add a dtsx package and drag drop data flow task as show in below figure.

1

Step 2:- Now, double click on Data flow task and drag drop data source and select SQL Data source and configure the SQL statement as shown below.

2

3

Step 3:- Now, We have to drag drop PIVOT transformation control as shown below.

5

Step 4:- Now, we have to configure the  PIVOT control. When you choose edit option then you will get the following screen.

Here, we have to choose Pivot Key, Pivot Value and Set Key.  In the Pivot Value we use the value which we have to show in the columns.

 

15

If you see pivoted output columns highlighted in yellow in above image is the year wise columns. if you are sure for the columns which you want as a pivot columns then you can add the name of the columns in place of [value1],[value2] etc. and click on Generate Columns Now button. (In current case we have limited years so we have added [1997] to [2004] values in this textbox and clicked on Generated Columns Now button due to which we got yellow required Pivot output columns as highlighted yellow.

Step 5:- Our configuration is not yet complete to configure this we have to click on advance setting and select Input and output properties.

Here, if you want to add any extra column as an output column which exist in Input columns  then you have to select Pivot Default Output and click on add column. Now, the main important point if we want to map it with existing input source column then in such case we have to add LineageId of that particular column.(AS highlighted in below images)

7

8

Step 6:- The another most important point is PivotUsage which is highlighted below. If we want to add columns which are not affecting the pivot then we have to update the value of PivotUsage to 0.

9

Step 7:- Once the Pivot is configure the next step is to provide the output of pivot to a destination. As a destination we are using Excel file as usual Smile.

So, drag drop excel destination source and configure it as shown in below figure .I hope you will not face any issue in this. If you are new and facing any difficulties then please visit previous articles of Zero to Hero in SSIS Series

10

11

Step 8 :- Once the configuration is done we will see below screen.

12

Step 9:- Now, once everything is completed then we run the package and we will get following screen.

13

Now to cross check we open the excel and cross check the data. When we open the excel, we found desire result.

14 

So, We have achieved what we want. We did our first Pivot Transformation example.

Please, feel to drop your comment.

Enjoy !!!

RJ!!!

The Unconventional Guide to How to Implement Lookup Transformations TIP #124

This is one the articles in the series of step by step SSIS tutorial. In this post, we are going to understand Lookup transformation and also see an example to implement Lookup transformation in 8 easy steps.

Now, Lookup transformation is one of the interesting transformation which is used to implement equijoin between data sources.
We are discussing the same example which we discuss in the earlier post which is Person & PersonPone tables of AdventureWorks.

Suppose, we want Person details and the phone number of person as a result then we can apply Lookup transformation.

When, we applied the Lookup transformation between Person & Person Phone table then we will get two result set.

One of the result sets is the Matched result set in which we will get the Person data and the First Matched Phone number while the PersonPhone table might have multiple phones for a person but we will get only one phone number record because Lookup transformation uses EquiJoin.

In the second result set, we will get unmatched Person records which mean the Person who doesn’t has a Phone number in PersonPhone table.

I think, we have a basic understanding of what is Lookup Transformation. Now, we are going to implement Lookup Transformation step by step.

Step 1:-
So, the first basic step is to add a package and drag-drop data flow task as shown in below figure. You can rename the  data flow task  as I did in below figure.

1_DataSource

Step 2: – In this step drag drop a data source and configure it for Person table as shown in below figure. If you face any difficulties in this then you can review previous articles of this series

2_SqlSource

Step 3:- Once the Person table is configured just drag drop Lookup Transformation control and provide the input of Person table Data source to Lookup table. When you try to configure the Lookup you will get the following screen. We will discuss Cache mode separately in detail. In this example, we are using Full Cache and connection type to OLEDB connection Manager.

Now, Click on Connection option on the right and configure the PersonPhone table. 
4_Lookup

5_LookupTransformationColumn

Step 4:- Now, configure the Columns relationship between Person & PersonPhone table.To achieve this just drag-drop BusinessEntityId of available input columns of Person table to available Lookup columns which are in PersonPhone table and matched with BusinessEntityId which is common between Person & PersonPhone table. Here we have to select PhoneNumber column which we have to include in the result set.

4_Lookup_111

Step 5:- Now, Drag drop two excel destination source and configure it. As, I did in below figures. Here one Excel is with name LookupOutput.xls which contain matched records.

7_ExcelOutput

Below, is another Excel which contains unmatched records. Here UnMatched means the Person records who don’t have phone numbers.

8_Lookupoutputnotmatched

Step 6:- Now, If you see there are 3 output lines coming from Lookup transformation.So, First blue output gives it to Lookupoutput excel which will have all the matched records of person & Person Phone number. You can configure inputs as shown below.

9_Matched_Excel

Step 7:- In a similar way, we have to configure unmatched record output. Once, we done with configuration of both Matched & unmatched output, we will get following structure as shown in below screen

10_NotMatched_Excel

10_Final

Step 8:-  Once, everything is configured you can run the package and if everything is perfect then you will get the following screen

finalRun

I hope above steps are easy and you can implement Lookup Transformation at your end too.

Please, provide your inputs.

Enjoy !!!

RJ!!!

How to do Step by Step Merge Join Transformations – #123

 

In last post tip #122, We discussed Merge transformation. Now in this tip, we are going to discuss Merge Join. Although, it might be confusing sometimes Merge & Merge Join. So, let me explain it here.

As explain earlier merge is like a union operation in which it just collect all the records from provided sources without any condition. suppose if we have Person & PersonPhones two sources as shown below

Below is Person table with two columns personId & Name

PersonId Name
1 Ram
2 Shyam
3 Ghanshyam

Below is PersonPhones table with two columns PersonId & Phone

PersonId Phone
1 9999
2 3122
3 3422

Now if we talk about merge transformation then the result would be as shown below

Merge Transformation output

PersonId Name Phone
1 Ram  
2 Shyam  
3 Ghanshyam  
1   9999
2   3122
3   3422

Now, If we talk about Merge Join transformation for above same inputs (Person, PersonPhones) then we will get following output

PersonId Name Phone
1 Ram 9999
2 Shyam 3122
3 GhanShyam 3422

So, Merge Join is basically a join like Inner join, Left outer join, Full outer join etc.

I hope you got the context. Here we go step by step to implement the Merge join transformation

Step 1:-  Add the different sources which you want. For this example I am taking two sources which have AdventureWorks database and one source have Person table & another one have PersonPhone as shown above in the example.

We added data flow task as shown in below image and renamed it to Merge join data flow task.

DataflowTaskStep1

Now, in this data flow task, we will add data sources, in the first source we use person table and in the second source we use PersonPhone.

from person table, we are choosing BusinessEntityId, FirstName,MiddleName, LastName,suffix as shown in below figure

MergeSource1Step2 

In the other source, we are using BusinessEntityId & Phone number columns.

MergeSource2Step3

If you find any issue in above steps then please, follow previous articles of step by step SSIS on Indiandotnet.

Step 2:- Now once the source is configured, we are going to drag drop merge control as shown in below figure.

DragDropMergejoinStep4

Step 3:- Now to configure Merge join, we are going to drag drop inputs from the sources. When you try this at your end then you will get the following screen. Here you have to set left input and right input for the merge join.

ConfigureMergeJoin4

Now, When you tried it you might found following warning. Which means the provided inputs of datasource1 & datasource2 are not sorted.

WarningforSortStep5

Step 4:- Here you have the option to add a Sort transformation and then provide input to merge join transformation control which is fairly simple and we have discussed in previous tips. Here, I am sharing one more option.

In this, you have to right click on DataSource and choose option Advance Editor by which you will get the following screen. You have to select “OLEDB Source output” of “Input and output Properties” tab.

Here, we need to make the IsSorted property to True as highlighted in below snap.

IsSortedProblem6

Step 5:- Once the above Step is done next step is click on output column’s BusinessEntityId value. Here you have to set the SortKeyPosition to 1.

As highlighted in below screen.

BusinessEntitySortKEy

Step 6:- Once you configured sorted input sources then next step is to configure the merge join. So when you click on Edit option of merge join. You will get the following screen.

Here if you see, we can configure join type like Inner join, Left outer join, Full outer join.

For current example, we are choosing the option “Inner join”  and joining key is BusinessEntityId which is primary key in Person table and foreign key in PersonPhone table.

ConfigureMergejoinTypesStep6

Once the configuration is done. We can select what are the columns which we need as an output.

Step 7:- Now, Next step is to configure the output in excel. for this, we drag & drop a destination source. In the current example, we are using Excel as a destination. We are mapping the columns of output to excel as shown in below figures.

ConfigureExcelDestination

MappingInexecel

Step 8:- Once, everything is configured. We run the package and if everything is working fine then we will get the following screen.

MergeJoinRunResult

To cross check the result we open the excel and we get the output which we expected as shown in below figure.

ResultOutputinExcel

I hope this article might help you to understand the Merge join.

Enjoy !!!

RJ!!

Step by Step Merge transformation control in SSIS TIP #122

In the Series of Zero to hero in SSIS  this is another post. In this post we are going to discuss Merge transformation control.

By the name it is clear that Merge transformation do some merge related task. For example suppose there is two sources which are source1

and source2. Now we want merge records of Source1 & Source2 then this transformation is helpful.

Although, sometimes you might confuse that there is already UNION ALL transformation the when to use UNION ALL or Merge transformation.

For Merge transformation we require sorted inputs and there is only two inputs required. The output of merge transformation will be sorted output.

Let’s start it Step by Step

Step 1:- Firstly configure the sources. In current example I am using AdventureWorks2008 database’s person table and using FirstName, LastName, MiddleName columns as a Source1. For Source2  I am using AdventureWorks2012 database’s person table and using FirstName, LastName, MiddleName again. (You can choose any different source like flat file, excel ,other data source provider as well to make it simple I am  using same source)

source1

Step 2:- Once you setup your data source the next mandatory condition is the output should be sorted. So here we are using Sort transformation control. Now for first source we are sorting data on FirstName,MiddleName, LastName.

Sort1

and for second source we are sorting data on FirstName , LastName ,MiddleName

Sort2

Step 3: Now once we have two sorted input we are going to use Merge transformation control so just drag drop and configure it. As shown in below figure. So there are two inputs which is Merge Input 1 & Merge Input 2 and we configured the data according to our need. And if you see below screen you will find “Configure the properties used to merge two sorted inputs into one output” which means for Merge transformation we require two sorted inputs only.

Merge_configuration

Step 4:- Now,we want the output of merge transformation in an excel so, here dropped Excel destination and configure it.So  you can see below configured package with excel destination.

Merge_Final_dtsx

Step 5:- Now just hit F5 or run it. If everything configured properly then you will get all the green checks with no of rows count at each level as shown in below figure

Merge_dtsx_Result

Step 6:- We can cross check Excel also so in my case we got the required data as shown in below Excel’s snapshot.

Merge_Result

So, Isn’t it simple ?

Here the important points to remember

1) Merge requires Sorted inputs only

2) Merge requires only two inputs

I hope this tutorial might help you. I appreciate your feedback.

Enjoy !!

RJ!!

Step by Step SSIS Multicast Transformation TIP #121

Dear Friends,

This is another post in the series of step by step SSIS ,Zero to hero series. In this post we will discuss another transformation control which is Multicast transformation.

Multicast is a way which help us to provide multiple copy of same source data for different transformation. Or in other words we can say multicast transformation create multiple pipelines for the same data.

Lets understand this by an example. Suppose, we are product developer and selling product in different state in India. Now ,the problem is same product may have different selling price due to different tax system. So, we have to use same data of product but there will be different discount according to sate tax.

Now, here we go step by step. for your information in this example we are going to use similar example which we did in last post of Derived column Transformation tip #120

Step 1:- Add a package and drag drop data flow task and configure source database. I am taking Adventurework2012 database and using product &  productCategory tables for source data query below is simple snap of data

Data1

Step 2:- Once the data is configured. Drag drop Multicast control  as shown in below figure and give output of source object to Multicast and try to configure it.

DragDropMulticast

Step 3:- Now, when we have configured Multicast we can get multiple output from Multicast control. Suppose , One state is taking 10% WAT tax, another state taking 20% WAT tax and last one is taking no tax (wow that’s great). So we can drag drop derived column and configure then according to 10% ,20% WAT tax for reference you can take a look of tip #120 

I configured the the 3 different output in same way and adding the respective flat files. As shown in below figure you will see we added 3 files

1) with 10% ,with 20 % and without and tax

FileDestination

After configuration you will get structure something like as shown in below image. As a result we will get 3 flat files with 10% ,20% and without any change.

finalDesign

Step 4:- If you face any difficulty in Derived column configuration and moving the data to file please follow earlier post. Now once everything is configured run the package by pressing F5 or hitting run button you will get following screen. So , if you see Multicast providing similar row count to different output which is 295 rows.

Final_Result

We , can use this Multicast where we need multiple copy of same source data.

I hope this article might help you somewhere.

Enjoy !!!

RJ !!

Step by Step SSIS–Derived Column Transformation TIP # 120

Dear Friends,

In the series of Step by Step SSIS , Zero to Hero in SSIS this is another post in which we will try to understand what is Derived Column Transformation.

Let’s understand this by a real world example. Suppose, we are a shop keeper and our work is buy things from carpenter and sell them by adding our 20%  margin.

So , carpenters give us source data (file, sql server etc) with their rate according to product. What we do actually ,we add another column in our register by adding 20% which is our selling price. So ,our selling price column is a derived column here which we derived from source by adding some of our operations.

Isn’t it simple ?

Let’s understand this now step by step. I am using Adventureworks2012 database here as a source database and using product & ProductCategory table combination query. So , Assume this source data is data of carpenter’s products.  Here if you see below image we have productId, Product Name, color,Category & ListPrice (price at which we (Shop Keeper bought the the product)

Data1

Step 1:- It is useless to say here add a new Package in your project. Drag drop data flow task on canvas. Now double click the data flow task you will get a new screen where you need to drag drop source Assistance.

Now,need to configure source assistance where database will be adveturework2012.  If you see below we have same columns which we shared earlier in figure

SourceColumn

Step 2:- I am sure you will not face any problem in configuration of source control. Now in step 2 we have to drag drop Derived column.  Once you drag drop derived column you need to provide output of source assistance to Derived Column Transformation. Now to configure this use context menu by right clicking and choose Edit option you will get below screen. Here we have different functions and operators which we can be utilized with columns and variables and parameters to create derived column.

DerivedColumnSettings

If you see above highlighted row in image we can add a new column or can replace any existing column as shown in drop down.

Step 3:- If you see there is expression column also where we can add custom expression. Here we are multiplying 0.20 in list price and adding it again in Listprice and aliasing this new column SellingRate as shown in below figure

Expression

if you see below screenshot we can add other functions ,operators also.

VariousOpeartors

Step 4:- I hope above steps are pretty much clear. Now add a destination file in which we will get derived column “SellingRate” . We have to configured the derived column. As shown in below figure we configured derived column.

SellingRatemapping

Step 5:- Now, Once the destination file is configured run the package by pressing F5 or clicking run option. If everything working fine then we will get all the green checkbox sign as shown below figure

FinalResult

I hope this post might help you to understand the derived column.

Enjoy !!!

RJ!!!

Step by Step SSIS–Union ALL Tip # 119

 

Dear All,

In the series of  zero to hero SSIS series this is one of the simplest transformation control which known as UNION ALL. 

As you might aware UNION in general term  which means collective. In SSIS UNION all control is doing the same task.

It collect all the inputs may be of same type of sources or different type of sources and  union them all and provides single output for the same.

Lets understand this by below example.

Suppose, We have different text files which contain fruits &  vegetables name. Now our aim is to combine all the fruits and vegetables name which exists in these files.

if you see below image you will find 3 files which FruitA (Contains fruit names start with letter A) , FruitB (Contain fruit names start with  letter B), FruitP (contains fruit name start with letter P)

Files Now we need to combine this file using UNION ALL transformation. So follow below step by step

1) Step 1:- Step 1 is simplest step and now you all well versed in this. We need to add a new SSIS package file and drag drop  data flow task .

2) Step 2:- Now add different flat file source for all the 3 files and configure all the 3 files  fruitA.txt, fruitB.txt , fruitP.txt .

3) Once we configured all the 3 flat file sources, We have to drag drop UNION ALL transformation control.Once we drag drop UNION ALL we can provide the output of all the 3 files as a input in UNION ALL control as shown in below  figure.

source

Step 4:- Now, we have to configure the UNION ALL control. To do this right click on UNION ALL control and click on EDIT option. Here we have to configure all the columns which we need to union.

ConfigureUnionAll

Step 5:- Once the UNION ALL is configured our next step is to get the output of UNION ALL in a resultant file. for this we have to drag drop destination flat file control as shown in below figure.

file

Step 6:- Now we have to configure the flat file destination and save it to specific location. A part from this we have to provide input to flat file destination which will be output of UNION ALL control. As shown in below figure I am saving the file on same location and giving name finaloutput.txt

finaloutput

Step 7:- Once all the above step is processed by us the final step is to run the package for this just click on RUN icon or hit F5. If everything is working fine the we will get following  result with all the green right check images.

finaloutputREsult

If you see above image we did union of all the 3 files and saved output in a single file.

Now, for learning purpose and to make the post simplest I use only same type source you can use different type of source and destination as per your need.

A part from this UNION ALL doesn’t remove duplicate so if there are duplicates in the files it will not remove.

I hope this article will help you somewhere.  Please provide your inputs.

Enjoy !!!

RJ!!!

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.

Data

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

Step2

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.

 

Step3

Step 4:-

Now, drag drop flat file destination and configure it.

Step4

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

Result

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

File

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

Enjoy !!

RJ!!!

Abstract Classes & Interfaces

Abstract class or Interface : the most confusing decision in the programming world when to go with which. Let us understand each one in detail and try to answer this question.

OK so let’s discuss about them one by one.

Abstract class:
An abstract class, just like any other class is a collection of fields, properties and methods. It has got behavior of its own. The only difference is that an abstract class cannot be instantiated; and of course the use of the keyword “abstract” in the class declaration.
It can have methods that contain a method body or even ones that contain only declaration (abstract methods).

AbstractClassBasicDefinition

Now, coming over to some points we all get confused about. The “Myths and Truths” of the abstract class –

Myth : Abstract class must contain at least one abstract method.
Truth : Abstract classes need not have any abstract methods. But a class containing even one abstract method must be declared abstract.
Now the question arises what is the use of such a class? Such an abstract class would just prevent a user from instantiating the given abstract class, while providing a default implementation for all child classes.

AbstractClassWithNoAbstractMethods

Myth : Abstract class cannot be instantiated and hence cannot contain a constructor..
Truth : Abstract classes cannot be instantiated yet it can contain a constructor.
Once again, the question arises what is the use of having constructor if it cannot be instantiated?
This is so because abstract classes are based upon the concept of inheritance and in C#, whenever an object of a derived class is instantiated, prior to calling the constructor of the child class, the constructor of super-class is called and this is where a constructor of abstract class comes in use.

ConstructorInAbstractClass
On instantiating the object of the derived class (shown in the above figure) , the output is :

ObjectInstantitationOutput

So this was all about abstract classes. We would be discussing interfaces, their differences and which to use when in the following posts………..

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.

Query1

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.

Step1

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

step2

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.

step3

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

step4

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

step5

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.

step6

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.

Step7

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

step10

 

Step9

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

step8

Step 8: In nutshell we will get following screen

step11

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.

Step12

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

BikeCategories

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

RJ!!!