Hare Krishna !
📋 Use Case : -
I have two tables employee table and employee resign table . i want to delete those emp id from employee table who have resigned . i want to do it with the help of azure data factory
➡ Step 1: Set Up Linked Service
1. Create Linked Service to Azure SQL Database in Azure Data Factory:
- Go to Manage in ADF.
- Under Linked Services, create a new linked service for your Azure SQL Database, providing the necessary connection details (server name, database name, credentials).
➡ Step 2: Create the Pipeline
1. Create a new pipeline in Azure Data Factory.
➡ Step 3: Add Lookup Activity
1. Add a Lookup activity to fetch the `emp_id` from the `employee_resign` table:
- Go to the Activities pane, drag Lookup into the pipeline.
- Configure the Lookup activity to use the linked service and point it to the employee_resign table.
- Use the query:
SELECT emp_id FROM employee_resign
- This will retrieve the employee IDs that need to be deleted.
➡ Step 4: Add ForEach Activity
1. Add ForEach activity:
- Drag the ForEach activity into the pipeline and connect it to the Lookup activity.
- In the ForEach settings, select the output of the Lookup activity (`@activity('LookupActivityName').output.value`) to loop through the `emp_id` values.
➡ Step 5: Add Delete Activity
1. Inside the ForEach activity, add a Stored Procedure activity or a Delete activity to delete each employee:
- For Stored Procedure
- Create a stored procedure in the Azure SQL Database to handle the deletion
CREATE PROCEDURE DeleteResignedEmployee (@emp_id INT)
AS
BEGIN
DELETE FROM employee WHERE emp_id = @emp_id
END
- Configure the Stored Procedure activity in ADF to use this procedure, passing `@item().emp_id` as a parameter.
- For Delete activity
- Use dynamic SQL in the query to delete employees:
DELETE FROM employee WHERE emp_id = @item().emp_id
➡ Step 6: Test and Run the Pipeline
- Test the pipeline by running it. It should delete all employees listed in the `employee_resign` table from the `employee` table.
This approach ensures that resigned employees are dynamically deleted using a loop in Azure Data Factory.
#AzureDataFactory #DataEngineering #AzureSQL #CloudComputing #DataAutomation #ETL #SQL #CloudData #DataIntegration #TechTips #MicrosoftAzure #Azure #DataPipelines #TechInnovation #DataTransformation