Delete Files from Different Blob Containers/Storage by using Controlled Table in Azure Data Factory

Issue: Delete Files from Different Blob Containers/Storage by using a Controlled Table in Azure Data Factory.

In this article, we are going to learn how to delete files from different blob containers/storage by using the controlled table in Azure Data Factory.

How to create a controlled table:

First of all open the Azure data factory portal, then go to the resource group, then go to the server, and then go to your desired database, inside the database click on the query editor and then create a controlled table.

Once our controlled table has been created, go to Azure data factory, then go to the Author, and click on + sign to create a new pipeline, then find and drag the lookup activity, from where we will get the list of the files to be deleted.

Click on the lookup activity, then go to the settings tab and click on the + New button to create a new source dataset.

Select Azure SQL database and then click on continue.

Name the dataset, then select the linked service if you have already created it, otherwise create a new linked service that will be pointing to your Azure SQL Database, then select the controlled table and then click on ok.

You can also use the query instead of a controlled table, if you want to delete the specific files, and also uncheck the box ''first row only'', because we have to delete the entire data.

Once we are done with our lookup activity, find and drag the ForEach loop activity, and connect it with the lookup activity, 

Then go to the settings tab and check the sequential, and then click on the ''Add dynamic content to provide values from the lookup activity.

Click on the Activity outputs it will add the dynamic content and add the ''.value'' right after the .output, and then click on Finish.

 Once we are done with our ForEach loop activity, click on the pencil sign on the ForEach loop and go inside to setup the Delete activity.

Inside the Foreach loop activity, find and drag the Delete activity, then go to the source tab and click on + New button to create a new source dataset.

Select the Azure Blob Storage and then click on Continue.

Name the dataset, then select the linked service, and then provide the file name which you want to delete, or leave it blank if you want to delete all files from the blob storage, and then click on ok.

To create a parameter click on the Open button.

Then go to the parameter tab and click on + New button then name the parameter.

Go to the Connection tab then click on the ''Add dynamic content'' and then select the parameter we created and click ok.

Next, go back to the delete activity, then click on the source, and where we need to provide the Container name or specific Folder name which we want to delete.

Select the wild card and provide the values from ''Add dynamic content''.

Next, click on Debug.

Video Demo: Delete Files from Different Blob Containers/Storage by using Controlled Table in ADF