Azure Data Factory has many options for scripting, fetching, and manipulating data. But sometimes we need to do something as basic as executing an operation on the database that does not return data, for example, you need to execute an update or an insert operation, you cannot do this using a Lookup Activity because the process will fail with the following error:
The specified SQL Query is not valid. It could be caused by that the query doesn’t return any data
Also in this scenario, you can’t create a stored procedure on the database to do this.
1) Create a new Stored Procedure Activity.
2) Select the linked service that you want to use to execute the query, then you need to use the stored procedure sp_executesql. To select a custom stored procedure, activate the Edit check below the Stored procedure name text input.
3) Create a new stored procedure parameter using the “+ New” button, then you need to insert stmt into the Name field and select the Type String.
4) Finally, you need to paste or write your script into the Value text box.
With these simple steps, you can run any scripts on the database using Data Factory without the creation of a stored procedure.
Show Comments