Search code examples
reporting-servicesssisairflowssrs-2012

Using Apache Airflow to Automate SSRS and/or SSIS

Does it make sense to use Apache Airflow to orchestrate/automate ETLs, and subsequently dispatch reports via SSRS or SSIS? Put another way, I'm wondering if I can wire up an Airflow DAG to SSRS or SSIS. The MsSqlOperator Airflow operator looks like it interfaces with SQL Server, but I can't find any reference to a provider for SSRS or SSIS.

This is meant a very general question, I'm looking for directional guidance, as opposed to code examples (although I'll happily take those). Just wanting to know if I am going to pursue something that isn't going to work or is otherwise a bad idea.

Solution

  • Assuming you're looking to orchestrate the running of a report or running of an SSIS package, yeah sure, that's doable.

    SSIS

    Assuming you're working with the project deployment model, a package run is a few stored procedure calls strung together

    • create_execution creates an instance of an execution
    • set_execution_parameter_value allows you to configure values for an instance of that execution
    • start_execution begins the actual running of the package

    Depending on whether you want the package running in synchronous or asynchronous (default) mode, you might want to set the SYNCHRONIZED bit.

    SSRS

    I'm not sure what you're looking for here but we trigger the proc add_event to kick off a subscription which then gets emailed out but I have seen plenty of questions from people that want to run an ssis package that pulls a report and exports to csv/pdf/etc

    https://businesswintelligence.com/content/26/manually-trigger-ssrs-subscription

    Docs/Learn