--- title: Microsoft SQL Server - Load data - Apache Airflow Load Control description: Load the deployed Microsoft SQL Server solution with Apache Airflow by running the DAG that the generator writes. --- [Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-apache-airflow-load-control#main-content) [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en) [![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/) Open main navigation Close main navigation - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new) - [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal) How can we help you? - There are no suggestions because the search field is empty. 1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en) 2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en) 3. [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow) # Microsoft SQL Server - Load data - Apache Airflow Load Control **Applies to** Platform: Microsoft SQL Server Load the deployed Microsoft SQL Server solution with Apache Airflow by running the DAG that the generator writes. The DAG has one task per loader, runs them in dependency order and calls each loader through the Airflow connection `dwh_azure_sql_conn`. Afterwards each task log shows the row counts of its loader. The DAG is written for Apache Airflow 2. It imports `airflow.operators.python_operator`, `airflow.operators.dummy` and the provider `apache-airflow-providers-microsoft-mssql`. ### Prerequisites - [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) contains `Airflow`, and [Configure LoadControl](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en) is `true`. - The artifacts are [deployed](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment?hsLang=en) without errors. - An Airflow 2 environment with the Microsoft SQL Server provider, for example the one of [Apache Airflow - Load control environment](https://knowledge.bigenius-x.com/apache-airflow-load-control-environment?hsLang=en). - Airflow reaches SQL Server with a SQL Server login. From a Docker container on the same machine, the host is `host.docker.internal`. ### Run the DAG 1. Copy `LoadControl\_dag.py` from the replaced artifacts into the folder `dags` of Airflow. `` is the project name in lower case with underscores, for example `stage_mssql_file`. 2. Create the connection `dwh_azure_sql_conn` of type `mssql`. Each task names its loader with database, schema and procedure name, so one connection serves all layers: ``` docker compose exec airflow-scheduler airflow connections add ` dwh_azure_sql_conn --conn-type mssql ` --conn-host host.docker.internal --conn-port ` --conn-login --conn-password ` --conn-schema ``` Here `` and `` are the SQL Server login, `` is the port of the instance, 1433 by default, and `` is a target database. The command prints `Successfully added `conn_id`=dwh_azure_sql_conn`. In the Airflow UI, the same connection is created under Admin \> Connections with + (Add a new record): Connection Id `dwh_azure_sql_conn`, Connection Type Microsoft SQL Server, and Host, Schema, Login, Password and Port as above. Select Test; it shows `Connection successfully tested` when Airflow reaches the database. Then select Save. ![Airflow menu Admin opened with the entry Connections highlighted](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-36-12-8250-AM.png?width=507&height=168&name=image-png-Jun-06-2024-06-36-12-8250-AM.png) ![List Connection page with the button + highlighted](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-37-07-9860-AM.png?width=503&height=139&name=image-png-Jun-06-2024-06-37-07-9860-AM.png) ![Add Connection form with the fields Connection Id, Connection Type set to Microsoft SQL Server, Description, Host, Schema, Login, Password, Port and Extra, and the buttons Save and Test](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-39-02-9338-AM.png?width=640&height=448&name=image-png-Jun-06-2024-06-39-02-9338-AM.png) ![Buttons Save and Test with Test highlighted](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-52-25-8266-AM.png?width=158&height=49&name=image-png-Jun-06-2024-06-52-25-8266-AM.png) ![Message Connection successfully tested](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-52-00-3336-AM.png?width=640&height=54&name=image-png-Jun-06-2024-06-52-00-3336-AM.png) ![Buttons Save and Test with Save highlighted](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-52-54-6833-AM.png?width=158&height=49&name=image-png-Jun-06-2024-06-52-54-6833-AM.png) 3. Unpause the DAG: `docker compose exec airflow-scheduler airflow dags unpause `. The DAG ID is the file name without `_dag.py`. In the Airflow UI, open DAGs and switch on the toggle left of the DAG. ![Airflow menu bar with the entry DAGs highlighted](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-54-19-7985-AM.png?width=541&height=42&name=image-png-Jun-06-2024-06-54-19-7985-AM.png) ![Airflow DAGs list with the paused DAG stage\_mssql\_file and its toggle switched off](https://knowledge.bigenius-x.com/hs-fs/hubfs/knowledge_base_screenshots/mssql-airflow-dag-list-stage-mssql-file.png?width=460&height=146&name=mssql-airflow-dag-list-stage-mssql-file.png) 4. Trigger it: `docker compose exec airflow-scheduler airflow dags trigger `. The run starts in the state `queued`. In the Airflow UI, select Trigger DAG in the column Actions. ![Actions column of the DAG list with the button Trigger DAG](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Jun-06-2024-06-55-31-9128-AM.png?width=129&height=87&name=image-png-Jun-06-2024-06-55-31-9128-AM.png) Each Airflow task passes its own load timestamp: the time of the Airflow worker clock when the task calls its loader, as text with microseconds and without a time zone offset. The native load control instead uses one load timestamp for all loaders of an execution, see [Microsoft SQL Server - Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control?hsLang=en). ### Check the result `docker compose exec airflow-scheduler airflow dags list-runs -d ` shows the run with the state `success` when all tasks succeeded. The log of each task lists the row counts of its loader, for example `RowCountInserted: 33`. ### Example The DAG `stage_mssql_file` of a Stage Files project has 10 loader tasks between the tasks `begin` and `end`. Each task calls one loader through the same connection: ``` op_kwargs = { 'StoProc': '[DWH].[st].[STG_ST_load_incremental_RawDataLoader]', 'conn_id': 'dwh_azure_sql_conn'} ``` ![Graph of the DAG stage\_mssql\_file: the task begin, ten loader tasks such as STG\_ST\_load\_incremental\_RawDataLoader, and the task end](https://knowledge.bigenius-x.com/hs-fs/hubfs/knowledge_base_screenshots/mssql-airflow-graph-stage-mssql-file.png?width=640&height=350&name=mssql-airflow-graph-stage-mssql-file.png) Here `DWH` and `st` are the example database and Stage schema. The task builds the timestamp with `datetime.now()` and passes it to the parameters `@LoadTimestamp` and `@LoadEffectiveTimestamp` of the loader. ### Failure symptoms and recovery #### The DAG shows an import error for the Microsoft SQL Server provider Cause: The Airflow image does not contain `apache-airflow-providers-microsoft-mssql`. Fix: Add the provider line to `requirements.txt` and rebuild the environment as described in [Apache Airflow - Load control environment](https://knowledge.bigenius-x.com/apache-airflow-load-control-environment?hsLang=en). #### The DAG shows an import error for the Microsoft SQL Server operators Message: `ModuleNotFoundError: No module named 'airflow.providers.microsoft.mssql.operators'` Cause: The Airflow environment installed `apache-airflow-providers-microsoft-mssql` without a version limit and got version 4 or later, which no longer contains `MsSqlOperator`; the DAG imports it. Fix: Pin the provider below version 4 in `requirements.txt`, for example `apache-airflow-providers-microsoft-mssql<4`, and rebuild the environment. #### A task fails because the connection is missing Cause: No Airflow connection has the ID `dwh_azure_sql_conn`. Fix: Create the connection as in step 2. Then rerun the task and its downstream tasks: `docker compose exec airflow-scheduler airflow tasks clear -t "^$" -d -y`. ### Release differences #### Double brackets in the DAG Up to Generator 1.10, the DAG put an extra pair of brackets around each loader name. Since Generator 2.0 it writes single brackets. With a DAG of Generator 1.10 or earlier, open the file in an editor, replace `[[` with `[` and `]]` with `]`, and save it. For example, this line: ``` 'StoProc': '[[DWH].[rv].[RDV_HUB_CreditCard_Hub_Loader]]' ``` becomes: ``` 'StoProc': '[DWH].[rv].[RDV_HUB_CreditCard_Hub_Loader]' ``` ### Related articles - Previous: [Microsoft SQL Server - Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment?hsLang=en), [Apache Airflow - Load control environment](https://knowledge.bigenius-x.com/apache-airflow-load-control-environment?hsLang=en) - Reference: [LoadControl Options (Project)](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en), [Configure LoadControl (Project)](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en), [Microsoft SQL Server - Placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values?hsLang=en) - See also: [Understand Load Control](https://knowledge.bigenius-x.com/understand-load-control?hsLang=en), [Microsoft SQL Server - Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control?hsLang=en), [Microsoft SQL Server - Artifacts](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts?hsLang=en) - [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content) - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines) - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working) - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account) - [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en) - [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en) - [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content) - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration) - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions) - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features) - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects) - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches) - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections) - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview) - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps) - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms) - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping) - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships) - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality) - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes) - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling) - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts) - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings) - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace) - [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content) - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse) - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse) - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks) - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts) - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders) - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment) - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment) - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline) - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment) - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment) - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control) - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow) - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties) - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms) - [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content) - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations) - [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content) - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches) - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases) - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules) - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules) - [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en) - [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content) - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application) - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application) - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators) - [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content) - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs) - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits) - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) - [Customer Portal](https://knowledge.bigenius-x.com/customer-portal) - [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login) [![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com) All rights reserved © 2025, biGENIUS AG