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

Microsoft SQL Server - Placeholder values

Applies to
Platform: Microsoft SQL Server

The placeholder values of a Microsoft SQL Server project name the database and schema of each layer, of the load control and of the source systems. Enter them in replacement_config.json before you run replace_placeholders.ps1. Afterwards the deployment and load scripts contain your database and schema names.

The replacement itself is described in Replace the placeholders in the artifacts.

Variable names

Each variable name has the form <project>#<group>#<variable>: the project name in lower case, then a layer or source system in lower case without spaces, for example rawvault for Raw Vault. The load control variables use loadcontrol#loadcontrol#<variable>.

Variables

Layers and load control

Each layer and the load control have three variables. The deployment creates missing databases and schemas.

  • server_name: empty when the database is on the server that deploy.ps1 connects to. On another server, the name of the linked server that reaches it. The scripts address objects as [<server>].[<database>].[<schema>].[<object>]; with an empty value the server part is removed.
  • database_name: the database.
  • schema_name: the schema, prefilled with LC for the load control.

Source systems

A source system that the project reads directly has the same three variables for its source tables. With Stage OpenQuery, server_name of the source system is the name of the linked server. The loader passes database_name, schema_name and the table name to the linked server in the query of the OPENQUERY statement.

CSV folders of Stage Files

Each CSV source system of a Stage Files project has four folder variables:

  • source_directory: the folder with the CSV files to load.
  • format_directory: the folder with the .fmt files from the folder Format of the artifacts.
  • archive_directory and error_directory: the Archive and Error folders of the loader.

SQL Server opens these folders, so the paths must be valid on the SQL Server host. Write each backslash twice in JSON: C:\\data\\source for C:\data\source. The loader adds the closing backslash when it is missing.

Example

The Data Vault project Datavault.Mssql deploys its Stage layer to the example database DWH with the schema st, on the server that deploy.ps1 connects to:

{ "name": "datavault.mssql#stage#server_name",
  "value": "", "secretname": "" },
{ "name": "datavault.mssql#stage#database_name",
  "value": "DWH", "secretname": "" },
{ "name": "datavault.mssql#stage#schema_name",
  "value": "st", "secretname": "" }

After the replacement, a Raw Vault view reads the Stage result view as:

FROM [DWH].[st].[STG_ST_BDC_WRITTEN_OFF_GOODS_Result] AS [s1]

Example files of Generator 2.0:

Related articles