 Hello, welcome to SSUnitech Social Decide and today we are going to record this video as per your demand as you were requested to record videos on SSIS interview questions and answers. So this is the first video I am going to record on the SSIS interview questions and answers. So this is totally dependent on the transitions. So before going forward if you haven't watched SSIS tutorial part 52 so I would strongly recommend to watch that video where you can understand about the transitions in the SSIS. So before jumping next slide if you haven't subscribed to my channel so I would strongly recommend to subscribe my channel and press the bell icon to get the notification of the newly uploaded videos. So move to next slide. So here as we can see we are having three dataflow tasks first, second and third. So the question is we just want to load that data from dataflow tasks 1 and 2. If any one of these will be failed then other should be rolled back and third dataflow task will be moved forward. So third will not be dependent on the first and second. But first and second both are dependent on each other. As I have created few scenarios over here as we can see in the input we are having dataflow task 1, dataflow task 2, dataflow task 3. If dataflow task 1 will be completed with success and second will be failed. So in the output we just want to roll back dataflow task 1 and 2 both. In the second scenario as we can see if the first dataflow task will be failed and second will success. So the first and second will be rolled back because first is rolled back. If both will be succeed then both will be completed. If any one will be failed then that second should be rolled back. So how we can design our package as of now we have only these three dataflow tasks. So I would suggest just pause this video and think about this. How we can set up this scenario because here we are having dataflow task 3 which is not dependent with the dataflow task 1 or 2. It will be run independently. So how we can design? So go to on ssdt and here as you can see we are having these three dataflow tasks. Let's have a look of flat files. So we are having flat file 1, 2 and 3. So in the t1 we are having 5 columns and 5 rows. So that is good. And here we are loading this data into sico server table. So let's have a look of sico server table. So this is a table. Let me refresh this. So as of now we don't have any data into this table. So here these three dataflow tasks will be dependent with each other. So let me execute this package and we will see. So as we can see all these have been executed successfully. Now I stop this package and go to on sico server table and let me refresh this table. So as we can see we are having 15 records. So just delete that one. Yes. Good. Now go to on flat file and let me create few custom error on this. So just create this custom error on the flat file too. Now I just want to execute the package. So once I execute this package then first will be completed with success and third will be completed. Second is failed. But here go to on table and let me refresh this. So we are having 10 records because first and third is completed with success. So what our requirement is we don't want to load the data for first dataflow task if second will be failed. So I would suggest we can use the sequence container. So let me drag and drop the sequence container and here let me put these dataflow tasks inside the sequence container and let me set the transaction property for this sequence container with the required. So go to on properties and here as we can see transaction options that is supported let me change with the required. So now a new transaction will be created at the sequence container level and go to on dataflow task. So in the dataflow task let me check the transaction options so that is supported. So it will join the existing transaction. So in the second it will also supported. So it will again join the transaction. So now this will be the part of sequence container and a transaction will be created at the sequence container level. So these two will be the part of the transaction. Now let me execute this package and see the output. So as we can see this is failed. Good dataflow task is completed with success. Stop this package go to on table let me refresh this. So we are having only five records so that looks good but it will ask one more question after this. Here we have created the sequence container and set the required transaction option for this sequence container. If we set the transaction option for this dataflow task one as not supported then what will be happened? So here don't get confused with this question so let me set this not supported. In this case this will not be part of the transaction so don't confuse with this question. Now in the table we will have ten records five from this dataflow task three and five from this dataflow task one. Let me execute this that looks good stop this package and go to on table. Let me refresh this it should have ten rows. So as we can see we are having ten rows. So this is the question don't get confused about this if we set not supported so it will not be part of the transaction anywhere. I hope you have understand this first question. If you like this video please subscribe my channel and press the bell icon to get the notification and do comment if you want more SSIS interview questions and answers. Thank you so much for watching this video.