CODEWITHSUNDEEP
azureazure-data-factory
AlexB
AlexB

Reputation: 4528

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?

Upvotes: 21

Views: 41382

Answers (4)

SouravA
SouravA

Reputation: 5243

I agree that the absence of something like "Execute SQL task" of SSIS is bumming. I normally use a "LookUp" activity as I don't like to create procedures for simple tasks which could be a one liner command. The only constraint of lookup activity is that it needs some sort of output result set. So what I do when I need to run an update statement is something like this:

enter image description here

The dummy query select 0 id "feeds" the data to the Lookup activity and thus it is able to run the command set on database.

EDIT: At the time of first writing this, I found no other way than this and the answer came out of my own personal experience. However, Microsoft did add a "Script Activity" (refer Lukasz's answer) later which is surely the better way to go right now.

Upvotes: 28

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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://learn.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

Upvotes: 9

Filipe
Filipe

Reputation: 116

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

Upvotes: 1

Fang Liu
Fang Liu

Reputation: 2363

  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.

Upvotes: 9

Related Questions