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

Replace the placeholders in the artifacts

Applies to
Platform: Microsoft SQL Server · Snowflake · Databricks · Fabric Lakehouse · Fabric Warehouse · Oracle

Generated artifacts contain placeholders for the names and settings of your target environment. Replace them with the replace_placeholders.ps1 script after you download the artifacts and before you deploy them. Afterwards, the deployment files contain the values you entered in replacement_config.json.

Snowflake dbt and Databricks dbt artifacts contain no replacement script and no replacement_config.json. Their placeholders are <placeholder> values in dbt_project.yml.

Prerequisites

Replace the placeholders

  1. Unzip the downloaded artifacts into a folder. It contains replace_placeholders.ps1 and replacement_config.json.
  2. Open replacement_config.json in a text editor. It lists one variable per placeholder for each environment; the generated file has one environment, DEV. Each variable name has three parts separated by #: the project, the layer or source system, and the variable, for example datastore.mssql#stage#database_name.
  3. Enter each value in the value field of its variable and save the file. The script reads only value; it does not read secretname or secretprovider.
  4. Start PowerShell 7 in the artifacts folder, for example by typing pwsh in the address bar of File Explorer.
  5. Run .\replace_placeholders.ps1. The script prints the folders and the environment it uses, then one -> working on file line per file. It ends with Finished - <number> files processed, with the number of files it changed.

The script replaces the placeholders in place, in every .sql, .txt, .py, .json and .ipynb file of the folder and its subfolders.

Example

A SQL Server project Datastore.Mssql reads the source system bfmmssql from database SRC, schema dbo, on the server it is deployed to. All names are example values. The server name stays empty:

{
  "name": "datastore.mssql#bfmmssql#server_name",
  "value": "",
  "secretname": ""
},
{
  "name": "datastore.mssql#bfmmssql#database_name",
  "value": "SRC",
  "secretname": ""
},
{
  "name": "datastore.mssql#bfmmssql#schema_name",
  "value": "dbo",
  "secretname": ""
}

Before the replacement, SQL\L010_Stage.sql contains this line, wrapped here:

FROM [{datastore.mssql#bfmmssql#server_name}].
[{datastore.mssql#bfmmssql#database_name}].
[{datastore.mssql#bfmmssql#schema_name}].[BDC_CUSTOMER] AS [s1]

Afterwards, it reads:

FROM [SRC].[dbo].[BDC_CUSTOMER] AS [s1]

Check the result

To find placeholders that are left, run in the PowerShell window of step 4:

Get-ChildItem -Recurse -Include *.sql, *.txt, *.py, *.json, *.ipynb |
    Select-String -Pattern '\{[^{}]+#[^{}]+#[^{}]+\}' |
    Select-Object -First 5

No output means every placeholder was replaced. A listed line shows a placeholder without a variable in replacement_config.json. With -ArtifactOutputPath, run the check in the output folder.

Script parameters

The current folder is the folder PowerShell was started in; relative paths are resolved against it. -Help lists the parameters.

  • -ReplacementConfigPath "<file>": the configuration file to use. Default: replacement_config.json in the current folder.
  • -ArtifactInputPath "<folder>": the folder with the files to change. Default: the current folder.
  • -ArtifactOutputPath "<folder>": an existing folder for a replaced copy of all files; the input folder stays unchanged. Default: the current folder, whose files are overwritten.
  • -ArtifactBackupPath "<folder>": an existing folder for a copy of all original files. Each run creates a subfolder named after its start time, for example 20261023150001146. Default: no backup.
  • -EnvironmentName "<environment name>": the name of the environment of the configuration file to apply. Default: the first environment.
  • -Help: shows the parameters and changes no file.

This call uses a configuration file in another folder:

.\replace_placeholders.ps1 `
    -ReplacementConfigPath "C:\Config files\replacement_config_dev.json"

Use several environments

To keep the values of several target environments in one file, copy the DEV object in environments, rename the copy, for example to PROD, and enter its values:

{
  "environments": [
    { "name": "DEV", "projects": [ ... ] },
    { "name": "PROD", "projects": [ ... ] }
  ],
  "secretprovider": {}
}

Select it when you run the script; the script prints -> using environment PROD.

.\replace_placeholders.ps1 -EnvironmentName "PROD"

Empty values

An empty value removes the placeholder with its brackets or quotes. Leave a server, database or schema name empty when the objects do not need it, as in the example. What happens to the dot after an empty name depends on the platform:

  • Microsoft SQL Server and Oracle: removed when the names before it are empty too, kept after a filled name. A server SRV with an empty database gives [SRV]..[stage].[STG_ST_bdc_customer].
  • Snowflake, Databricks, Fabric Lakehouse and Fabric Warehouse: always removed. A database DWH with an empty schema gives DWH.STG_ST_BDC_CUSTOMER.

Failure symptoms and recovery

In the messages, <path>, <environment name> and <variable name> stand for the folder, environment and variable the script reports.

The configuration file is not found

Message: Replacement configuration file <path>\replacement_config.json not found
Cause: The script looks for the file in the folder PowerShell was started in. Changing the folder with cd does not change it.
Fix: Close PowerShell, start PowerShell 7 in the artifacts folder (step 4) and run the script again.

PowerShell cannot run the script because of its version

Message: The script 'replace_placeholders.ps1' cannot be run because it contained a "#requires" statement for Windows PowerShell 7.0.
Cause: The script was started in Windows PowerShell 5.1.
Fix: Start PowerShell 7 with pwsh and run the script again.

The environment is not found

Message: Environment <environment name> not found in replacement configuration file
Cause: The value of -EnvironmentName matches no name in environments.
Fix: Use a name from the configuration file.

The output or backup folder is not found

Message: Output folder <path> not found or Backup folder <path> not found
Cause: The script does not create these folders.
Fix: Create the folder, then run the script.

A variable name has the wrong format

Message: Variable name <variable name> is not in the expected format 'project#layer#variable'
Cause: Microsoft SQL Server and Oracle only: a name in replacement_config.json was changed.
Fix: Restore the name from the downloaded file.

A value is wrong after the replacement

Cause: The script replaced the placeholders, so a second run on the same files finds none.
Fix: Unzip the downloaded artifacts again or copy the files from the backup subfolder, correct the value and run the script again.

Related articles