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

Advertisements

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

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

Step by Step SSIS learning What Data Conversion use in SSIS ? TIP#116

Dear Friends,

In the series of step by step learning of SSIS this is part #6 in which we are going to learn a new control Data Conversion.  I am sure by the name it clear that this control will be use when we require data type conversion of input columns then we need this control. This is exactly same like CONVERT  or CAST  in our programming.

Although, Here it would be important to share SSIS also have data type which are similar to other programming or database’s data types

Like DT_NUMERIC is equivalent to numeric data type, DT_I2 is equivalent to smallint, DT_str is equivalent to VARCHAR and so on.

I am sure you will find this control fairly simple so without wasting much time lets start step by step.

For a change I am using excel data source here.

Suppose, we have an excel of sales order which have columns like sales order, subtotal,Tax amount, and freight.

SaleOrderExcel

Here sales order is varchar, while SubTotal , Tax amount & Freight are numeric with decimal 4 places. We want to convert the numeric field in integer and want result in a flat file. so let’s start.

Step1 :- Drag drop a Data flow Task control and double click it. Now add an excel data source from SSIS tool bar as shown in below figure.

ExcelSource

Step 2:- Now next step is to configure this excel source. so to do this just right click and use EDIT option. You will get a screen where you need to provide the file which we want to access.

ExcelSourceStep2

Once the connection with excel is established the Next very important step is to configure sheet as highlighted in below figure. remember you need to configure this sheet because an excel may have number of sheets available so you need to assign proper sheet whose data you want to process.

ExcelSource3

Step 3: Once the sheet is configured you can choose the columns as well which you want to process or want in destination by click column’s option in as shown in below figure

ExcelSourceColumn

Step 4:- Now drag drop the data conversion control from SSIS toolbar

dataConvrsionControl

Step 5:- Now , open context menu and click edit button to configure data conversion. Now remember here we have to change data from numeric to integer (As discussed earlier our aim). so just change the desire data type which is integer (DT_I4) of all the three numeric data type. As shown in below figure. Also , point to remember here if when you do data type conversion you need to careful about Aliasing also. You will see copy of as a prefix on column.

Convesion

Step 6:- Now, we need this output in flat file so adding a file destination control from SSIS toolbox and configuring it by clicking Edit button as shown in below figure.

FlatFile

Step 7:- In next step we have to configure the file location and columns which we require in the flat file. And delete unnecessary columns which are not required.

deleteAccesscolumns

dataConvesionmapping

Step 8 : Once the above step is done, we are good to go to run this package. So,hold your breath for few second and hit F5 Smile .

You will get find below screen. Which shows that all the provided rows are inserted in file. 

result

Step 9 : To cross check whether file has integer columns or not lets open it.

CSVREsult

So, if you see above screen we did it successfully. So we achieved our goal in this post and used DATA conversion control successfully.

I hope you feel this post useful.

Thanks for reading this post.

Enjoy !!!

RJ

How to use SORT control in SSIS ? TIP #115

Dear Friends,

In the series of Learn SSIS step by step this is the 5th post. Now from this post we are going to use each transformation control one by one.

So, lets start with simplest one transformation control which is “Sort”.

By the name it is clear data it will sort the data which is provided to it and give sorted output as a result. Lets understand this by below step by step example. (Here I am not writing step to start visual studio and create a new project, I am pretty much sure you are aware of this now.)

Before going further let me tell you want we are going to do  here. We will have a input result (which will be a text file) and then sort it and save in another file.

Step 1:-I renamed package to SortPackage if you want you can rename your package as well. Now drag drop a Data Flow task as shown below and double click DATA flow task

Step1

Step 2:- When you click it you will get a data flow screen where you can drag drop FLAT file source. You can choose source assistance as well. Now configure this flat file source. Means give the path of the file which you want to have as a input source. I am taking a text file which contain fruits name in different order. as shown in below figure

Step3

Right click flat file source click on EDIT option and follow the screens

Step4

