Airflow - run sql procedures (SQL Server)

Question

I am triggering the task manually from the UI and it shows the task as success but nothing happens in the database. Basically I am calling a simple procedure (with no params) that copy values from the staging table to the main and delete the contents in the staging table.

from airflow import DAG
from airflow.operators.mssql_operator import MsSqlOperator
from datetime import datetime

dag = DAG("sql_proc_0", "Testing running of SQL procedures",
          schedule_interval = None, catchup = False,
          start_date = datetime(2019, 1, 1))

# [dbo].[LoadData] is the name of the procedure
sql_command = """ 
EXECUTE [dbo].[LoadData] 
"""

task = MsSqlOperator(task_id = 'run_test_proc', mssql_conn_id = 'mssql_azure_test',
                     sql = sql_command, dag = dag, database = 'TestDB')

Javiar Sandra Javiar Sandra · Accepted Answer · 2019-02-20T01:11:19

Basically auto_commit=False is set by default. When it is set to True, it works (it took me 2 hours to figure this out and after posting it in SO!)

task = MsSqlOperator(task_id='run_test_proc',mssql_conn_id='mssql_azure_test',
                     sql=sql_command,dag=dag,
                     database='TestDB', 
                     auto_commit=True)

Airflow - run sql procedures (SQL Server)

1 Answers