 Hey guys welcome to SSU Unitex, so see this side and today we are going to see about the stored procedure activity. So what is the stored procedure activity? You can use the data transformation activity in the data factory or SNAPs pipeline to transform and process raw data into the predictions and insights. The stored procedure activity is one of the transformation activity that pipeline supports. For example, in the input we are getting the raw data and we are loading that inside the SQL Server table and if you want to do the some cleanup on that data, so for that we have written the stored procedure. So in the same pipeline, first we can load that raw data into the SQL Server table and on the same page we can execute the stored procedure that we have written for the cleanup activity. So that stored procedure will take the input from your SQL Server table and loading into the final table. So on the same pipeline we can achieve this by using the stored procedure activity. Next this article build on the transform data article which present the general overview of the data transformation and the support transformation activities. Next you can use the stored procedure activity to invoke the stored procedure in one of the following data store in your enterprise on an Azure VM. First is the Azure SQL Database that we can use as your SNAPs analytics that we can also use and third is the SQL Server database. So if your destination is any one of these so we can use this stored procedure activity. So if you are using SQL Server then you need to install the self hosted IR that we have already discussed in the previous videos. Now go to on the browser and we will discuss about the requirement. So here as we can see we are under this blob storage. So under this blob storage here we are having these set of files. So the requirement is if your file is not available. For example if your employee file is not available and we want to load the data from this employee table on that scenario your pipeline will fail. So we can use the stored procedure activity and by using the stored procedure activity we can check if file is not there then we can insert the log inside the SQL Server table. So go to the SQL Server and here we are having one of this table that is the fail pipeline details let me try to execute it. So it is having four columns and as of now it does not have any data. So we have written one of the stored procedure and this stored procedure is taking four input parameters like error code, pipeline, error description and fail date and time. And simply we are inserting into this table which is the fail pipeline detail all these variable values. So this is we have already written inside the SQL Server. So now go to on the browser and we will try to implement that practical. Go to on the data factory and here let me try to add a new pipeline and let me quickly call this pipeline as stored procedure activity. So before going to start with the stored procedure activity if you haven't watched the get metadata activity and if condition activity then I would strongly recommend to watch those videos because we are going to use those two in this video. So first we are required to use the get metadata activity. So this get metadata activity will be going to get all these file names from here. So let me go and try to create a new data set and this data set for the employed only. So let me call this as blob storage as this is the source file is the delimited. Now here link service we have already created the link service but I am going to create a new one. Here we can go and we need to select the subscription then we need to select the storage account name that is the SSC testing. Now we can click on create so it will be created a new link service. Now we can go on the containers here we can see the file path so container directory and file. So we can browse under the input here we have this employee.txt file. Let me click on ok everything is ok click on ok. So we have done with this data set part now here we can see the field list. So under the field list we are going to check whether the file is available or not so we have to use the exist so that we can select. Now let me try to click on debug so this time as we have already seen file is there so the value of this exist will be true. So let me click on this output and as we can see exist value is true. Now next we need to use the if condition activity. So what if condition activity will do it will be checking this exist value is true or not. So if that value is true then we want to copy the data if that value is not true then we want to execute the stop procedure. So go to on the activities and under the expression here we need to set this and let me check this with the true value or not. So we can use the bool like this let me click on ok. So if file is there then it will be executing true block if file is not there then it will be executing false block. So as of now in the true block I am not going to do anything let me quickly go in the false one first here we need to execute the stop procedure. So we can use the stop procedure activity and now go to other settings and under this link service we need to select the link service so that is the we have already created so I am going to use the same. Here we can see the stop procedure name as we have already seen we have created this stop procedure. So we need to select this from this drop down. So as of now we have only this one stop procedure so that is why only one is here that we select this now we can see the import. So once we click on import so it will be importing all the parameters. So as here we can see the error code error description field daytime pipeline so these parameters it is expecting. So for the error code we can specify any value for example I am going to specify error 101 and here the error description so in the error description we can call this as the file is not available at the source. So this will be your error description then we can see the field date and time so this will be the current time when this pipeline will be executing. So for that we can go in the function and here we can see the date time we should see UTC function is here so let me try to get it the UTC now so this one is there so that is ok click on ok. So we have done for the field date and time same thing we need to do for the pipeline. So let me go on the system variable this time data factory name so we do not want data factory name we want pipeline name. So pipeline name is here so we can select the pipeline name and click on ok. So everything is done and if we will be going to execute so it will not go in the false block because your source file is there so let me try to develop it I will show you and then after we will be going to delete the EMP file from the Azure Blob stories so here as we can see it's in queue it's executing in progress so it is executed successfully and here the exist value is true so if the exist value is true then it will go in the if condition and here it will be go under the true so we don't have anything in the true that's why it written from here now go back to the Azure Blob stories and try to delete this file click on ok. So it is deleting the file from the Blob stories so file is deleted now this time we want to execute so your exist will be false because file is not there so here we can see the get metadata output so exist value is false because your file is not there so on that scenario it will be going to go under the if condition and this is false so it will go in under the false and under the false we have one of the store procedure activity so that's why you can see so it is executed successfully now go back to the ssms and let me check the table so previously we did not have the data but now we can see error code error description and the pipeline so this is the pipeline name and the failed date and time so this is the current date and time so this is the use of the store procedure activity so you can use the store procedure activity as per your requirement for the cleaning of the raw data or doing whatever because this is very state for our activity if you still have any doubt then you can drop your questions in the comment box I will try to respond there thank you so much for watching this video if you really 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 see you in the next video