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:
Supported data stores:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.