 Hello, welcome to SS Unitech, so see this side and this is continuation of SSIS tutorial. So today we going to discuss about one more transformation which is derived column transformation. So derived column transformation is very important transformation because here we can apply the expressions according to our business requirement. So let's start with derived column transformation. So what is derived column transformation? So basically it is used to create a new column values or replace existing column values by applying expressions. So what is expression? So an expression can contain any combination of variables, functions and columns from transformation input. For example, if we are getting data from SQL server table and we want to apply few formula to manipulate the input, then we can use derived column transformation by which we can manipulate our input data. It also has an option to concatenate data from different columns. Like if we are getting the data like first name and last name and we want to concatenate first name and last name to get the full name. So by using this transformation we can achieve this. So we have few examples over here. As we can see we have input table and it has few columns. So in our first condition we want to concatenate first name and last name. So we can do this. Second, if we want to get the only first character from first name then also we can do this. If we want to apply round function inside the salary column and we want to only do digit after decimal place, so we can do this. If we want to extract only here from data birth then we can also do this. So how we can do this? So let's start. So this is our table as we can see it has four rows we have displayed in our slide. So let's move to SSGT. So here we need to add one more package, right click on this SSIS package and add a new SSIS package and rename with derived column. So this is what derived column transformation. So first of all we have to use a data flow task and inside this data flow task we have to configure our source. So our source is C cross over table. So we have to use OLEDB source. Now we need to configure OLEDB source. Here we need to specify the connection manager. So click on new. As we have created the connection so I am going to use this one click on OK. Here we need to select the table. So this is our table we can select and go to one columns. So we want all the columns click on OK. So we have done our source part. Now we need to use derived column. So this is our derived column transformation. So we can drag and drop as we can see over here it has effects. So we can use expressions. So double click on this derived column and go to one columns. So first of all we want to concatenate first name and last name as we can see over here. So first name then last name. So we can drag and drop this first name inside this expression. So this is our first name. Then we need to specify plus symbol and then we want one space. Then again concatenate symbol then last name. So we can drag and drop this last name and we can rename this with full name. This is our full name. As we can see derived column inside this derived column we have dropped down. What we want? We want to add a new column or we want to replace our existing column. So we want to add a new column for this. So to your first option. So we have done our first part. Now in our second condition we want to get only first character from first name. So drag and drop this first name inside the expression. Then we need to write substring. So this is our substring and we want only first character. So we have to specify one and one. So we can close the bracket. So we have done our second part and this is for first character from first name. So our next is we want around the salary. So drag and drop this salary column inside the expression and we have to use round. So we can specify round comma two because we want round only two decimal places. So we have done our third and this is for round salary. So our next is we want to extract your part from data work. So we need to drag and drop data work this time and so here we need to use that part function. So that part then we need to specify what we want to extract. We want to extract here. So we have to specify here then close the bracket. So this is for here. So we have done all these. So now I am going to use multicast in upcoming videos for this time I am not going to load this data into any table or any flat file. I am just going to populate this data by using enable data viewer. It will pause this package and display the data. So now I am going to execute this package. So once we execute this package then we can see package is paused between these two tasks because data viewer is enabled between them. So as we can see we have full name then first character from first name then round salary and then here. So we are getting the data as we were expected. So we have done all these four examples now as we can see I have explained this. So here we can see we can also use if a condition like if the month name is equal equal to one then we want to display Jan if the month name is equal to equal to two then we want to fab if this value not be satisfied then we want to this number. So this is our if then then again if then else. So we can use if else condition inside our drag column. So how we can achieve this we can now I am going to delete this one and going to add one more column. So I am going to use for employee ID if the employee ID is less or equal to two then we have to use Koshelma and we need to specify low then again column and we have to specify our employee ID less or equal to three then we want to display medium else we want to display large. So this is our if and else condition. Now we can check again I am going to use multicast and going to connect here we need to enable our data where now I am going to execute as we can see last column has been added successfully. So for ID one and two we are getting low for ID three we are getting medium for ID four we are getting large. So we can use if and else condition as well. We can also replace null values by using the first condition as we have discussed over here or we have a replace null formula by which we can directly replace the null values. Now I am going to draw I am going to add one more column and this time if the null value is present in this department name then we want to replace that value. So this is a function which is available inside this editor if we need then we can use. So go to on null function so we have replaced null so we can use it replace null then if the null value is present in this department name then we want to display NA. So now I am going to again enable data where so as of now we can see we have the null value in department name if the employee ID is three so now I am going to execute this package. So null values should be replaced by NA. So for this column as we can see null value has been replaced by NA so we can use it if we needed. So we have a live assignment for you check if null and divide by zero values are available if available then replaced by 0.00 and formula for this live assignment will be available on the description of this video if you need then you can copy from there. So thank you so much for watching this video if you like this video please subscribe our channel to get many more videos.