 Hello, welcome to SSHiritech, so see this side and this is continuation of SSIS tutorial. So today we wanna discuss about chain data capture with incremental load. If you haven't watched last video of this video series, so I would strongly recommend to watch that video before going forward because where you can understand about the chain data capture with initial load. So what is initial load and how we can implement initial load. Before move to SSGT let's have a look of sequence over table. So this is our source table which we have used in our last video and this is our destination table. So in our last video we have loaded this source data to destination. Now we will load data on incremented basis. If we will insert few record on this table and then we want to execute our incremented package then only those records will be moved to our destination table. If we will do some changes on this existing data then that data would be updated in our destination table. If we will delete any data from this source table then in destination table we are having a flag column that is deleted that would be replaced by true. So go to our SSGT to implement the package. Here let me create one more package. Then call this package as cdc incremental load that is fine. Now we have to use cdc control task this is for start and this is for end. So here I wish to rename this with start and this is for end. So I would like to change with end that is fine. Maybe in between these two cdc control task I would like to use a dataflow task. So we can drag and drop dataflow task as well. So before going to configure all these let me create connection managers. So first I would like to create new ledb connection manager and then call this as source. Next I would like to create one more ledb connection manager and this is for destination. So let me rename with destination. Then I would like to use area.net connection manager for this cdc control task because cdc control task accepts only area.net connection manager and call this as cdc nice. Now I need to configure our cdc control start. So double click on this and as we can see cdc database edu.net connection manager that is cdc because in our connection manager we have only one edu.net connection manager that is cdc. So here we have by default cdc. Now in cdc control operation I would like to use get processing range this time. So here I don't have any variable which will store the cdc state. So let me create a new variable cdc state nice click on ok. Now so table to use for storing state that is cdc state stable that is fine. So we have configured cdc control task start let me configure cdc control task end. In our SQL server cdc database edu.net connection manager that is cdc that is fine. In cdc control operation I would like to use mark processed range in our variable let me select cdc states variable that is fine and in our table I would like to use cdc states that is fine click on ok. So now we have configured our cdc control start and end. So we can connect with start then dataflow and dataflow with end. Now let me concentrate on dataflow task so double click on this dataflow task. So here I would like to use our source so that is cdc source. So we can drag and drop the cdc source then double click on the cdc source as we can see edu.net connection manager that is cdc that is fine ok. Now in cdc enable table as we can see we are having employee source because in our database we have enabled cdc for this table only that's why we are going to get this table in this drop down so we can select then in cdc processing mode I would like to change with net in our variable let me select our cdc state variable that is fine go to on columns so I am happy with all these columns and click on ok. Now we have done our source let me configure our transformation. So here we need to use a transformation that is cdc splitter so we can drag and drop and connect with source and there is nothing to do with cdc splitter it will divide our inserted updated and deleted record automatically. Now let me configure our destination where we want to load our incremented data so we want to load in cc server table so we need to use oledp destination that is fine let me connect with cdc splitter so it will ask what output we want so this time I would like to select insert output that is fine click on ok. Now click on this oledp destination and here let me select our destination table employee destination go to mapping, mapping succeed and in our is deleted as we can see ignored because we have specified default value for this is deleted that is 0 so this will be inserted now click on ok so this is our oledp command transformation so I would like to use for update and second for delete let me rename this with update and this is for delete so let me rename with delete now go to on cdc splitter and pick this data pipeline and select with update so here this is for update so we can select our update output click on ok and third is for delete so we can configure our delete now I would like to configure our update oledp command here we need to select our connection manager that is destination looks good go to on component properties in component properties we need to write our update statement so update then we want to update our destination table that is fine then we want to set value for EMP name with anything second is salary anything and we have department ID so that is department ID with anything and we want to update where EMP ID is anything now click on ok so we have then our sequel command now go to on column mapping so in column mapping as we can see we have available destination and available input columns so we want to map with parameter 0 to employee name second parameter 1 to salary and parameter 2 for department ID and then for employee ID that is fine click on ok so we have then our update next move to our delete output in delete output we don't want to actually delete the data from destination table we just want to update the is deleted flag so here go to on sequel command let me write our update statement so update and we want to update our EMP destination table this is our EMP destination then we want to set is deleted column with one where EMP ID is anything now click on ok go to on column mapping and our parameter 0 should be mapped with EMP ID now looks good click on ok and save our package so go to on SSMS and here I would like to insert some record so I would like to insert 11 name is Neha then salary something and department anything and we want to insert one more record name is Shiv and we are having salary anything and department anything so as we can see in our destination table we are having only 10 rows and in our source table we have 12 rows so our package will be executed then these 11 and 12 will be inserted in our destination table and here I would like to update some record so let me update Howard with Howard fight that is fine and let me delete one record that is employee ID 8 so once our package will be executed then 11 and 12 will be moved to our destination table and our ID to our employee name from Howard to Howard fight would be updated and for ID 8 in our destination table it will be replaced false to true so go to on ssdt and let me execute this package so as we can see we are getting 4 rows from our CDC source then after CDC splitter it will divide 2 rows for insert one row for update and one row for delete output so go to on SSMS and here I would like to refresh this destination table by pressing ctrl r so here as you can see we are having Howard fight as we were expected and in our ID 8 Jadip we are getting true that is also fine and for insert 11 and 12 have been inserted in our destination table so I hope you have understand what is CDC and why it's required and how we can do the initial load and here we have understand about agreement and load so 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 thank you so much