How to Integrate SQL Server with Airflow in PostgreSQL

Galaxy Glossary

How do I connect Microsoft SQL Server to Airflow?

Set up an Airflow connection and tasks that run Microsoft SQL Server queries inside your DAGs.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why integrate SQL Server with Airflow?

Automating SQL Server jobs in Airflow centralizes scheduling, logging, and monitoring, eliminating Windows Task Scheduler or SQL Agent silos.

What prerequisites are required?

Install Apache Airflow ≥2.6, Python ≥3.8, network access to SQL Server, and an account with least-privilege SELECT/INSERT rights on your ecommerce tables.

How do I install the SQL Server provider?

Run pip install apache-airflow-providers-microsoft-mssql and restart the Airflow webserver and scheduler.

How do I add a SQL Server connection?

In the Airflow UI navigate to Connections → +.Choose “Microsoft SQL Server”. Fill Host, Schema (database), Login, Password, Port (default 1433). Save as mssql_ecommerce.

Connection string alternative

Add to airflow.cfg: export AIRFLOW_CONN_MSSQL_ECOMMERCE='mssql+pyodbc://user:pass@server:1433/db?driver=ODBC+Driver+17+for+SQL+Server'

What is the MsSqlOperator syntax?

Use MsSqlOperator for ad-hoc SQL, MsSqlHook for Python logic, and MsSqlSensor for data-arrival checks.Details below.

How do I build an ecommerce DAG example?

The snippet below extracts daily revenue from Orders and writes to an audit table.

How do I pass parameters safely?

Parameterize queries with Jinja templates or the parameters argument to avoid string concatenation and SQL injection.

Best practices for production?

Reuse one mssql_conn_id per environment, upgrade the provider with Airflow, enable retries, and store credentials in a secrets backend.

What common mistakes should I avoid?

Do not use autocommit=True with multi-statement transactions unless you wrap them in explicit BEGIN/COMMIT.Avoid ";" inside f-strings—it breaks the operator’s templating.

Why How to Integrate SQL Server with Airflow in PostgreSQL is important

How to Integrate SQL Server with Airflow in PostgreSQL Example Usage


from airflow import DAG
from airflow.providers.microsoft.mssql.operators.mssql import MsSqlOperator
from datetime import datetime

default_args = {"retries": 2, "retry_delay": 300}

dag = DAG(
    dag_id="daily_sqlserver_revenue",
    start_date=datetime(2023, 1, 1),
    schedule_interval="0 2 * * *",
    default_args=default_args,
    catchup=False,
)

calc_revenue = MsSqlOperator(
    task_id="calc_revenue",
    mssql_conn_id="mssql_ecommerce",
    sql="""
        INSERT INTO RevenueAudit(customer_id, revenue, audit_date)
        SELECT c.id, SUM(oi.quantity * p.price), CAST(GETDATE() AS DATE)
        FROM Customers c
        JOIN Orders o ON o.customer_id = c.id
        JOIN OrderItems oi ON oi.order_id = o.id
        JOIN Products p ON p.id = oi.product_id
        WHERE o.order_date = CAST(GETDATE() AS DATE)
        GROUP BY c.id;
    """,
    autocommit=True,
    dag=dag,
)

How to Integrate SQL Server with Airflow in PostgreSQL Syntax


MsSqlOperator(
    task_id='run_sql_server_query',
    mssql_conn_id='mssql_ecommerce',
    sql='''\n        SELECT c.name, SUM(o.total_amount) AS revenue\n        FROM Customers c\n        JOIN Orders o ON o.customer_id = c.id\n        WHERE o.order_date = CAST(GETDATE() AS DATE)\n        GROUP BY c.name;\n    ''',
    parameters=None,           # dict or tuple for parametrized SQL
    autocommit=False,          # True commits each statement automatically
    database='ecom_db',        # overrides schema in connection if needed
)

Common Mistakes

Using ODBC driver mismatch: Airflow needs the same driver version installed in the scheduler image; mismatched versions raise “Data source name not found” errors. Fix by installing the exact ODBC Driver 17 in Dockerfile and <code>apt-get update && apt-get install -y msodbcsql17</code>.
Hard-coding credentials in DAG files: this leaks secrets in source control. Store them in Airflow Connections backed by AWS Secrets Manager, or use environment variables like <code>AIRFLOW_CONN_MSSQL_ECOMMERCE</code>.