 Hey guys, welcome to SSUNITEX Lucille Decide and today we are going to start with the conditional split transformation. So in the last video of this video series, we have discussed about the joint transformation. So before going forward, if you haven't watched that video, so I would strongly recommend to go and watch that video. I'll provide the link of that video in the description of this video. So let's get started with the today's video. So what is the conditional split? So basically the conditional split transformation routes data rows to different streams based on the matching condition. So this is the same thing as we have already seen inside the SSIS conditional split. So we will be going to have one input source and based on certain condition, for example, in the input, we are having employee data. In the employee data, it will have the data from the different departments. It might be having the data from IT department, HR department, payroll department, accounts department. So according to that department, we want to split that data into different different outputs. So for the IT departments, I want to create one Excel from this input and that will be holding only for IT department data. Similarly for the HR, so we will be going to see in the practical in this video. So don't worry for now. Next, the conditional split transformation is similar to the case decision structure in the programming languages. The transformation evaluates expressions and based on the results directs the data row to the specified stream. So this is the same thing. So this is not very clear. Then go to the practical and we'll see. So here as we can see in the output folder, we don't have anything. So we just want to load the data from this input. So under this input, we have this employee file. So under this employee file, it is containing the data for the employees like employee ID, employee name, employee address and department. So we want to split this data as per their department. So here it has the data for IT department and HR department. So we want to create two separate files for the IT and HR and loading data from this input a single file to two different files and those files under the output folder. So this is the actual requirement. So go to on the Azure Data Factory and here let me try to add a new data flow. So this data flow here we are required to add the source. If you are not familiar with the data flows, I have already discussed about the data flows in earlier of this video series. So let me call this as conditional split. Now here our source data is in the blob storage. So let me add a source and here we have already created a data set for the source. So let me try to add this. So this is the employee one. So we have already created this data set in earlier of this video. Now go to the source options. So under the source options here we can see all these options. So we are not required to go there directly go in the data preview and try to refresh it and it will have all the data like IT department along with the HR department data. So that you can see here. Now we are required to split IT and HR department into two different outputs. So we can click on this plus symbol and here we can see option for the conditional split. So in this symbol we can see it is taking one input and generating multiple outputs. So let me try to click on this conditional split. Here by default it is adding two outputs. The first output as per the condition and second the default output. So if we can select the first one here we can see the output stream name is the split one. So we can call this as IT and this is the incoming stream that is from the source one. So that is okay. Here we can see the split condition. So we need to specify the split condition here. So we can go and the stream names. So this should be for the IT. So we can call this as IT. And here we can see the condition. So we need to specify the condition here. So we can open the expression builder and under the expression builder we can directly select the department. Then you can see here. So click on department name double equals to we have to specify then double quote. Here we can filter for the IT. We can directly save and finish. Now here we need to specify if the rows are not matching any condition. So let me call this as default. So if anything is not matching with the department ID then all those rows will go in the default one. If we have more than two conditions then we can click on this plus symbol here to add one more condition. In our case that will be the HR and go back to here in the expression builder. Here we can select the department name double equals to with the HR and we can save and finish it. Now here we can see we have three outputs one for the IT, second for the HR and third for the default. Now go in the IT and here we can see the plus symbol. So we can click on that. Let me add the sync part here. So under the sync we are required to set up the output location. So I am not going to create the dataset. I am going to use the inline query. And under this inline query here we can see the inline data set type. So we can select for the delimited text. Now we need to specify the link service. So we have already created the link service. So I am going to use that one. So this is for the SSU testing. We can select that. Now go to on the settings. So under the settings we can see the folder path. So we are required to specify the folder path here. So we can browse and data should be loaded under the output folder. So we can select the output folder and click on okay. So this should be like output folder. Now here we can scroll a little bit downside and we will see the option for the first row as header. So yes we want first row as header. So we can click on this checkbox. Now here we can see file name option. So under the file name option we can see like the pattern or the name file as per the column data and here name output to a single file. So I am going to use the output to single file and let me call this as it. So this will be the it file. Now we can go for the HR one and let me try to add the HR like in the sync. Inside the sync here let me go with the inline query again. Data set type that should be delegated text. So that we can select and after that we can select the link service name that is SSU testing. Now go to the settings and under settings go to the folder path and need to select the output folder path here and click on okay. Here the first row as header. So that should be true and here the file name option. We don't want default output to a single file and let me call this as HR file. So we have done everything here either we can directly check like we can go in the sync one go to the data preview option and here let me try to refresh it. So under this sync we can see it will have total two rows for the it department. So let's wait. So here we can see the department name is it and it is having employee Rajesh and Joanne is in it department. Let me go in the second one which is the HR department and let me try to refresh it. So it should have the HR department data there. So we can wait. ID 2 and 4 is the HR department that we could see. Now we can publish this. So it is saying the file name option output a single file single partition should be selected partition type. So go back to the sync one and here go to the setting and it is saying set single partition. So we can select the set single partition because inside the optimize here we can see the use current partition by default it was there. So that's why we have to select the single partition. So it should have the single file there as we can see use current partition. So instead of that it should be single partition. Now we can do the publish here. So it will be publishing. Now publish is completed. Now go to the pipeline and we'll add one more pipeline. And here let me use the data flow activity. So this data flow activity will call the data flow that we have created for the conditional split. Now we can do the debug on this. So it will be executing and after execution of this data flow the file HR and IT will be available in the output. So it is in progress so we can wait. So here it got succeed. Now we can go in the output folder and try to refresh it. So it should have two files that you can see the HR and IT. So under the HR file it should have the data only for HR. So we can edit and we can check. Yeah it has the HR data in the IT it will have the data only for the IT department. So here we have split that into two outputs that you can see IT department. If you are having more than two conditions so we can also add all the conditions here like the IT department HR department then we can add the payroll department accounts department. So whatever the departments you are having you can add here and add the sync. So all the files will be loading in the destination. So the main purpose of this transformation to split our input data into different different outputs. So one thing that you need to remember for example if in the input we are getting 10 rows then the output of all these transformations we need to sum that should be 10. Like if here we are getting 10 rows then the IT will be returning 4, HR will be returning 4, default will be 2. So adding all the output rows of the transformation will be equals to the source number of rows. So this is the conditional split transformation. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. See you in the next video.