I need to execute a couple of queries in my pipeline, but I don't want to create a Stored Procedure for each query and then use the Stored Procedure Activity in my pipeline. What is the best solution to execute a single query in my Pipeline?
 |
| Script Activity for SQL queries |
Solution
Untill recently your only option was to use the Stored Procedure activity to execute sp_executesql with your query in the parameter "stmt" of this system stored procedure. Not that bad for one simple query, but annoying for more complex queries or a whole bunch of queries. Especially when you want dynamic queries with values from your pipeline.
 |
| Stored Procedure Activity to execute single query |
Luckily Microsoft just introduced the Script Activity for both ADF and Synapse pipelines. This allows you to execute SQL scripts for SQL Server, Oracle and Snowflake databases.
 |
| Allowed Linked Services for Script Activity |
1) Script type
After selecting the Linked Service you need to specify the Script type: Query or NonQuery. The first option is a SELECT query that returns one or more records. The second option is for your other query types like INSERT, UPDATE or DELETE.
 |
| Script Activity to execute a single query |
This means you could also use this new Script Activity with a SELECT query to replace the Lookup Activity if your lookup source is a database. The Items option in the ForEach Activity is a slightly different: @activity('MyTables').output.resultSets[0].Rows
 |
| output.resultSets[0].Rows instead of output.Value |
2) Query
For the actual query you can enter a query in the textbox, but you could also use the familiar Dynamic Content option. However the Script Activity has an other option called parameters. For SQL Server you need to use @myParameterName in your query. For Oracle and Snowflake you need to use ? instead.
 |
| Using parameters in your query |
You can even add multiple queries separated by ; in that textbox, but you could also add multiple query textboxes which each their own parameters. You can add additional textboxes by pressing the + button above the first textbox. This creates something similar as a notebook with multiple scripts.
 |
| Multiple queries |
3) Logging
Last option is to add extra logging. Either to 'External storage' (Azure Storage Account) or to the 'Activity output'. To log additional information you can use the PRINT statement in your queries.
 |
| Add PRINT to your queries |
When using "Activity output" you can check the output to see the result of your PRINT statements.
 |
| Check the property outputLogs |
Conclusion
In this post we explained how you could use the new 'Script' activity to execute queries in your pipeline. This is a great alternative for executing queries via the Stored Procedure Activity or even the Lookup Activity.
The screenshots in the documentation also show an extra feature (which is not yet available) where you can execute queries that are stored in an Azure Storage Account file. So expect more features for this new Script Activity.
