In Azure Data Factory, a new activity has been introduced this year which is the Script activity. This is not the same as the script component of SSIS, which allows us to run .NET scripts. This task executes SQL script, so it is similar to the SSIS SQL Execute Task. This new activity in pipelines provides us the ability to execute single or multiple SQL statements.
This is a nice feature, for the following reasons:
- We previously could only execute SQL scripts through the “Stored procedure” activity or we could use the Lookup component to execute some scripts.
- But even then, the stored procedure activity only supports Microsoft relational sources (Azure SQL DB, Azure Synapse and SQL Server). For all other sources, we either had to use the Lookup component or use something else like an Azure Function.
Luckily, those days are over because with the script activity, we can execute common operations with Data Manipulation Language (DML), and Data Definition Language (DDL). DML statements like SELECT, UPDATE, and INSERT let users retrieve, store, modify, delete, insert and update data in the database. DDL statements like CREATE, ALTER, and DROP allow a database manager to create, modify, and remove database objects such as tables, indexes, and users.
Script activity can be used for a variety of 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.
- Use the rowset/ resultset returned from a query in a downstream activity.
Supported data stores:
- Azure SQL Database
- Azure Synapse Analytics
- SQL Server Database
- Oracle
- Snowflake