Hey friends, Welcome Learn MSBI Series step by step. Here you will find questions and answers to all your MSBI doubts. We proudly say that we are expert in MSBI and other microsoft technologies.
In this article we will learn another component tool of SSIS i.e. Conditional Split Transformation. So if you have landed on this article directly then request you to read our previous article to get started on MSBI stepbystep. Click here to get that all MSBI articles. I'm saying this because in this article example which i will be using will be continuation of previous article i.e. How to implement ETL Process using SSIS with an example.
Apart from paid package we do provide free-learning materials (Videos & Articles) to kick start with MSBI and other topics.
Now coming back to topic, In this article session we will understand how to implement ETL i.e. (Extraction, Transformation and Loading) process in Data Warehouse using microsoft tool SSIS (SQL Server Integration Services).
Conditional Split is a MSBI component available in SSIS toolbox. This tool is used during Data Flow task and during ETL process. Conditional Split as name says "Condition" if you are a programmer then you can easily understand where i'm relating this word "Condition". "Conditional" in programming sector we say "Conditional Statements" means a simple "IF statement" or a "CASE statement" where depending on following condition a specific task executes.
The Conditional Split transformation checks the given condition and route data to appropriate destination depending on the given condition. For example if a men's age is greater than 66 then He can go it into the Senior citizen quota or else He will not be considered as senior citizen. Here the condition was age greater than 66 (age > 66).
To understand conditional split in more better way We will take up example of government employees i.e. From this government employee data we will split employees who's age is greater than 60 and to give them retirement. Further we split retired employees as per their grades because to give them retirement amount and to start their monthly pension.
Conditional Split 1
We will split employees in two parts i.e. If employee age is greater than 60 then we will put them in retired employee list.
Step 1
we will create a simple excel file with columns like EmpID, EmpName, EmpAge, EmpSalary, EmpGrade. as shown in below image.
Step 2
Now we will create a SQL table as our dataware house to store extracted and filtered data from Excel source.
Employees age with below 60 will go into employee table and all employees age above 60 will go into "RetiredEmployee" table.
Step 3
Create a new SSIS project -> On the control flow tab just drag and drop "Data Flow Task" as shown in below image.
Step 4
Just rename it with a suitable name and double click on it. Once you double click it will take you to "Data Flow" tab.
Step 5
Since we have our source file in an excel format so we will drag and drop "Excel Source from SSIS toolbox as shown in below image.
Step 6
Configure our excel file to this "Excel Source" if you don't no how to configure it request you to read our previous article on "ETL process".
Step 7
Now we will drag and drop "Conditional Split" component from SSIS toolbox.
Step 8
Right click -> Click on Edit and these two condition as shown in below image.
So what we are doing is we are splitting output to two formats on condition of their respective ages.
So if an employee age is greater than 60 than we will add it "RetiredEmployee" table and if an employee age is less than 60 than we will add it to "employee" table.
Once you have added this conditions just apply it and click on OK button.
Step 9
To add split data to database we need "ADO.NET destination" component so as we know that we are getting outputs in two formats and that formats we need to separately add it to two different tables i.e "RetiredEmployee" and "employee" table so for this we need two "ADO.NET destination" components.
Let's add two "ADO.NET destination" and configure it.
When you drag an output arrow from "Conditional Split" component to ADO.NET compoment it will prompt box state that on which condition you want split.
Just select condition and configure both "ADO.NET destination" respectively.
Step 10
Finally run the project and check the output in the SQL table.
Conditional Split 2
To decide the employees pension and retirement amount we will further split retired employee i.e. "BestRetiredEmployee" and "NormalRetiredEmployee".
Step 1
In the condition split component we will add two more conditions that if EmpGrade is A or B we will call them as BestRetiredEmp and if EmplGrade is C or D we will call them as NormalRetiredEmp for the same we have already created data-table in SQL as shown in below image.
Best Employee Table in SQL Database
Normal Employee Table in SQL Database
Step 2
Since we have to decide Pension amount and Retirement Amount depending on grades so for this we will add new component from SSIS toolbox i.e. "Derived Column" in this column we can add extra columns and do manipulation.
So just drap and drop "Derived column" rename it and drag new arrow from "Conditional split" and attach it to "Derived column" as shown in below image.
Step 3
Just right on "Derived column" and edit it as shown in below image.
So if you see above image we have added two more columns "EmpPension" and "EmpRetirementAmount" where on "EmpPension" we have added extra 5000 on their salary amount and on "EmpRetirementAmount" we have multiplied current EmpSalary to 50 times. Once you have completed this step click on OK.
Step 4
In this step we need add one more "ADO.NET" destination to store best retired employees data. So add new "ADO.NET" destination component and save it. Then drag an arrow from "Derived Column" to "ADO.NET" destination and save it.
Step 6
Finally Run the project to see the output.
Step 7
Same way repeat same steps for NormalRetiredEmployees.
So hey guys so hope you understood this article if you have any doubts feel free to drop us your query on below mention details
To quick start on MSBI, request you to watch our Tutorial Videos for free and read more MSBI articles.
If you want to complete .NET DVD Pack which includes AngularJS, Sharepoint, MVC, MVP, MSBI, SQL Server, C#, ASP.NET, Java, Testing, WCF, WPF then visit our Order now page.
Order only MSBI self-study learning video materials which are available with customized package costing 999 INR/15$. Click this link here to see details and order it.
