Transform data by using the Script activity in Azure Data Factory or Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

You use data transformation activities in a Data Factory or Synapse pipeline to transform and process raw data into predictions and insights. The Script activity is one of the transformation activities that pipelines support. This article builds on the transform data article, which presents a general overview of data transformation and the supported transformation activities.

Using the script activity, you can execute common operations with Data Manipulation Language (DML), and Data Definition Language (DDL). DML statements like INSERT, UPDATE, DELETE and SELECT let users insert, modify, delete, and retrieve 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.

You can use the Script activity to invoke a SQL script in one of the following data stores in your enterprise or on an Azure virtual machine (VM):

• Azure Database for PostgreSQL (Version 2.0)
• Azure SQL Database
• Azure Synapse Analytics
• SQL Server Database. If you're using SQL Server, install Self-hosted integration runtime on the same machine that hosts the database or on a separate machine that has access to the database. Self-Hosted integration runtime is a component that connects data sources on-premises/on Azure VM with cloud services in a secure and managed way. See the Self-hosted integration runtime article for details.
• Oracle
• Snowflake

The script can contain either a single SQL statement or multiple SQL statements that run sequentially. You can use the Script task for the following purposes:

• Truncate a table 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. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set.
• Save the rowset returned from a query as activity output for downstream consumption.

Syntax details

Here's the JSON format for defining a Script activity:

{ 
   "name": "", 
   "type": "Script", 
   "linkedServiceName": { 
      "referenceName": "", 
      "type": "LinkedServiceReference" 
    }, 
   "typeProperties": { 
      "scripts" : [ 
         { 
            "text": "