How to execute a SQL query in Azure Data Factory

Question

I create a pipeline in ADF for performing copy activity. My source database is Azure SQL database and Sink is Azure Blob .I want to execute an SQL Query in ADF to delete data from source once data is copied to blob. I am not allowed to use copy or lookup to execute query.Is their any custom way to do this.I need to create a view and have to do some activity.Please help

Answer 1

If you are using data mapping flows, there is a new activity to execute custom SQL scripts:

• Azure Data Factory mapping data flows adds SQL scripts to sink transformation

In a regular pipeline, you probably have to resort to using the Stored Procedure activity:

• Transform data by using the SQL Server Stored Procedure activity in Azure Data Factory

You would have to write the delete logic in the SP, and then invoke the SP from Data Factory.

Answer 2

You can also use the built-in stored procedure sp_executesql, which allows you to provide a random SQL statement as parameter. That way you don't have to implement your own stored procedure.

See more information about this stored procedure on sp_executesql (Transact-SQL).

Answer 3

You can write a stored procedure for deleting the data from source table and call that stored procedure in "Stored procedure" activity after copy activity.

Your data flow will look like:

COPY ACTIVITY -----> STORED PROCEDURE ACTIVITY