---
title: Microsoft SQL Server - Load data - Native Load Control
description: Load the deployed Microsoft SQL Server solution with the native load control by running execute.ps1.
---

[Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-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 a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control)

# Microsoft SQL Server - Load data - Native Load Control

**Applies to**  
Platform: Microsoft SQL Server

Load the deployed Microsoft SQL Server solution with the native load control by running `execute.ps1`. The script builds the execution plan of the project, runs every loader in dependency order and logs each step. Afterwards the procedure `Show_Execution` lists every loader with its result and row counts.

How the load control plans, runs and logs loads is explained in [Understand Load Control](https://knowledge.bigenius-x.com/understand-load-control?hsLang=en).

### Prerequisites

- [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) contains `Native Load Control`, 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, including the load control.
- PowerShell 7 and sqlcmd are installed.

### Load the data

1. Open PowerShell 7 in the artifacts folder, the folder that contains `execute.ps1`.
2. Run `.\execute.ps1`.  
   ![PowerShell after .\\execute.ps1 with the prompt Please specify the server or instance name](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Feb-19-2024-01-59-40-4173-PM.png?width=415&height=92&name=image-png-Feb-19-2024-01-59-40-4173-PM.png)
3. Enter the server, the login and the password as for `deploy.ps1`. Leave the server empty for `localhost` and the login empty for Windows authentication. The script returns when the execution has ended.  
   ![execute.ps1 with the server prompt left empty; the script answers -\> using server name localhost](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-04-2023-03-10-52-3360-PM.png?width=349&height=54&name=image-png-Oct-04-2023-03-10-52-3360-PM.png)  
   ![User name prompt of execute.ps1 with a login entered](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-04-2023-03-10-40-8633-PM.png?width=342&height=71&name=image-png-Oct-04-2023-03-10-40-8633-PM.png)
4. Read the last message, as described in [Check the result](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control#check-the-result).

The script runs `LoadControl\LoadControl_Execution_<project>.sql`, where `<project>` is the project name. It builds the execution plan `DEV_<project>`, adds all load objects of the load configuration `<project>` and starts the execution. All loaders of one execution get the same load timestamp, the time at which the execution was created.

### Check the result

The load succeeded when the last message reads `Execution plan [DEV_<project>] completed by execution [<number>].` Otherwise it ends with `with <number> FAILED or DISABLED load steps.`

![End of the execute.ps1 output: the last step finished, Execution plan completed by execution \[1\], then Finished](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Feb-19-2024-02-01-15-9437-PM.png?width=654&height=103&name=image-png-Feb-19-2024-02-01-15-9437-PM.png)

To see every step, run in the load control database, where `LC` is the load control schema:

```
EXEC [LC].[Show_Execution] @ExecutionPlan = N'DEV_<project>';
```

Each step shows its `LoadObject`, `ExecutionResult`, `RowCountInserted` and `ErrorDescription`. More queries are in [Monitor Load Control](https://knowledge.bigenius-x.com/monitor-load-control?hsLang=en).

![Show\_Execution result with one row per step and the columns Execution\_Step\_ID, LoadObject\_ID, LoadObject and RowCountInserted](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-04-2023-03-15-49-1966-PM.png?width=670&height=146&name=image-png-Oct-04-2023-03-15-49-1966-PM.png)

### Example

The Data Vault project `Datavault.Mssql` has 103 load objects. Its first load on a local SQL Server ran all 103 steps successfully and ended with:

```
Process_Execution_Step [87]: Execution [1] step [102] finished
  sucessful: INS 130, UPD 0, DEL 0, WRN 0, ERR 0, MSG (null)
...
Prepare_Execution_Step [87]: Execution [1] has no more selectable steps.
Process_Execution [87]: Execution [1] finished.
Start_Execution [87]: Execution plan [DEV_Datavault.Mssql] completed
  by execution [1].
```

The number in square brackets after the procedure name is the session ID; long lines are wrapped here.

### Run loaders in parallel

To load with several workers, start `execute.ps1` or the execution script in more sessions while the first execution runs. Each further session logs `Execution plan [DEV_<project>] has active execution [<number>]. Adding worker.` and runs steps of the same execution. A session that starts after the execution has ended starts a new execution, which loads all objects again.

In `Show_Execution`, `ExecutionProcessSPID` shows the session that ran each step:

![Show\_Execution result with the columns LoadObject, RowCountInserted and ExecutionProcessSPID; steps 10 to 12 ran in the sessions 54, 53 and 68](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Aug-01-2024-09-19-46-3867-AM.png?width=670&height=75&name=image-png-Aug-01-2024-09-19-46-3867-AM.png)

`LoadControl_Execution_<project>.sql` also contains commented calls for other plans: a plan with the Stage layer only, removing load objects from a plan, removing a plan, and stopping or resuming an execution. The procedures are described in [Plan Load Control](https://knowledge.bigenius-x.com/plan-load-control?hsLang=en) and [Execute Load Control](https://knowledge.bigenius-x.com/execute-load-control?hsLang=en).

### Load several projects with one load control

Several projects, for example a Stage Files project and a Dimensional project that uses it as a linked project, can share one load control:

1. Keep [Deploy LoadControl](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en) `true` in the project that deploys the load control, and set it to `false` in the others.
2. Enter the same `loadcontrol#loadcontrol#` values in the replacement configuration of every project.
3. Deploy the load control project first, then the others. Each project registers its load configuration under its project name.
4. Add the dependencies between the projects. The statement below makes every load object of the first layers of project B depend on every load object of the last layers of project A.
5. Build one execution plan with the load configurations of both projects and start it.

Deploying a project with Deploy LoadControl set to true recreates the load control tables and removes the configuration of the other projects, your dependencies, execution plans and execution history. Deploy the load control with one project only and set Deploy LoadControl to false in the others. A deployment of project B removes its dependencies on project A, so run the dependency statement again after it. After a deployment of the load control, run both statements again.

Dependencies for step 4, where `<entry schemas of B>` and `<exit schemas of A>` are quoted schema lists such as `N'cl'`, and `<user name>` marks the rows:

```
INSERT INTO [LC].[LoadConfig_LoadObject_Dependency]
  ([FK_LoadObject_ID], [FK_DependentOnLoadObject_ID], [AllowDisable],
   [InsertedAt], [InsertedBy], [UpdatedAt], [UpdatedBy])
SELECT obj.ID, objdep.ID, 1, GETDATE(), N'<user name>',
       GETDATE(), N'<user name>'
FROM [LC].[LoadConfig_LoadObject] obj
CROSS JOIN [LC].[LoadConfig_LoadObject] objdep
WHERE obj.SchemaName IN (<entry schemas of B>)
  AND objdep.SchemaName IN (<exit schemas of A>);
```

When the dependencies already exist, the statement stops with `Msg 2627` (`UQ_LoadConfig_LoadObject_Dependency`) and adds no rows.

Execution plan for step 5, where `<project A>` and `<project B>` are the project names:

```
EXEC [LC].[Build_ExecutionPlan]
  @ExecutionPlan = N'LoadAllProjects';
EXEC [LC].[Build_ExecutionPlan_AddLoadObjects]
  @ExecutionPlan = N'LoadAllProjects', @LoadConfig = N'<project A>';
EXEC [LC].[Build_ExecutionPlan_AddLoadObjects]
  @ExecutionPlan = N'LoadAllProjects', @LoadConfig = N'<project B>';
EXEC [LC].[Start_Execution] @ExecutionPlan = N'LoadAllProjects';
```

### Failure symptoms and recovery

#### Some loaders failed or were disabled

Message: `completed by execution [<number>] with <number> FAILED or DISABLED load steps.`  
Cause: A loader failed. The load control disabled the loaders that depend on it, with the result `Disabled due to error of method <loader>`.  
Fix: Run `Show_Execution` and read `ErrorDescription` of the failed step. Correct the cause and run `.\execute.ps1` again; it starts a new execution that loads all objects.

#### A Cached table is empty after its loader failed

Message: the error of the loader, for example `Cannot insert the value NULL into column '<column name>'`  
Cause: A loader of a model object with Implementation Type `Cached` empties its table in a separate transaction before it inserts. When the insert fails, the table stays empty until the loader runs again successfully.  
Fix: Correct the cause and load again.

For failures in other steps, see [Troubleshoot generation, deployment and load failures](https://knowledge.bigenius-x.com/troubleshoot-generation-deployment-and-load-failures?hsLang=en).

### Release differences

#### Project names with spaces

In Generator 1.8, `execute.sql` called the execution script without quotes, so it failed for a project name with spaces. Since Generator 1.9 the path is quoted. With artifacts of Generator 1.8, open `execute.sql` and put the path in quotes, for example:

```
:r ".\LoadControl\LoadControl_Execution_My Project.sql"
GO
```

### Related articles

- Previous: [Microsoft SQL Server - Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment?hsLang=en)
- Reference: [LoadControl Options (Project)](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en), [Deploy LoadControl (Project)](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en), [Configure LoadControl (Project)](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en), [Implementation Type (Model Object)](https://knowledge.bigenius-x.com/implementation-type-model-object?hsLang=en)
- See also: [Understand Load Control](https://knowledge.bigenius-x.com/understand-load-control?hsLang=en), [Native - Load control environment](https://knowledge.bigenius-x.com/native-load-control-environment?hsLang=en), [Configure Load Control](https://knowledge.bigenius-x.com/configure-load-control?hsLang=en), [Plan Load Control](https://knowledge.bigenius-x.com/plan-load-control?hsLang=en), [Execute Load Control](https://knowledge.bigenius-x.com/execute-load-control?hsLang=en), [Monitor Load Control](https://knowledge.bigenius-x.com/monitor-load-control?hsLang=en), [Microsoft SQL Server - Load data - Apache Airflow Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-apache-airflow-load-control?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)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG