How To: Execute scripts with different content depending on the environment

All environments in a pipeline may not be the same. Sometimes, schema names are different across environments. It could be that your tablespace (or filegroup in SQL Server) are not consistent. But because you need to deploy the same script in all environments, you need to specify a variable to represent the entity that changes from environment to environment.

This document walks through the example where the data in a table needs to vary from environment to environment.

Use case: I need to run DML scripts with different values depending on the environment. For example, I've a CUSTOMER table with the following entry in DEV:

CUSTOMER - DEV

In PROD, the content is of course different:

 I want to run a script with the following code in DEV

and in PROD

The solution is to use Changelog parameters in your changelog.xml.  See also Setting Properties in the Project Changelog.

1. Add the following <property> tags

   <property context="DEV" dbms="postgresql" name="customer.id" value="JD6"/> <property context="PROD" dbms="postgresql" name="customer.id" value="SC98"/>

2. Then simply modify your SQL script as follow and add under the /sql folder. We will support data_dml and sql_direct

   UPDATE CUSTOMER SET Load_Ind = ‘Y’ WHERE ID = '${customer.id}'

Setting Properties in the Project Changelog