 Hello, welcome to SSUnitech. So, see this side and this is continuation of SSIS tutorial. So, today we gonna discuss about OLEDB command transformation. So, what is OLEDB command transformation? So, the OLEDB command transformation runs SQL statement for each rows in the data flow. For example, we can run an SQL statement that could be inserts, update or delete rows in our database table. And the SQL statement includes parameters. The parameter values are stored in the external column in the transformation input and mapping an input column to an external columns, maps an input column to a parameter. So, what it mean and how we can use it? So, first of all we want to load data in our database table, then we want to delete that data, then we want to update the data. So, go to our SSMS. So, have a look of source. So, this is our source table. Once we execute, then we can see it have two columns. First is ID, then name. And in our destination, it also contains two columns. First is ID and second is name. Initially, we don't have any data. So, by using OLEDB command transformation, we want to load data in this table from our source table. So, go to our SSGT and here we are going to add one more package. So, right click on this SSIS package, then new SSIS package. After that, I am going to rename this package by OLEDB. Then we have to use a data flow task. So, I am going to drag and drop this data flow task and then double click on this data flow task. So, here we need to configure our source, transformation and destination. As we have discussed, we have SQL Server table. So, we have to use OLEDB source. So, we can drag and drop. Then double click on this OLEDB source. Here we need to select OLEDB connection manager. So, I am going to click on new. Then we have already made the connection. So, I am going to click on OK. Here we need to select our source table. So, as we have discussed, this is our source table. Go to our columns. So, we want ID and name both columns. So, we can click on OK. Now, we have done our source. Now, we have to configure our transformation and that is OLEDB command transformation. So, this is our label inside this command. So, we can drag and drop this OLEDB command transformation. Then we need to connect with OLEDB source. So, here we need to configure our OLEDB command transformation. So, now double click on this OLEDB command. In connection manager, we need to select our destination connection manager. So, that is our SQL Server table. So, we can select then go to on component properties. So, here we can see we have an option for our SQL statement. So, this is our SQL command. Here we can write our SQL statement. So, as I have already typed the query. So, this is for insert statement. So, I am going to copy this and paste in our SQL command. So, here we can see we want to insert data in our destination table. So, this is our insert statement. Then values coming from parameters. So, for that we need to specify question mark. So, I have specified two question marks. So, first for the ID and second for the name. And then we want to map these parameters from our input source. So, I am going to click on OK. Then we need to go on column mappings. So, in this column mapping we can see we have ID and name. So, we need to take care about the orders which we have specified in our query. So, first is for ID. So, parameter 0 should be mapped with ID. Then name should be mapped with parameter 1. So, we have done our mapping. Now, we need to click on OK. So, we have done our OLEDB command transformation. Now, we can click on start. As we can see 8 rows should be inserted in our destination table. So, I am going to stop this package and go to on SSMS. Then we can execute our selected statement. So, as we can see we are getting the data. This data is inserted in our destination table by row by row. So, our first row will be insert first, then second, then third. So, it will be performed row by row. That is why the performance of this OLEDB command transformation is very low. Then we need to configure for delete. So, I am going to change our SQL command. And this time I want use for delete. So, this is our deleted statement. So, I am going to copy this and then we need to paste that command in our SQL command. Then click on OK. Go to on column mappings. As we can see ID mapped with parameter 0. So, that is fine to me. Now, click on OK. And now, I am going to execute our package. So, this time 8 rows should be deleted from our destination table. Now, go to on SQL Server Management Studio. Then we need to execute our selected statement for destination table. So, as we can see all rows have gone. So, I am going to insert a record one more time. So, we can copy this inserted statement. Go to on OLEDB command. Go to on component properties. Then in SQL command, now click on OK. Then go to on column mappings. So, ID has been mapped successfully. Now, we need to map for name. Name is mapped successfully. Now click on OK. Now, we can save our package and then we can execute. Once we execute our package, then 8 rows should be inserted in our destination table. Now, go to on SSMS. Then we need to execute our selected statement. Here we can see we are getting the data. Now, this time I want to update some record manually. So, we can update our destination table. Then set and this time we want to set the value for su kumar sin, where ID is equals to 7. Now, we can execute our update statement. Now, I am going to select. So, as you can see in our ID 7, we are getting su kumar sin. But in our source, we are getting su only. So, once we execute our update statement, then our destination table should be updated. So, I am going to copy this update statement. Then go to on SSDT. Here we need to double click on this OLEDB command. Then component properties. Then SQL command. This time insert should be replaced by our update statement. As we can see name comes first. So, we need to map parameter 0 with the name and then parameter 1 with the ID. Now click on OK. Go to on mapping. So, name should be mapped with parameter 0 and ID should be mapped with parameter 1. So, we can map. Now click on OK. So, we have done our package. Now we can save our package and execute. So, we can stop this package and go to on SSMS. Then we can execute our selected statement for destination table. As you can see, su kumar sin has been updated by su. So, I hope you have understood how we can use OLEDB command transformation. So, it will perform row by row. That's why the performance wise it's not good. Except this, we can use import export or we can use execute sql task to execute over sql queries. 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.