In this video, we will try to understand what exactly are transactions and checkpoints. transaction is a concept where we say that either all the activities should pass or either all activities should fail. For example, look at the situation here in our SSIS project we are loading first the country master then the states and the salesperson then the product and the fact master. So, we would like to say that okay this is one big logical transaction here, if any one of them fails in between, we want that none of the records should get inserted. For example, let us say this country master is loading and after that after that something happens in the states package. If something happens in the states package, then we do not want the country master data to be inserted into the database.

Now, by default transactions are not enabled in our SSIS package. For example, you can see that over here, first the country master is loading and then the state's is loading. So what I've done is I've purposely injected an error in the state's master loading. So you can see that the states ID in the database is numeric. And I'm trying to put a text over here. Right?

So what will happen the state's muscle loading will crash. Now what I want is, if the state master loading crashes, I don't want country master data to be inserted into database because this is one big logical transaction right? But what happens in reality, if you go and run this application, let us say I go and run this application. So you can see that over here now, the country master first load so you can see the country master is loading here. Very nice. After that, the state master attempts to load but He crashes.

So you can see, if I go to the main DTS x package, you can see that the country master loaded successfully, but the state master did not loaded and the other things did not execute further. Now what my expectation is, if this is one big logical transaction, then I don't want this country master data to be inserted into the database, but when I go and see in my database here, when I go to my database here, and if I try to do a select here, you can see that the country data has been loaded. So in other words, transaction is not enabled by default in SSIS package. So now the next question is, how do we go and enable transactions. To enable transactions, we have something called as the sequence container. If you remember, in one of our previous videos, we had talked about the for loop container and the for each loop containers and One more container we have called as a sequence container if you drag and drop the sequence container okay it is one big box.

So, this sequence container you know you can think about it it actually goes and groups of components. So, what I can do is I can put the sequence container here and I can take all of these components and drag and drop them here. So, by doing so, what happens is, you know this becomes one logical transaction now, so, this is actually one big logical unit over here. Okay. So, and the next thing what I need to do is I need to right click on this and I need to go and go to the properties so, right click Properties and in this you can see that there is a nice property here called as transaction option and in the transaction option You can see that there is a drop down and then and there are three options in that. So, in this what we will do for now I will explain all the three options later on.

But But for now to enable transaction you will say required okay. So, so So now when I when I go and do this as required, what I have said is, I'm seeing that all of these components all of these DTS x package, what I have here right is now grouped into one logical transaction. So, again I repeat to enable injection, right click Properties, and you can see that there is a transaction option here and in that we have to go and say required. So, now, let me go and save this. Let us go back to SQL Server. And let me first go and delete all the records.

So, I'm going to go into Delete all the records from here. Right? And let us go and run the application. And again, what I've done is if you remember, we have actually injected an error in the state's master loading, right? So let me go and run this. So obviously, when I run this is going to go and crash in the states faster, right?

Stays monster loading. So you can see that country monster loaded fine. But when it came to state's monster, it crashed. So now what my expectation is, the country should not load so you can see that I've selected I'm trying to go and see that is that any data loaded in the dem country table, right. So I'm going to go into execute here and you can see that he has not loaded that means that because States muscle loading failed, whatever was loaded into the country master was reverted back. So in other words, now, this whole thing will either pass or the whole thing will either fail.

So this is termed as transactions. Now, the next thing, which we would like to discuss more in details is the transaction options, right. So if you remember, we had three different transaction options here, we just looked into the required one. So, what are the other two options? And what is the significance? Now, the other two options so these other two options over here that is supported and not supported, are more relevant when you have a sequence container inside a sequence container.

What do I mean by that? Let's say I have a sequence container here, right? So, these options become more relevant Then we have something like this. So let us say this sequence container which I have here now. Okay, I will do a control x here, control x, control x and I go and put this inside this. So you can see now we have a situation here in a where we have a sequence container inside a sequence container.

So, it is possible that in this of this parent sequence container, you can have some data flow tasks. So, you can have something like this. So, you can see that this sequence container one has its own data flow task. And once you execute this, he's going to go and execute this sequence container here. So, now, let us say situation number one, let us say that this parent sequence container has enabled transaction so he sees that okay, I'm enabling transactions Right now, the contained sequence container inside it, what he can do is he can go and override that transaction. So for example over here now if you see the parent sequence container has enabled injections, right?

So if you see at this moment, the parent sequence container here has enabled injections right. So what I can do here I can go and override that. So I can go to my child sequence container here and say that even though my parent sequence container has enabled injection, I'm not going to support it. I'm not going to support it. So what will happen this sequence container will run without connections. Okay, and the next option what we have here is supported so supported means that okay the parent sequence container has created a transaction he goes and joins that transaction and the last one is required.

