Is there a way In ADF to delete a DB Table of its data if a Pipeline Fails?

Question

Hello!
@Nasreen Akter
@HimanshuSinha-msft

Our Security Architect asks if there is any way to delete a DB Table if (and before) an ADF Pipeline Fails? I googled but can't find much.

Thanks!
Mike Kiser

Answer 1

Hey @Mike Kiser ,
Just to clarify, your ask is to delete a table in case if a pipeline fails correct?
If that is the ask, then definitely it is possible to achieve it.

There are multiple ways:

1. Failure can occur at any activity within a pipeline. So after every activity, in failure row mapping add a lookup activity with the truncate/ Delete table tablename query
   This might be a tedious task as ADF restricts only 40 activities to be created within a pipeline.

2) Create a 2 pipelines within ADF.
1st pipeline would be the pipeline that contains your entire logic
2nd pipeline would contain only 2 activities:
Execute pipeline activity to call the 1st pipeline and lookup activity in case of failure to delete the table

These are the quick approaches of deleting the tables the moment the ADF pipeline failed.

There can be other approaches based on your logging framework as well wherein based on the logs you can determine whether your pipeline failed and then execute the SQL query but those would not be spontaneous as getting the logs of ADF executions might result in delays.

And can you please clarify what do you mean by BEFORE?