CODEWITHSUNDEEP
sql-serverazurecloudazure-sql-databaseazure-data-factory
Eldho Baby M
Eldho Baby M

Reputation: 135

How to execute a SQL query in Azure Data Factory

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

Upvotes: 5

Views: 24789

Answers (4)

Ashwin Mohan
Ashwin Mohan

Reputation: 118

They have rolled out the script activity

The script task can be used for the following purposes:

Truncate a table or view in preparation for inserting data. Create, alter, and drop database objects such as tables and views. Re-create fact and dimension tables before loading data into them. Run stored procedures. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set. Save the rowset returned from a query as activity output for downstream consumption.

Script task is present under General tab of Activities.

Ref 1 https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-script

Ref 2 https://techcommunity.microsoft.com/t5/azure-data-factory-blog/execute-sql-statements-using-the-new-script-activity-in-azure/ba-p/3239969

Upvotes: 0

juebro
juebro

Reputation: 51

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).

Upvotes: 5

Uditi
Uditi

Reputation: 1

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

Upvotes: 0

silent
silent

Reputation: 16238

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

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

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

Upvotes: 4

Related Questions