 Hello, welcome to SSPiritech, so see in this side and this is continuation of SSIS tutorial. So today we gonna discuss about conditional split. So what is conditional split and how we can use it. So first of all what is conditional split. So it is MSBI component which is available on SSIS toolbox. And next is it's just like a if else condition or case statement by which we can split our input data into multiple outputs depending on the certain condition which we will be applied. Next is it has one input and multiple outputs depending on the condition. And it also has a default output so that if a rows doesn't match with specified condition then that row will be redirect to that default output. And it also has an error output. So let's move to our case study. So we have two case study. First is based on the string data and second is based on the integer data. So let's start with string data. So in this scenario as we can see we have a CQS over table that contains different countries sale detail. Our requirement is to split the data for USA and India in different flat files and rest of the countries will move to a different flat file. Let's have a look of CQS over table. So as we can see this is our table and if we can execute our first query then we can see we have total four countries India, USA, Australia and Nepal. So we want to split data India in different flat file USA in different flat file and in our third flat file we will contain the information of Australia and Nepal. So how we can achieve it. Let's move to SSDT. So I am going to create a new package. So right click on this and create a new package. And then we can rename this package with conditional respect case study one. Now we are dealing with movement of data. So we have to use a data for task then double click on this. And now here we need to configure our source transformation and destination. So our source is already be source. So we can drag and drop this already be source. Now we can configure our source. So here we need to select our connection manager as we have created the connection. So I am going to delete this one and create a new one. Here we need to select the server name. So this is our server name we can select. And then here we need to select our database name. So our database is SSIS test. Now we can test our connection, connection succeed, click on OK, again OK. So here we need to select our table. As we can see our SQL server then we can select our second statement and goto on SSDT and we can change our data access mode and here we can write our SQL statement. I am going to paste that SQL statement. Now move to columns as we can see we want all the columns then click on OK. So we have done our source part. Now we need to configure our transformation. So go to on common then we can drag and drop this conditional script and we can connect with source. Now double click on this conditional script then it will open conditional script transformation editor. Here we can specify our conditions. So our first condition is if the country is India then we need to select double equals to instead of single and then double correct instead of single. Now here we need to specify our country. So here we want to split our data in case one if the country is India. So in our second condition again we need to drag and drop and here we need to set for USA. This is for USA and rest of the data will move to our default output as we have discussed. Now click on OK. So we have done our transformation. Now we need to configure our destination. So I am going to load data in flat file. So we have to use flat file destination. So we can drag and drop and this flat file for India here it will ask for what input we want to move this destination. So in our case one as we can see in our case one we have specified for the India. So this is for case one if you want then we can change it as well in our case two it will reflect the output for USA. So I am going to drag and drop this flat file destination for USA as well. So this is for USA and we need to connect and it will ask what output we want we want case two. Now we can click on OK. Then we need to drag and drop for default. It will contain the information of Australia and Nepal. So this is our default output. So here we need to configure our destination. So as we can see inside this folder we don't have any file at yet. Then click for India and it will ask for flat file connection manager. So click on new. So here it will ask for flat file format. I am going to use delimited. Now click on OK. Then here we need to select our file go to this folder and we want to create file with the name of India. So here we need to create file with the name of USA. So click on OK. Go to our mapping. So mapping succeed. Again click on OK. So we have done for India. Now I am going to configure for USA. Again we need to repeat the process. We can browse inside this conditional script. Here we need to create file with the name of USA. So click on open. Go to one columns. So we are OK with it. Now click on OK. Go to one mapping. Mapping succeed. So click on OK. Here we need to configure our default output. So click on new. Delimited file. We need to browse. And here we need to specify the file name. So default. Click on open. So column names in the first row. Go to one columns. Click on OK. Now go to one mapping. So mapping succeed. Again click on OK. So we have done our package. Now I am going to execute this package. As we can see package has been executed successfully. Now I am going to stop this package. And move to our folder. So here as we can see inside this India we have the data for India only. And inside this USA we have the data for USA only. And inside this default we have the data for Nepal and Australia as well. So this is for conditional split. Now move to our second case study. So in this case study as we can see we have integer or numeric data types. So we need to take care while choosing their orders. For example if we have 5, 7, 11, 12 and 25. So we have two scenarios by which we can select our order. In our case 1 if we will select if the number is less than 30 then all data will move to that output. And rest will not contain any information. In our case 2 as we can see this is our correct order. So here we need to specify in increasing order. Like we want to get the data is less than 10 then 20 then 30. So we have to specify in increasing order. First less than 10 second less than 20 then less than 30. So it will return output as we are expected. So this is our case study. Now we can implement. As we can see we have a table with conditional split. I am going to execute and we can see we have all the data. Now I am going to create one more package for this case study. And this time conditional split case study 2. So again we need to drag and drop our data flow task. Double click on this. Here we need to select our source. So our source is SQL Server Table. So we have to use ODDB source. Double click on this ODDB source. Then we have to specify our connection manager. So as we have created the connection so I am going to use that one. Here we need to select our table. So this is our table. Go to one columns. It has only one column. Now click on OK. So now we need to use conditional split. So go to inside this common drag and drop conditional split. Now we need to select. So after mapping, double click on this conditional split. Here we need to specify our conditions. So in our first case as we can see I am going to select the value if less than 30. Second if the id value is less than 20. Third if the id value is less than 10. Now click on OK. So I am going to use multicast for display data because I am not going to load this data into any destination. So this is for case 1. This is for case 2. And this is for case 3. I am not going to configure our default output. So click on this enable data viewer. Then again enable data viewer. And again enable data viewer. Now I am going to execute this package. So once we execute this package as we can see in our case 1 we are getting all the data. We don't have any data in our case 2. And in our case 3. Because we have selected incorrect sequence. Now I am going to change the sequence first less than 10. Less than 20 is OK. Then less than 30. Now click on OK. Now I am going to execute this package again. So as we can see we have the data in our case 1. Which contains the id less than 10. In our case 2 we have the data when the id is less than 20. In our case 3. Data if id is less than 30. We are getting the data as we are expected. So thank you so much for watching this video. I hope you have understand how we can use conditional split. And we need to take care while using the conditional split inside the integer and numeric data. If you like this video please subscribe our channel to get many more videos.