Execute SQL Scripts on Snowflake using Azure Data Factory

Azure Data Factory has a new activity introduced this week (around the 10th of March 2022 for you future readers): the Script activity! This is not to be confused with the script task/component of SSIS, which allows you to execute .NET script (C# for most people, or VB if you’re Ben Weissman). No, this task executes SQL, so it’s more akin to the Execute SQL Task of SSIS.

This is great news, because:

1. You previously could only execute SQL scripts through the “Stored procedure” activity. If you don’t have a stored procedure, but you rather wanted to execute a hardcoded script (or a parameterized script through dynamic content and ADF expressions), you had to wrap it in the sp_executesql stored proc (you know, like dynamic SQL). Or you could (ab)use the Lookup component to execute some scripts.
2. But even then, the Stored proc activity only supports Microsoft relational sources (Azure SQL DB, Azure Synapse and SQL Server). For all other sources, you either had to use the Lookup component (feels dirty) or use something else like an Azure Function.

In other words, existing options in ADF were limited, especially if you were using a non-Microsoft database like Snowflake. Luckily, those days are over. Well, not entirely, because for the moment the Script activity supports the Microsoft databases (no not Access), and only Oracle and Snowflake are added to the list. I don’t know if other databases are in the pipeline.

The Script activity actually resembles the SSIS Execute SQL Task a lot. You can just specify a query, like in this example:

You have two options: Query and NonQuery (who comes up with this stuff?). NonQuery is for when you want to execute SQL scripts that don’t return a result set. DDL-style queries, such as TRUNCATE TABLE, CREATE TABLE, ALTER WAREHOUSE etc. But also INSERT, UPDATES, DELETE, MERGE, COPY INTO etc.

The Query option is for when – you already guessed it – the script is returning result sets.

That’s one big advantage over the Lookup activity: you have the ability to have more than one result set. Like the Execute SQL Script, you can also specify parameters.

For Snowflake and Oracle, you have to use question marks as placeholder (just like in SSIS).

If instead of parameterizing values, you need more dynamic SQL, you can just use dynamic content on the script itself. You can compare it as using a variable with expressions in SSIS.

Be sure to check out the official announcement, it has a great comparison between the new Script activity, and the Stored procedure and Lookup activities. If you don’t see the new Script activity yet, it’s possible it’s not available in your region yet, as updates are rolled out gradually.