Step 3:- Now once the file is configure. We have to drag drop SORT control from SSIS tool box as shown in below figure.

Step5

I also added the output of flat file source to sort control. As shown in above figure. Now configure it. Click on Sort control and you will get following screen. As show in below screen you can sort the data on any column and in any direction like Ascending or descending.

Right there is only one input column which is name so we are sorting name in Ascending order as shown below.

Step6

Step 4:- Now once this configuration is done we will save the data in new file with new name which will be sortedFruits. Now to achieve this drag drop the destination control. So I took same float file destination as shown in below figure

Step7

Step 5:- Now configure this file destination. Which means where we need to save this file and what are the columns which we required. so In current case there is only one column which is Name (Fruit name) and I am saving this file at same place with sortFruits name. so lets configure it by clicking right click on flat file destination and click on edit button

step8

Step 6:- Once this is configured we need to run this package by pressing F5.When you run hit F5 and everything going right then in execution screen at each step you will find green right check image as shown in below screen

Step1.JPG9

Step 7: Now to cross check we will first see whether that file is created or not. If created then whether we have sorted data or not. So lets open the flat files.

result

So, if you see at provided destination location a file is created and the data inside this file is sorted file.

I hope you like this first transformation control. till then Enjoy!!!

Thanks !!

RJ!!

How to deploy SSIS Package ? Step by Step SSIS TIP #114

Dear Friends,
In last post we have successfully created our fist basic package. So, Now the next thing is how to deploy this package.

We have created this package for someone else or our client so we need to run this package on his /her machine.

For this we need to know how to deploy. Lets start this step by step 

Step 1:- Open the existing solution and right click on the solution you will get following options as shown in figure. You need to click on “Convert to Package Deployment Model”   as highlighted below

convert_To_Package

Step 2:- Once you click it you might get popup for confirmation just click OK.

Package_model_cofirmation

Step 3: Now click on Properties you will get following screen in which you need to select the deployment option. And need to select true value for CreateDeploymentUtility as highlighted below in figure. Once You make this option true  press OK or Apply Button.

Properties 

Step 4:- Once you done with this again right click on the solution and again click on Covert to Package deployment model.  Now this time you will get Wizard screen as shown below. Click on Next button

Convert_Wizard_1

Step 5:- You will get below screen which is package selection screen. (Suppose you have multiple packages in a solution so for which package you want to create deployment you need to check / Uncheck according to your need.) Once you selected the Package click on Next button.

Wizard2

Step 6:- When you click next button you will get next option which is specific project properties as shown below in screen. In this we have an important option which is Protection level. It is use to set the level of showing sensitive data in package. for example a package may contain sql server connection string in which we have username and password and username and password are very important and sensitive information. Here ,with protection level we have option to encrypt this sensitive data or make package password protected. This will be an interesting topic which we will discuss separately in near future in separate post. for current time being just leave the default option as is and click on next button.

wizard3

Step 7:- When you click on next button you will get following screen. This is the screen where you can call another package which this deployment. We will discuss it later for current deployment process we don’t want to call any other package.  Just click on next button.

wizard4

Step 8:- When you click on next button you will get following screen which says about configurations. We can create a configuration file which is just a simple XML file. If you are a .NET developer just assume it web.config file where we can keep connection string or other configurable item.

wizard5

Step 9:- We can create parameters which can be use to pass the values in packages. In current solution we have not configured even parameters so don’t think to much about this currently. We will discuss it separately in coming post.

wizard6

Step 10:- Now directly jump to Perform Conversion and skip review step. and click next you will get following screen of popup. Press OK and then try to rebuild the solution.

wizard10

Step 11:- We did  a build/rebuild to cross check package is correct. Now open the deployment folder in bin folder (You can find the address in step 3’s screen) . When you open the folder you will get two files one is Your package file with DTSX extension and another one which is called manifest file as shown in the figure below. Below ExportSQLToFlatFile is our manifest file  and Package is our actual DTSX package file. (if we have config file then you will find config file as well)

Package_Manifest_Location