Okay in required what will happen Let us say if the parent sequence container has not enabled injection then this person over here will go and create his own new transaction right. So, three options, one is not supported the time you say not supported, you know he is not going to create any transaction neither he will join any existing transaction supported, if there is an existing transaction in the parent sequence container, he will join him required you know in case the parent sequence container does not create transaction he will create a new one and also let us say it the parent sequence container has created a transaction and if you select required here, he will create his own transaction that means, the parent sequence container will also have his own transaction logical unit and this child sequence container is a separate logical unit. Okay, so those are those three options.

Now the next concept which I would like to discuss here checkpoints ok. So, if you see if you go to your properties of your of your package of your control flow right. So, go to control flow and click on this white area here and if you go to the properties, so, if you go to the properties here you will see that there is a there are two three properties here called a checkpoint file name and checkpoint usage and those things right. So, what exactly are checkpoints, now, checkpoints and transaction they go opposite to each other. Now, let us take the same situation what do we have here. So, let us say that it is the same project, country master is loading states master is loading salesperson has loaded and then suddenly something happens bad in the product loading.

Now, what you'd like to do is you'd like to go correct that error of product master So, whatever error has happened in the product master go and correct it and rerun the project. But when you read on the project, you don't want to start again from start you don't want to load the country master. Now, you don't want to load the state's master because they have already executed successfully right you want to start from the product master loading checkpoints checkpoints means that every time a transaction is successful, going to the checkpoint saying that I am successful to you here. So, next time you know when something bad happens you will start from there and not from the start okay. So, first thing is if you want to do checkpoints, you have to disable transactions because what transactions is either all of them are successful or either all of them fail.

And what checkpoint says checkpoint says that okay, whatever has been successful, I will not start from there. I will start from the point you know where the failure has happened. So definitely, in the same project, I cannot enable transactions and checkpoints because the concepts go opposite to each other, right? So the first thing is let me go to my sequence container here. And let me disable my transactions for now. So I'm going to go here and and disable my transaction, I will say that for now saying not supported.

So in other words, there are no transactions right. And to enable checkpoints, I'm going to go and right click on this control flow white area. So go to the control flow, and this outside white area, don't click on this component over here, click on this white area and go to properties. And over here, the first thing, what we need to do is we need to give a checkpoint file name. So I'm going to go here and create a small text file. So I'm going to go to my C drive.

Let us go to our same customer information system and in this I will create a file called as. So we don't have any file at this moment. So, let me create an empty file here and say new text file saying checkpoint file okay to open nice. So, the first thing is what exactly is this file? Why do we have to give a file over here this file is where SSIS goes and writes till what point he was successful. So, every time you know this application runs and let us say the country master is for successful, so he will go and write in this file saying that Okay, I have executed the country master successfully I have excluded the state successfully and let us say there is a problem in the salespersons So, what will happen is, next time when we run the project, you can see that we have said exists.

So, he will go to the checkpoint file name and he will say that okay I completed till country master and states I have to start from salesperson. So, the importance of this checkpoint file over here is that he goes and writes till what point he was successful it is a checkpoint till what point he was successful successful when the application ran previously and from what point has he to start now, right. So, one thing is you have to give the checkpoint file name and also you can see this small this property over here saying save checkpoint. So, you have to go and say your save checkpoints Yes, save the checkpoints, save you know, till what point was this package successfully executed right. So, that is one and the second thing what we also need to do is You know, the whole concept on which checkpoint works is till what point he was successful or till what point at what point he failed, right?

So we have to inform the SSI system, that whenever there is a failure, don't swallow that failure but inform the SSI system so that he can properly go and make an entry into the checkpoint file name. So what you have to do is you have to go and go to this main DTS x package, go and say that whenever there is a failure, don't don't swallow it, you know, true so fail whenever the parent fails or child fails, I'm seeing that fail at that time and inform the checkpoint system. So we can see that at on every component I am going and say, okay, when the package is failed, then please fail yourself and inform the SSI system. So you can see that I'm going to go and enable this failed package on failure true and fail parents Onto and save this right. Good. So, we already have our edit here.

So, if you remember we had entered injected. So, let me go and run this. So, what will happen now, the country master will load successfully right but it will crash in the states faster right. So, in the states monster it has crashed, let us go to the checkpoint file and let us see. So, you can see that over here, he has returned till what point he was successful, this is very interesting. Let us go and see that what has been put in the file.

So, I'm gonna go here and let us try to understand that what has he put in this file over here? So, in this checkpoint file, as we said previously, he puts till what point he has been successful. So, you can see here that, you know, there are some unique numbers over here, right. What are these unique numbers Now every time you drag and drop a component on the SSIS control flow or data flow right so every time you drag and drop these components here SSIS assigns them a unique u ID, what is a geo ID a geo ID is a value which is unique in time and space means a UID your generated UID and my generated UID can never be seen. Okay. So, for example, if you look at this sequence container, let us say if I go to the properties over here, you can see that that is a unique ID you can see that there is an ID here, right this ID right.

