---
title: Microsoft SQL Server - Artifacts
description: The artifacts of a Microsoft SQL Server generator are sqlcmd scripts plus PowerShell scripts that deploy and load them.
---

[Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts-2.0#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. [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts)

# Microsoft SQL Server - Artifacts

**Applies to**  
Platform: Microsoft SQL Server

The artifacts of a Microsoft SQL Server generator are sqlcmd scripts plus PowerShell scripts that deploy and load them. This article explains what each file does and how the files call each other. Use it to find the script that failed and to see which project settings change the artifacts.

All nine Microsoft SQL Server generator configurations produce this structure. The layer scripts differ per configuration, and only Stage Files adds the folder `Format`.

### Files in the artifacts folder

For example, the artifacts folder of a Stage Files project with Native Load Control:

![Artifacts folder with the folders Format, LoadControl and SQL and the files deploy.ps1, deploy.sql, execute.ps1, execute.sql, README.md, replace\_placeholders.ps1 and replacement\_config.json](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Mar-26-2026-10-26-31-7526-AM.png?width=430&height=368&name=image-png-Mar-26-2026-10-26-31-7526-AM.png)

- `deploy.ps1`: checks that sqlcmd is installed, asks for the server and the login, and runs `deploy.sql` with sqlcmd. See [Microsoft SQL Server - Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment-2.0?hsLang=en).
- `deploy.sql`: runs `SQL\deploy_all_layers.sql`, then `LoadControl\deploy_loadcontrol.sql` and `LoadControl\configure_loadcontrol.sql` when the artifacts contain them, and last `SQL\validate_deployment.sql`.
- `execute.ps1` and `execute.sql`: with Native Load Control, they run `LoadControl\LoadControl_Execution_<project>.sql`, where `<project>` is the project name. See [Microsoft SQL Server - Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control-2.0?hsLang=en).
- `replace_placeholders.ps1` and `replacement_config.json`: replace the placeholders of all files. See [Replace the placeholders in the artifacts](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en).
- `README.md`: the generator and its version, the project, the branch and the build time.
- `SQL\`: `deploy_all_layers.sql`, one script per layer numbered in load order, for example `L010_Stage.sql` and `L020_Raw Vault.sql`, and `validate_deployment.sql`.  
  ![Upper part of the SQL folder of a DataVault and Mart project with deploy\_all\_layers.sql and the layer scripts L010\_Stage.sql to L040\_Mart.sql](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-13-2025-11-51-04-3039-AM.png?width=464&height=211&name=image-png-Oct-13-2025-11-51-04-3039-AM.png)
- `LoadControl\`: the load control scripts or the Airflow DAG, depending on the project settings below.
- `Format\`: Stage Files only. One format file `.fmt` per Stage model object, which the loader uses to read the CSV files.  
  ![Format folder of the artifacts with the format file Person\_EmailAddress.fmt](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Apr-02-2025-12-35-50-5685-PM.png?width=597&height=201&name=image-png-Apr-02-2025-12-35-50-5685-PM.png)
- `ParseError.log`: only when the generation reported validation warnings; it lists them.

### What the deployment scripts create

Each layer script switches to the database of its layer. It creates the database with the recovery model `SIMPLE` and the schema when they are missing. Then it creates the objects of each model object:

- Tables: with the default [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), each table is dropped and created again, empty. With `Generate Drop Table Statements` cleared, only missing tables are created.
- Views: dropped and created again.
- Stored procedures: the loaders, created with `CREATE OR ALTER PROCEDURE`.

`L000_Loadcontrol_Deployment.sql` drops and creates the tables, views and procedures of the load control. The configuration scripts `L010_Stage_Configuration.sql` and following register the load objects and their dependencies.

`SQL\validate_deployment.sql` recompiles every generated view and stored procedure with `sp_refreshsqlmodule`. It prints `Deployment validation succeeded.` or `Deployment validation found <number> errors.` followed by error 50001.

### Settings that change the artifacts

- [LoadControl Options (Project)](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en): Native Load Control adds `execute.ps1`, `execute.sql` and `LoadControl\LoadControl_Execution_<project>.sql`. Airflow adds the DAG `LoadControl\<project>_dag.py`. With Airflow alone, the folder `LoadControl` holds only the DAG.
- [Deploy LoadControl (Project)](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en): true adds `deploy_loadcontrol.sql` and `L000_Loadcontrol_Deployment.sql`.
- [Configure LoadControl (Project)](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en): true adds `configure_loadcontrol.sql`, one `L0x0_<layer>_Configuration.sql` per layer, the execution scripts and the DAG. With Configure LoadControl and Deploy LoadControl both false, the artifacts contain no folder `LoadControl` and no `execute.ps1`.
- [Deployment Options (Project)](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en): decides whether existing tables are dropped.

With Native Load Control and Airflow both selected, the folder `LoadControl` of a DataVault and Mart project holds the load control scripts, the execution script and the DAG:

![LoadControl folder with configure\_loadcontrol.sql, deploy\_loadcontrol.sql, L000\_Loadcontrol\_Deployment.sql, one configuration script per layer from L010\_Stage\_Configuration.sql to L040\_Mart\_Configuration.sql, the execution script and the DAG file](https://knowledge.bigenius-x.com/hs-fs/hubfs/image-png-Oct-13-2025-11-43-29-1009-AM.png?width=504&height=310&name=image-png-Oct-13-2025-11-43-29-1009-AM.png)

In the DAG file name, `<project>` is written in lower case with underscores, for example `stage_mssql_file_dag.py`.

### Example

The Data Vault project `Datavault.Mssql` with Native Load Control and the default settings produces these files:

```
deploy.ps1
deploy.sql
execute.ps1
execute.sql
README.md
replace_placeholders.ps1
replacement_config.json
LoadControl\configure_loadcontrol.sql
LoadControl\deploy_loadcontrol.sql
LoadControl\L000_Loadcontrol_Deployment.sql
LoadControl\L010_Stage_Configuration.sql
LoadControl\L020_Raw Vault_Configuration.sql
LoadControl\L030_Business Vault_Configuration.sql
LoadControl\LoadControl_Execution_Datavault.Mssql.sql
SQL\deploy_all_layers.sql
SQL\L010_Stage.sql
SQL\L020_Raw Vault.sql
SQL\L030_Business Vault.sql
SQL\validate_deployment.sql
```

Its `deploy.sql` reads:

```
:r "SQL\deploy_all_layers.sql"
:r "LoadControl\deploy_loadcontrol.sql"
:r "LoadControl\configure_loadcontrol.sql"
:r "SQL\validate_deployment.sql"
```

### Release differences

- Generator 2.2: `SQL\validate_deployment.sql` is added, see [Generators Release - 2.2.0](https://knowledge.bigenius-x.com/generators-release-2.2.0?hsLang=en).

### Related articles

- Previous: [Generate artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en), [Download generated artifacts](https://knowledge.bigenius-x.com/download-generated-artifacts?hsLang=en)
- Next: [Replace the placeholders in the artifacts](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en)
- Reference: [Microsoft SQL Server - Placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values-2.0?hsLang=en), [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), [Deployment Options (Project)](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en)
- See also: [Microsoft SQL Server - Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment-2.0?hsLang=en), [Understand Load Control](https://knowledge.bigenius-x.com/understand-load-control?hsLang=en), [Troubleshoot generation, deployment and load failures](https://knowledge.bigenius-x.com/troubleshoot-generation-deployment-and-load-failures?hsLang=en), [Generators Release - 2.2.0](https://knowledge.bigenius-x.com/generators-release-2.2.0?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