 Hello, Welcome to SSunitech, so we will decide and today we are going to see about the parse transformation in the SEO data factory. So before going forward, if you haven't watched the last video of this video series, so I would strongly recommend to watch that video where we have discussed about the Stringify transformation. So what is parse transformation? Use the parse transformation to parse text column into your data that are string in the document form. The current supported type of embedded document that can be parsed JSON, XML and delimited text. So these are the supported format that we can parse. So what is the parse transformation? Let's see in the practical, so you will have the better understanding. So here, if you have seen in the last video, we have loaded the data from the source to this table which is the sales rotable. What we need to do here, we need to parse this item into two column like the item 1, item 2 for this, item 5, 6 and 10 for three columns. So in the destination where we want to load the data in the below table, we can see the item 1, item 2, item 3. So from this data, it will be parsed into three columns. Similarly for the address, it is having like city, state and country. So this data will be going to load into the city, state and country. So this JSON will be going to parse into three columns here and this item which is the delimited text we could say with the comma delimited. So this will be parsed into three columns with item 1, 2 and 3. So this is the actual use of the parse transformation and this is very useful while we are going to deal with the XML or the JSON or the delimited text of any column. So let me go into the Azure Data Factory and we will try to implement this in practice. Now let me try to add a new data flow. So let me click on these three dots with the new data flow option. Here let me call this data flow as data flow of parse transformation. So that is for PS. Now first we have to add the source. So as we have already seen the source is the SSMS table which is the sales underscore raw table. So let me try to click on this add source. For that table we have already created the data set. So let me try to add the data set here. So we can see this raw. Let me try to open this so we can verify. So this is pointing to the Azure SQL database and the table which is the sales underscore raw. So the same table. Now here let me try to go in the data preview and try to refresh. So we can verify the data as we have seen in the source. So as we can see it has all the data. Now let me try to add the parse transformation first. So this is the parse transformation and let me call this parse transformation for PS underscore items. Let me go here. In the items as we have already seen we are having the delimited text here and it has total three columns value. Now so here it is showing error because we have used this underscore. Let me remove that and go back to here. So here input stream that is the source one that is okay. The format as we have seen for the item that is the delimited text. So we have to select that one. And after that in the delimited text setting let me open this first row as header. So we are not going to select that one in the column delimited that is the common. So everything is okay. Let me try to close this and here we can see the column. So either we can create a new column or we can modify in the existing column. So that we have to select over here. So I am going to select the items here. Now under this expression we have to write the expression. So the expression like from which column we want to do the parse we have to select here. So that is the items column. So from this column we are required to do the parse. So here let me call this with the parse. So let me use the ps after these items. So this new column will be added in your output stream. Here we can see the output column type. So here let me define the complex type. As we have already seen we have total three columns. So let me call the first column value as item 1 as what will be the data type that will be string. We can put the comma second for the item 2 and after that let me put the string as well similarly for the item 3 as your string value. Now let me save and finish and here let me go in the data preview and try to refresh it. So it will be going to have the data with three different columns for the item that we have already seen. So as we could see here the item 1 value which is the I1 that we can see which is hidden here. Second for the item 2 that is this one. Third is for item 3 for the first row we did not have the item 3. Let me verify that here we have only two items and here we have three items. So that is why here we can see none and here we can see the value. So this is we have passed successfully for the delimited text. Similarly let me do for the JSON format let me try to add a new pass because if we can see in the pass setting here we can select the format only once and we can add the multiple columns but the format that should be the same. So here we are going to use the JSON. Similarly we can do for the XML as well. Let me add the pass transformation and this pass transformation for the JSON one. So let me call this as country underscore we are not going to use we can directly write PS. Now the incoming string that is okay here the format that the JSON here we can see the JSON settings. So the document form is the single document we can select that one. Let me go in the columns now like which column we are going to create any new column or modifying the guesting one. So I am going to create a new column with the output new. Here under the expression we want to do the pass for the address column this time so we can select the address and save and finish. Here we can see the expression. So again we have to write the expression here similarly we have done in the item one. So CT the data type that is the string. Second for the state data type as string country data type as string. So this will be having three columns. Let me save and finish. Here let me go in the data preview and try to refresh it. So it will be going to have three columns there as well. So we could wait here we can see like the last three columns this is for city this is for the state and this is for the country. So these three columns are added here. Now everything is okay. So this is very straightforward we can say like in the part setting we can select the format and after that we can select the column by which we want to do the pass. So under the expression we have to specify the output column we can select here either we can modify the existing one or create the new one. So I have created the new here then we have to specify the columns with their data types that will be passed. Now we want to load this into the SQL Server table. So let me add this thing here and after that here we are required to select the data set. So data set can be selected because I have already created the data set for the same. So this is the first one data set that we have created for this. So let me try to open to verify the same. So this is pointing the same server and database and the table which is the sales underscore final and here the table which is the sales underscore final. Let me close this go to the mapping and auto mapping we have to off and here we need to do the mapping. So here we can see item one item two item three. So we need to select the item one item two item three. So this is under this column which is the items underscore ps. So let me add the item one from this similarly for item two and similarly for the item three. Now here we can see the city state and country. So this is coming from the address. So address column is having all this data. So that can see address new first is city. So we can select it second is the state. So we can select the state from here. Similarly for the country we can select the country here as well. So we have done this mapping successfully. Let me go in the data preview and try to refresh it. So here as we can see we are having all the data as per our expectations. Let me try to publish this and after that let me try to add a new pipeline to execute this data flow. Here for executing the data flow we are required to use the data flow activity. So we can drag and drop it here. So publish is completed. We can select the data flow that we have created. So go to the setting and here we can select the data flow which is data flow underscore ps. This is we have created. Let me try to execute So after completion of this execution your final table will have the data. So as we can see it got executed successfully. Let me go in the ssms and try to execute this query again. So here as we can see the data is successfully loaded into the destination table and this item which is the comma delimited passed into three columns that we can see and this address which is the json is passed into three columns which is the city state and country. So we are successfully loaded from this format to this format. So thank you so much for watching this video. See you in the next video.