So, you can see now the sequencer said the sequence container ID is 553 f So, if I go here So, you can see that he's saying okay, this is 553 f that was successful, good. Okay, there's a container. If I go to the country master It says 706 B. So you can see that 706 p was successful. But after that, you know the state's master crashed. So you can see the state's master ID is 188 F, and you can see that his entry is not here.

So in other words, you can see that he says that Okay, I have executed the container successfully, I've excluded the country master successfully, but after that, you know, some problem happened. So in checkpoint file by using the geo ID it keeps track till what point SSIS package was successful. Okay. So now what we'll do is So, let us go back and correct the error and let us read on the package and what our expectation is, our expectation is that he should not start from country master. Now. He should start from he should start from states master loading.

So let us go to the state's master text and I'm going to go and correct this error. I received this Now let me go and rerun the package. So I'm going to go and rerun the package and let us see what happens. So, my expectation is he should start from state master. So, if you go here to the main DTS x package, so, you can see that let the package finish. So, let the package finish executing.

Now, let us go and see interesting right, you can see that he started from states master look at this green sign here, he started from here and not from here right because of that checkpoint file. You know, he read from the checkpoint file and he started from here. And now the interesting part would be to go and see the checkpoint file you can see the checkpoint file has been deleted. So he said he went ahead. He saw that till what point he has completed he started executing from there completed successfully and deleted why he deleted because, you know, VSA That if the checkpoint file exists then we only take consideration of checkpoints. So, if you remember, here we had said if exists exists right?

So what the SSIS package does in case you there are no checkpoint issues, he goes and he deletes the file. Now again, this checkpoint usage is a drop down, right? So we selected if exists so exists is that if the file exists, then only think about checkpoint The first one is never never means the file exists it does not exist don't even care about checkpoints right. And the last one is always always means that always go and look out for the file. If there is no checkpoint file then don't run the program at all means he's expecting always a checkpoint file, right. Good.

Now, I would like to also make couple of statements here about checkpoints. checkpoints are enabled in SSIS at the component level, right so if you see the entry Here right in your checkpoint file, it tracks the checkpoints at the component level, it will not track the checkpoint at row level row level means for example, now, let us say the error happens here in the second row. So, the checkpoint will not go till here and you go on the state's master component. So, this complete thing will be restarted. So, he cannot go at row level, okay, again I repeat checkpoints in SSIS are enabled at component level. So, it can restart from a component and not from a row.

Okay. So, in case if you're thinking something like this, you know where you are saying that okay, run it, and the first record gets inserted in the state's loading. But in the second record, there is a problem. So is it possible to restart from this? No, it will not happen for that, you know, we have to use SQL Server checkpoints at the end of the day, Your compete msbi stands on SQL Server engine, right? So, if you want to do at this minute level at this unit level of row, then you need to use SQL Server checkpoints.

And remember that SSIS checkpoints internally uses SQL Server checkpoints right. So, that was a very small important note, you know, where a lot of people always keep asking that can we enable checkpoints at the row level, you cannot do it through SSIS directly there is no limit component over here. For that you need to go to SQL Server and in the Steena storage procedure or by using t SQL, you need to go and enable your checkpoints. So, let us quickly revise you know what we have learned in this video. So, point number one we talked about transactions. transaction is a concept you know where we say that either all the activities should pass and in case there are a problem in any one of the activities then all the activities should fail.

We also discussed about checkpoints. checkpoints is again a transit is a comp is a concept, you know where the point from where the failure happened, we want to restart from that. Right. So checkpoint helps us to restart the execution from the point where the failure happened. Right and to enable transactions, remember you need to go you need to drag and drop the sequence container and use this transaction options drop down. And to enable checkpoints.

Remember you have to go to the package to the control flow property and you have to give the checkpoint filename and then go to every component and say fail, package on failure true and fail parent on failure. True. Right. Right. So I hope that you enjoyed this video. In this video, we were trying to understand what exactly are transactions and checkpoints.

So with this week, complete, seven hours of training and we have one more one Our pending to complete day one. And I was thinking that you know, what is the best way or in a grand way to complete day one? So I was thinking that how about the next one hour we spend on SSRS if you remember I said that all the three things are important in msbi a true msba developers developer, knows SSIS knows SSIS knows SSRS. So I was thinking that to end this day one, or the eight hours of training in a very grand way, by starting SSRS so the next video is a one hour flat video, which is on SSRS. And again, we would like to hear from you on which video you are on and if you're facing any problems, so please go to facebook.com slash questpond. And please put your feedbacks, please let us know on which video you are or if you're facing any problems and if this course is going right on Not thank you very much and keep watching the series.

Thank you