Wednesday, March 2, 2022

Execute SQL statements using the new 'Script' activity in Azure Data Factory and Synapse Pipelines

We are introducing a Script activity in pipelines that provide the ability to execute single or multiple SQL statements.  

 

AbhishekNarain_0-1646209643679.png

 

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