 Hello, welcome to SSUnitech Society site and this is continuation of SSIS tutorial. So today we gonna discuss about transactions in SSIS and this is very important. So let's start with transactions. So SSIS also includes the transactions options to maintain the data integrity. And there are three types of transactions in SSIS that can be used in containers or tasks. First is non-supported, second is supported and last is required. In case of non-supported it doesn't start any new transaction or it will not going to join any existing transaction. For example, if we set transaction as required at the package level and at the task level not supported then that task will not be part of the transaction. Next is supported so it doesn't start any new transaction but it has the ability to join any existing transaction. Like if we set transaction options required at the package level and at the task level we gonna set as supported then that task will be part of the transaction. So this is our supported. And in case of required if there is any existing transaction then it will join that transaction otherwise it will start a new transaction. So there are three types of transactions and we will discuss one by one. So first I would like to start from non-supported. So go to on ssdt and here I would like to add one more package. Then I wish to rename this package with transactions. So this is our transactions and what our plan? Our plan we have a sequence of our table with the name of transactions and it has only one column with the name of ID and data type is integer as you can see over here. So by using SSIS package I would like to insert record on this table and we will generate a custom error to check the transaction how transaction will work. So this is our table and go to on ssdt. Here this is our package so I can right click on this package and go to on properties and here we have an option with transaction. This is our transaction option so I would like to change with required. So our package will be part of a transaction and now I gonna use execute sql task. So this is our first execute sql task this is our second so double click on this and here I would like to create a connection so new connection. As we have already made the connection so I gonna use that one click on ok. So here we need to write our sql query. So as I have already written the query so I gonna copy this and paste it here. Now click on ok again ok. So we can check the property of this execute sql task. Here as we can see transaction so transaction is supported so I gonna change this supported as not supported. Then I would like to add our first execute sql task to second not double click on this and here I would like to add our connection then we can write our sql statement. And this time I can copy this and paste it here and here I would like to pass a string value. So a string value will not be going to insert in our table as we are having integer data type in our table. So it will reflect an error now click on ok. So once our package will be executed then our package level transaction is required so it will start a transaction but in case of execute sql task as we can see it is not supported so it will not going to join any transaction and in case of execute sql task 1 so here as we can see supported so I gonna change this with not supported. So in this case our first execute sql task data will be inserted in our table but in our second it will not going to insert in our table because it is not part of transaction. Now we can execute and check. So first execute sql task executed successfully and data should be inserted in our table. Now we can stop this package go to on ssms and here we can refresh this table so we should have id1 so this is our not supported and in our package level we have required Now here I would like to change transaction level with supported. So this is our supported in our second change to supported. So these two will be part of transaction because at package level we have set our transaction as required so it will start a transaction and at the task level which is our execute sql task we have set as a supported. So in case of supported it will join our existing transaction which we have set at the package level. Now once I will execute our package so first execute sql task executed successfully and second failed. Now we can stop this package go to on ssms now I can refresh this table so we are not having any data because this package is a part of transaction. So if any task will be failed then whole package will be rolled back. Now I would like to delete our second execute sql task now double click on this execute sql task and here first it will insert one then I will insert abc. So first query will be executed successfully and second will throw an error. So we can save it now we can click on OK. So here I would like to change our package level transaction will be not required. So this is our required so I am going to change that not required. So at package level we don't have any transaction in task level we have set as supported. So it will not going to create any transaction but it has the ability to join the transaction but in our package we don't have any transaction so what will be happen. So we can start. So package failed. Now go to on ssms and here I can refresh so we can see our first insert statement executed successfully so data has been inserted in our table. Now I can stop this package and here I am going to change transaction level with required. So change from supported to required now we can save and execute. So now it has the ability to generate our transaction. So our whole task will be part of transaction first query will be executed successfully and second will throw an error. So whole task will be rolled back. Now we can click on start now stop this package and go to on ssms and here we have two rows now we can refresh and we are having still two rows. So I hope you have understand how we can implement our transaction if we required. Thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos.