'How to run SQL Script in Azure Data Factory v2?

There is NO Sql Script activity in Azure Data Factory V2. So how can I create a stored proc, a schema in a database? What are my options?

azureazure-data-factory-2


Solution 1:[1]

  1. There is a preCopyScript property. You could put your script there. It will be executed before each run.

  2. You could use store procedure activity as Summit mentioned.

  3. You could also create a custom activity.

Solution 2:[2]

It is possible using Script activity:

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.

enter image description here

Source: https://docs.microsoft.com/en-us/azure/data-factory/media/transform-data-using-script/inline-script.png

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

Solution 3:[3]

Just to provide another option, I will share how I use to do it.

  1. Create a "Stored Procedure" Activity
  2. On Settings at "Stored procedure name", mark Edit, and type: sp_executesql
  3. Under Stored procedure parameters, add a new parameter called "statement", and in "Value" put your SQL command.

This works with dynamic content as well. Reference about this procedure here.

Regards

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Fang Liu
Solution 2 Lukasz Szozda
Solution 3 Filipe

Related Questions