Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

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

  • 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.
  • 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.
  • replace_placeholders.ps1 and replacement_config.json: replace the placeholders of all files. See Replace the placeholders in the artifacts.
  • 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
  • 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
  • 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, 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): 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): true adds deploy_loadcontrol.sql and L000_Loadcontrol_Deployment.sql.
  • Configure LoadControl (Project): 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): 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

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

Related articles