airflow-ssis-provider¶

An operator and sensor for starting and tracking SQL Server Integration Services (SSIS) package executions from an Airflow DAG, via the SSISDB catalog’s own stored procedures over an MsSqlHook connection (built on apache-airflow-providers-microsoft-mssql, adapted from mike-hosseini/airflow-sql-server-plugin).

Connection setup¶

For a screenshot walkthrough of creating this connection in the Airflow UI, see SSIS connection.

Uses a standard Airflow mssql connection (MsSqlHook) pointed at the SQL Server instance hosting the SSIS catalog.

Operators and sensors¶

SsisPackageOperator: Builds and runs the SSISDB.catalog.create_execution / set_execution_parameter_value / start_execution sequence for one package, then pushes the resulting execution_id to XCom. Parameters: conn_id, database, folder, project, package, environment (optional SSIS environment reference), logging_level (LoggingLevel enum: none/basic/performance/verbose/ runtime_lineage/custom, default basic), parameters (optional list of QueryParameters(name, value, type), where type is ParameterType.PROJECT/PACKAGE/LOGGING).
SsisPackageSensor: Polls SSISDB.catalog.executions for the execution id pulled from XCom (xcom_task_id) until it reaches a terminal status (Success/Failure/Canceled/Completed/Pending/ Stopping). Raises PackageExecutionError on Failure/ Canceled.

Usage¶

from SSIS_Operator.operators.ssis_package_operator import SsisPackageOperator
from SSIS_Operator.sensors.ssis_package_sensor import SsisPackageSensor
from SSIS_Operator.models.SqlQueryParameters import QueryParameters, ParameterType

run_package = SsisPackageOperator(
    task_id="run_ssis_package",
    conn_id="mssql_default",
    database="SSISDB",
    folder="MyFolder",
    project="MyProject",
    package="MyPackage.dtsx",
    parameters=[
        QueryParameters(name="SourcePath", value="/data/in", type=ParameterType.PACKAGE),
    ],
)

wait_for_package = SsisPackageSensor(
    task_id="wait_for_ssis_package",
    conn_id="mssql_default",
    database="SSISDB",
    xcom_task_id="run_ssis_package",
)

run_package >> wait_for_package