We are introducing a Script activity in pipelines that provide the ability to execute single or multiple SQL statements.
Using the script activity, you 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
The below table compares Script activity with existing pipeline activities like Lookup, SProc and can provide guidance on when to choose what.
|
|
Script Activity |
Lookup Activity |
SProc Activity |
|
Supported data source |
Database (SQL family, Snowflake, Oracle) |
all data sources |
SQL family |
|
Supported Operations |
Read / Modify |
Read* |
Modify |
|
Multiple query support |
Yes |
No** |
Yes*** |
|
Query parameter support |
Input / Output |
Not supported |
Input |
|
Output Result set support |
One or more |
One |
No |
|
Output query logs (PRINT) |
Yes |
No |
No |
|
Integrated CICD (ADF) |
Yes |
Yes |
No |
* Lookup activity does not block modify operations, but it is not recommended to use lookup activity to modify data.
** Multiple queries can be executed successfully in lookup activity for some connectors but cannot retrieve full result.
*** For SProc activity, ‘multiple queries’ means one stored procedure with multiple queries in it. Cannot support executing more than one stored procedure in a single activity.
Note: Be careful when returning result sets, since the activity output is limited to 5000 row/ 2MB size. If you are logging through your SQL script (Print statements), you can work around the limit by choosing your Storage account for logging.
For more details, refer script activity documentation.
Posted at https://sl.advdat.com/3hv8Bc7https://sl.advdat.com/3hv8Bc7
