 Hey guys welcome to SSUnitex to see this side and today we are going to see about the Stignify Transformation. So to use the Stignify Transformation to turn complex data types into the strings. This can be very useful when you need to store or send the column data as a single string entity that may originate as a structure, map or the array type. So basically for example if we are having an input source and that input source is the complex data type. So the complex data type is nothing for example if we have a json file. So that json file is having the sub values on the json. So what is the sub values? So that will be the nested one. So like this whole file is the json file and in between we have some column which is again the json. So that column will be the complex type. So what we want to do? We want to keep that json as a single column and we need to put that into the SQL server. So this is we are going to do by using Stignify Transformation. So go to on the browser and we will try to see in the practical. So here we are in the blob storage under this input folder. We have this sales underscore raw text file. So let me go and try to see in the edit. So this is basically the json as we can see. So it is having like two records over here. The first record for the sales order id1 and second for the sales order id2. Here we can see the sales order date, the second column, the third column that is the items and then we can see the customer name and after that the address. So address is again the json that we can see. And it is having three columns that will be city, state and country. So if we are going to read this data by using the azio data factory. So where this will be the complex data type like the address one. So let me go and try to see that. So let me try to add a new data flow here and let me call this data flow as data flow is dignified transformation. Here let me try to add the source as I have already created the data set for this. So under this data set we are required to select that one. So here we can see this is the data set. So this is basically the json one. Let me try to open this and we will see here. So here we can see the input and the file which is the sales underscore a raw.txt file. So that is the same file that we can see here. Now let me close this and here everything is okay. We can go directly into the data preview. Let me try to refresh it. So once we will refresh it, then it will be going to have the data with all the columns that we can see sales order ID, then date, then item, then customer name and then address. Under the address we can see this curly braces. So it is indicating the complex type and it is having total three columns. One is hidden here that is for the city, then state, then country. If you can click over here, then that is the first column that is the city. So as I told you if we can go in the projection, then we can also verify like the address. So address is having the complex data type and here we can check we have three columns under this that is city, state and country. So what we want to do, if you are going to use directly sync over here, then let me show you that one first and under this, let me try to select the dataset that we have already created for the same. So that is for this one for the sales. Let me try to open this. So this is under the SSU database and under this table, which is sales underscore raw. So let me go in the SSMS and we'll see the table. So this is the first one, let me try to execute. So as of now we don't have any data like the sales order ID, sales order date, item, customer name and address. Address is having only a single column. It does not have multiple columns. So let me close this and go back to here and go in the mapping. Let me disable this auto mapping. So what it is doing here, we have to map this manually for the SOID and after that for the SO date. But here if we can see under this address, so address is the complex type. So either we can select the city, state or country. We cannot select the whole address here. If you are going to select all these, then here we can see the error under this data preview and here is the error because this address we have to remove. So let me close this and go back to here and in between we have to select the Stignify transformation. So that is under this formats and this is now here. Let me try to output a stream name that is Stignify that is okay and after that we have the incoming so that is the source one that is also okay. Here we can see the format. So in this case what is the format? The format which is the JSON. So here we can choose the two formats one for the JSON or second for the delimited text. So in our case we do not have the delimited text but we have the JSON format here. So we have to select one and after that here we have to select the column. So the column which is the address is having the values like complex data type and here under this expression we have to select that column again. So what we are doing we are going to replace the existing column with the string value of that column. So let me go in the data preview and try to refresh it. So it will be going to have the data as an string here. So here as we could see we are having the data which is the ABC. So here it does not have the complex data type. If we can go here then we can see the complex data type but it is based on the address. So this is updated here that we can see. Now next what we have to do we cannot load directly into the sync here as well because this is converted into the string but actually it is not. So what we have to do to convert this address as a string that we have converted from this complex data type to this one. So for that we have to use the derived column transformation. So derived column transformation will be going to convert your incoming address with the updated one. So here we can see this address under this expression we are required to only convert this into the string. So for that we can use the two strings and after that we can select the address column that we are having here. So this is the address column because here we can see this is again coming like actually it is a string but it is having three columns here. So what we are going to do we are just converting everything in the string and it will be keeping only a single column. So let me save and close here. So now here this is the address or we can call this like address one. So it will be great to differentiate between these two. Now we can go in the sync and here as we have already done the mapping so let me try to reset this and after that let me try to do the mapping again. So here SID, second SODAT, here the address. So under this address we have created a new which is the address one that we can see. So let me select that one. So everything looks okay. We can go in the data preview and try to refresh it. So this time it will be going to load as it is into the destination that is your SICO server. So we have to wait. So here as we could see it is having 1, 2, 3, 4 and 5 columns and in the address that is the same value that we are receiving from the source as a single column over here. Now let me try to publish this in between let me try to create a new pipeline. So this pipeline will be going to execute that data flow that we have created. So publish is completed. So we have to use the data flow activity here. And after that we are going the setting and under this we have to select the stignify transformation that we have created. So that is df underscore st. Now let me try to debug it. So once it will be executed then we will be going to see the data into the destination table. So it got executed successfully. Let me go into the SICO server and here let me check the data. So as we can see the data is successfully loaded over here. Now let me try to recap what we have done in this video. So here basically we are trying to load the data which is the complex data type into the SICO server directly without making any changes on that data. So in the source we are going to get the data from the Azure Blob Storage and after that we are using the stignify transformation. Stignify transformation will be going to convert your incoming complex data type into the string one. So that is converted over here but it is having like three columns. So we are going to use the derived column to convert that value as an string for all those. And after that in the sync we are directly loading that into the destination. So this is what we have done over here. Let me go here. So as we could see this data is not the proper data. So this is basically raw data we could see. So what will be the proper data? So as we can see in the second table which is the SOID SO date in the item we can see item 1, 2 and 3. Here it is having the comma separated values for those items. So we want to load that into this format and for the address instead of the address in a single column we want to keep into three different columns. So this is what we want to do. So that we will see in the next video how we can implement that. Thank you so much. See you in the next video.