 Hello friends, welcome to SSUnitech. Sushil this site and this is continuation of SSIS tutorial. Today we gonna discuss about types of CASA mode in lookup transformation. If you haven't watched last video of this video series, so I would strongly recommend to watch that video where you can understand about basics of lookup transformation. So let's start. So there are three types of CASA mode available in lookup transformation. First is full CASA, second is parcel CASA and last is no CASA. So let's start with full CASA. So what is full CASA? So this is used very often in this option entire reference table will be preloaded into the CASA memory and lookup transformation will perform lookup from the memory instead of data set. This works well when we have less number of record in lookup table. We need to remember CASA connection manager can be used only this type of CASA. In this case when package starts data will pull and keep into memory CASA or file CASA and later only CASA will be used not database. We might not get the latest data from the database. Second is parcel CASA mode. So what is parcel CASA mode? So in this option lookup transformation starts with empty CASA. When a new row comes the data flow the lookup transformation first check for matching values in its CASA. If it's not found in the CASA then it will check in the lookup table. If the match is found in the lookup table then the value will be cast into memory for next time. So if the lookup table is very big then we can use this approach. So CASA connection manager can't be used with this option and advanced tab of lookup transformation will be enabled only in the case of parcel CASA where we can configure CASA size. In easily CASA will be empty. So for each value first it check into CASA if not found then it goes to the database. If found in database then it store that value in the CASA for next time. Startup time will be less than the full CASA but processing time will be longer and it also required a lot of memory. And our last option is no CASA. In this option lookup transformation will not use the CASA to store the lookup table at any stage. When a new row comes from the data flow the lookup transformation directly check in the lookup table for the matching values. Every time it will get the data from database. So go to on ssgt. So this package has been created in our last video. So double click on this lookup transformation then we can see CASA mode. So in case of full CASA as we can see CASA connection manager has been enabled. Once we move to parcel CASA then CASA connection manager has been disabled. Once we move to no CASA then CASA connection manager is also disabled. So as we have discussed CASA connection manager is only enabled when we are going to use CASA mode is full CASA. Next is when we move to parcel CASA then our advanced tab has been enabled. As we can see we can specify the CASA size and we can also enable CASA for rows with the no matching entries. When we move to our full CASA then advanced tab has been disabled. We cannot modify anything and in case of no CASA as we can see it's disabled. So here we can see this is our source table and it has total 5 rows and this is our lookup table and it has 3 rows. So once we talk about full CASA then we can see whole data from lookup table will be cast into memory as we can see in case of full CASA. All 3 rows have been cast into memory. When we talk about parcel CASA then in our source it will start from id 1 so it will check in our parcel CASA. So initially it's empty it goes to lookup data table and it will pick the value from there. So 1 then join and city is usm. For serial number 2 again we can see id is 1 so id 1 will be checked in our parcel CASA. So it's found so there is no need to go to in lookup table and it will pick directly from parcel CASA. When we move to serial number 3 then we can see id is again 1 so there is no need to go to lookup table it will directly pick in parcel CASA memory. When we move to serial number 4 then we can see id is 2 and here in parcel CASA memory we don't have id 2. So it will check first in our parcel CASA memory. If the value is not found in parcel CASA memory then it will go to lookup table and pick the value from there. So 2 then monies then deli. So our second row has been cast in the memory. When we move to our serial number 5 then id is 3. So first it will check in parcel CASA memory it's not found then it goes to data table. So in lookup data table we have 3 so 3 has been cast. So this is our parcel CASA. When we move to no CASA so in case of no CASA it will check from 1 so id 1 initially no CASA is empty. So it will directly move to lookup data table and it will pick the value from there. So join and usa. So it is cast. When we talk about serial number 2 in source table we have id 1. So it will directly check in no CASA. So in no CASA it will store the last first value. So as we have first id 1 so id 1 is available in no CASA. So it will directly pick the value from no CASA. When we talk about serial number 4 then it has the id 2 and in no CASA we have first our last value is 1. So it will directly move to lookup data table and replace our existing value from memory. So 2 then monies and then deli. When we talk about serial number 5 then we can see id is 3. 3 is not available in no CASA. So it will directly goes to lookup data table and 3 is available over there. So it will pick and replace our existing value. So 3 has been replaced and it will store our last updated value. So I hope you have understand how we can use full CASA, parcel CASA and no CASA. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. And don't forget to press the bell icon to get the notification of our newly uploaded videos. Thank you so much.