While we have discussed various ways for running custom SQL code in Azure Data Factory in a previous post, recently, a new activity has been added to Azure Data Factory called Script Activity, which provides a more flexible way of running custom SQL scripts.
As shown in the screenshot above, this activity supports execution of custom Data Query Language (DQL) as well as Data Definition Language (DDL) and Data Manipulation Language (DML). For DQL, select the Query radio button and for DML, the Non-query radio button.
Also, it allows users to add parameters to the SQL scripts. These parameters can be used for passing a value to the script (Input parameter) or for capturing the script output (Output parameter).
For the benefit of readers who are not familiar with database scripts. The simplest example of an SQL Query is the SELECT * from table. (Use Query Radio Button)
Examples of DDL – CREATE, DROP, ALTER . (Use Non-Query Radio Button)
Examples of DML – INSERT, UPDATE, MERGE etc. . (Use Non-Query Radio Button)
Limitations: By default, the activity output is limited to 4MB, so if the result set returned by the query is larger than 4 MB then the result set will be truncated. To avoid this limitation, click on the advanced section in the settings and click on the enable logging check box to enable it.
As shown above it will ask for an external storage blob linked service. We can click on + New to create a new linked service. Once the storage blob linked service is selected we have the option to provide a folder path in the storage blob to store the result set. (adetest in the above screenshot).
For a list of supported datasets and a comparison between the other options to run custom SQL statements in Data Factory, please visit the reference link below.
Azure Data Engineering 