 Hey guys, welcome to SSUNITEX to decide and today we are going to see one more area of real-time interview question and answer. So in this video, we will see how we can load the fixed-length CSV file to the SEO SQL table. So basically while we are dealing with the SEO data factory pipelines, so where we cannot use the copy data activity directly to load the data from this fixed-length file to the SEO SQL or any other destination. So how we can achieve this inside the SEO data factory? So let me quickly go and show you the file first and after that we will discuss how we can do that. So this is the file that we can see like the ID, the first column, and the total length that we can see 11. Then the name, the length of the name is 100 that we could see here. After that the date of birth which is the date actual, so it is total 30 characters and after that the salary. So salary is having again total 11 characters. So this is the fixed-length file with four columns with the ID, name, date of birth, and salary. So let me quickly go and show you the destination. So in the destination here we have this table which is the EMP details and total we have four columns and as of now we do not have any data on this. So how we can load the data into this table? So let me quickly go into the SEO data factory. So this file is available inside the blob storage under the input folder with the name of the employee.txt. Now let me go into the SEO data factory. Here we have to create a data flow. Let me add a new data flow here. Now here we have to set the source. So what is our source? The source which is the blob storage with the input folder of the employee.txt file. So let me click on the new data set here. As our blob storage as the new data set, so we can click on continue. Then we need to select the file. So we can choose the delimited text file that is okay. Click on continue. Here we can go with the link service. So as we have already created the link service, so we can use that. Then we have to select the file. So the file which is available under the input folder, then the employee.txt file. We can mark the first row as header and click on okay. So we have done with the source and here if you can go in the projection level. So we can see we are having only a single column and this single column is having id, name, date of birth and salary all these values. If we can go into the data preview and try to refresh then we will validate like it will have only a single column here. So that we can see like this column. Now here we are required to use the derived column transformation. So what derived the column transformation will do? This will be going to convert the single column values with four different columns for the id, name, date of birth and salary. So here first we are required to use the id column. So let me select the id and under the expression what we have to do? We have to write the expression. So we need to use the substring. So this substring is asking the first parameter from where we want to get this. So we want to get this from this column. Now here second it is asking from which location we want to start. So we want to start from one and then number of characters. So that will be 11 as we have already seen for the id. Let me click on save and finish. So we have created one column here that is the id column. Let me click another column for the name and call this as name and here we need to fix the values from a single column which is the id, name, date of birth and salary. So again we have to use the substring. Now here we can select the column. Then it is asking from where you want to start. So in this case from where we want to start let me go here. So first 11 characters will be for id. So it will be started from 12 and will be taking total 100 characters. So here we have to specify from 12 and number of characters that will be 100 for the name. Now we can click on save and finish. Similarly let me add another column and this column is for the date of birth. Now here again we have to write the expression as an substring. Now here we have to select the column then the starting position. So how we can get the starting position for the date of birth. So if we can go so 11 character for the id then 100 character for the name. So total 111 characters till now. So it is going to start from 112. So let me go here and let me start from 112 and number of characters. So we want to use the 30. Now let me click on save and finish. So last column is the salary. So let me add the last column here and let me call this as salary. Here we have to write the substring function. So let me use the substring. Then the column name first parameter second parameter is the starting index position. So let me go here and like here we have total 11 character then we have 100. So it is 111. If we are adding 30 on that so that will be 141. So it will be going to start from 142. So we can specify like 142 and how many characters. So total characters we want to be. Now let me click save and finish here. Now go to the data preview and try to refresh. So it will be going to have total five columns. The first column is the existing column then the id, name, date of birth and salary. So we have splitted all these columns into four columns. So in the destination we are required to add only these four columns. So let me use the select transformation here. So select transformation we will be selecting only four columns. Like the first column is not required so we can remove that one and here the id, name, date of birth and salary. So these are the destination columns. Now we need to add the destination here. So let me add the sync and in this sync here we are required to set the data set. So let me add a new data set and this is for the azure sql database. We can go with that then we can select the link service that we have already created. And after that we have to select the table. So the table which is the EMP details click on OK. So we have created this data set here. If you can go in the mapping and here instead of auto mapping we can see there is some problem with the mapping because here we have clever characters and here we do not have any. So let me use the id here. Here let me use the date of birth and for the last I am going to select the salary. Now let me click on save this and here let me add a new pipeline for executing this data flow. So let me try to drag and drop this data flow to here and after that let me try to click on this debug. So once it will be executed then data should be loaded into the table. So it got executed successfully. Let me go into the ssms and here let me try to execute this. So here we could see all the data is successfully loaded into the table. So I hope guys you have understand how we can load the data from fixed length file to the azure sql table. Thank you so much for watching this video. See you in the next video with another ADF interview question and answer.