Step 12:- Now, we successfully completed our first part of deployment which is basically creating a package deployment file which is a manifest file. We can copy these files on any server where we need to deploy the package. When you click on Manifest file which is ExportSQLToFlatFile you will get following screen

Actual_Deployment_1

Step 13: Click on Next button when you get above screen you will get following screen which shows 2 options of package deployment

1) File System deployment 2)  SQL Server deployment

Now for current demo we are using file system deployment and clicking next button.

Actual_Deployment_2

Step 14:- When we click next button we get the screen which ask for folder where package will be deploy by default it is SQL SERVER’s DTS package folder so let it be currently and click Next button

Actual_Deployment_3

Step 15:- After clicking next button , we will get following screen. Which is a confirmation screen for deployment.

Actual_Deployment_4

Step 16:- Now the last step click the finish button and we are good to go.

Actual_Deployment_5

So all the above steps are for creating a deployment for package and then deploy in SQL SERVER. Now , I am sure your next question will be what now ?

So, now we call this Package in SQL SERVER Job and configure job which execute this package time to time.

We, will discuss it more in detail coming post.

I hope you will like this post. please do post your inputs.

Thanks

Raj

WWH in SSIS ? Zero to Hero in SSIS series TIP#111

Dear Friends,

As shared in last post Post #110, We are starting SSIS tutorial series ( A Step by Step  SSIS learning tutorial) from today.

Before starting anything we might have some questions like What is SSIS ? Why SSIS ? & How to use SSIS ?

In general term I called this “WWH” What, why  How ?

So, the straight forward answer is SSIS is SQL SERVER Integration Service (a Microsoft tool). It generally comes with SQL Server license. Although It is not always mandatory to have this SSIS tool with SQL Server license, You can install it standalone as well. SSIS is a ETL (Extract Transform loading) tool. We can can use SSIS with IDE which provided by Microsoft.

Now, I understand this is too straight forward and we are not able to digest easily. Let me explain it with an example.

I am leaving in Indore a city reside in Madhaya pradesh a state in the heart  of India. Suppose the Chief Minister of MP allocated some funds to Municipal corporation of  each cities like Indore, Ujjain, Bhopal etc. to do whatever best they can do with money and make their city a better place.

After few years The Chief Minister realized and thought let’s ask each cities how they utilized the money. What are the different areas on which they have work with the provided money.

The chief minister wants consolidate reports (which we generally called MIS reports) as well as some detailed reports.

Now, suppose different municipal corporations using different ways to restore data like Indore municipal corporation using Microsoft SQL SERVER, Bhopal municipal  corporation using ORACLE, Ujjain’s municipal corporation using  MYSQL, Devas municipal corporation using flat file system.

So there might be different heterogeneous systems used to maintain the data and data would be on different verticals like Transportation (Road & Bridges), Water harvesting (River  & tanks) ,Agriculture , lighting etc.

To grab this entire data & consolidate them in one single database and providing the reports to Chief minister so that he can analyze which city doing progress , which city consuming money , how much work done in different vertical so that , he can take further decisions.

I hope you understand  a big problem here which is gathering data from different heterogonous resource & compile them and provide a unique data base on which reports will create is a big task which can be easily done by an ETL (Extract Transform Loading) tool which is SQL SERVER Integration Service Tool.

Here Extract means extract or fetch data from different resource or data source whether it is SQL SERVER, ORACLE, MYSQL , Flat file or any other source

Transform means converting the data in to required single format

Load means prepare the data and provide final output.

Below is the image which illustrate same thing which we discussed earlier in this post.

ETL_First_image_By_Indiandotnet

So , I hope you understand the SSIS (ETL) role here.

Remember here SSIS use here to provide the data only rest work will be done by SSAS (SQL Server Analysis Services) and SSRS (SQL SERVER Reporting Services)

In this post we got 2 answers 1) What is SSIS ?  2) Why is SSIS? we will see How to use SSIS in next post.

Till then

Enjoy !!!

RJ !!!