 Hey guys welcome to SSVNitech, so we will decide and today we are going to see about the lookup activity inside the Azure Data Factory. So if you haven't watched the last video of this video series, so where you can understand about the delete activity and today we will see about the lookup activity. So what is the lookup activity in the Azure Data Factory? So what is the lookup activity in Azure Data Factory? So lookup activity can retrieve a data set from any of the Azure Data Factory. So it can be used to retrieve any of the data set. Next it can read and return the context of a configuration file or table. It may also return the result of the executing a query or stop procedure. So it may be used to get the values from the config file or table or we can directly get the values by executing the SQL query or the stop procedures. Next is the output of the lookup activity can be used in the subsequent activities. So that we will see in the next video how we can use the output of the lookup activity. So before going forward, I would strongly recommend to visit this website of the Microsoft. So you will have the better understanding what type of sources we can use. Let me quickly go through about this link. So here you can see the supported capabilities. So under the supported capabilities, you can see the lookup activity can return up to 5000 rows. And if the result set contains more than records, the first 5000 records will be returned. Next the lookup activity output only supports up to 4 MB in the size. Activity will fail if the size is exceed in the limit. Next the longest duration of the lookup activity before timeout is 24 hours. If it is going to take more than that, your lookup activity will fail. Next when you use the query or stop procedure to lookup the data makes your to return one and exact one result set. Otherwise your lookup activity will fail. Like if you are going to select any select statement, so you should have only a single output. If we are going to get the multiple outputs there by writing the two selected statements may be your lookup activity will fail. Next these are the supported data types by which we can use the lookup activity. So under the azure as you can see the azure blob storage and other options is also available. So you can go through on this like here azure SQL database is also available. Under the databases we can see we can use the DB2 we can use the Amazon and we can scroll a little bit down side. How we can use HBS we can use MariaDB we can use MySQL is also and Oracle. So these are the data types and you can see the SQL server as well. So these are the your sources by which you can get the data in the lookup activity. So go to order in the requirement and we will try to understand that requirement. So here in my example I am having these two databases in real time your source will be different environment and your destination will be different environment. So here under the same SQL server we are having two databases that you can see SSU and SSU product. Under the SSU we are having one of the table that is the partner revenue detail. So in the partner revenue detail that you can see the data. So this table may be going to fill on first week of every month and the refresh date of that will be updated for that date only. So in this month that you can see April 2022. So the data of this table is updated and you can see first of the April 2022. So if your refresh date which is the latest one then only we want to execute our pipeline and will load all the data into the other database that you can see SSU product. So under the SSU product we are also having this table and the table structure is same for both the tables that you can verify from here. So if you have the latest data only then we want to load the data there. So how we can check the latest data here. So for that we have to use the lookup activity. So go to on the browser and go to on the portal and let me try to create a new pipeline here. And let me call this pipeline as lookup activity. Here we can go under the activities and here we can search for the lookup. So we can drag and drop the lookup activity. Now under the lookup activity we can see here the name that is lookup one. Second is the description. So in the name we can call this as get refresh date. In the description we can specify the description if you want then the timeout retry. So all these options we have already seen in many of the activities. So go to on the settings directly. So under the setting we have to create a data set. So as of now we don't have any data set which is pointing to that table. So let me try to create a new one click on you and this will be the Azure SQL. So let me select that click on continue here under the link service. So we can click on that. So we are having these are the link service. So I am going to use this one as we have already created in the previous videos. And under the table if we can try to search out then this is the table. Now here let me go and call this as lookup and this lookup for the partner revenue. So everything looks good click on OK here. So once we have selected that data set then here we can see the option for the first row only. So this first row only can be used here. If we are not going to select this checkbox then entire data set will be returning. So if you can click on the preview data so it should see all the data. But if we are going to select only the first row then we will have only one row that you can see. So that option we can use and here we can see the query timeout in minutes. So that is one 20 that is OK in our case isolation level is not required as we can see the read committed read uncommuted. So all the isolations levels are available for the SQL. So we are not going to use that then the partitioning option. So that is OK that is none in the user properties in the previous of videos we have already seen about the user property. So I'm not going to cover that in this video. So let me try to publish in and we'll try to execute it. So this lookup activity will be having the output of the table that is a partner revenue and it will return only first row from that table. So now let me try to debug it and we'll see the output of this. So this is running so we can wait this is in queue. Let me refresh it. So this is in queue so it got succeed. So if you can click on this so this is indicating for the output under this output we can see the first row is returning only and these are the columns that you can see. So we are having total six columns and the refresh date that is here. So in the next video we will be going to see how we can store this refresh date from the output of this lookup activity. So guys thank you so much for watching this video. If you have any doubt in the lookup activity then comment your queries in the comment box and 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. See you in the next video.