airflow-ssis-provider
=======================

.. include:: _autodoc/ssis_provider.inc.rst

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
<https://github.com/mike-hosseini/airflow-sql-server-plugin>`_).

Connection setup
-----------------

For a screenshot walkthrough of creating this connection in the Airflow
UI, see :doc:`../connections/ssis`.

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
-----

.. code-block:: python

   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