Dynamic build configurations in Azure DW database project RRS feed

  • Question

  • Hello Team,

    I have a scenario wherein based on the build configuration (based on environments :dev,test,UAT,stage and prod) , the content of the script should change for external data source as seen below: wherein location of external data source should change as per the build config.

    For now I have added only 1 statement but still facing an issue

    So is there any way we can achieve that ?

    Wednesday, October 9, 2019 10:13 AM

All replies

  • Hi Nandan,

    Without knowing anything about how the build automation is being implemented, the best course of action might be to create a separate .sql script for each environment (AEAPBlobStorage_dev.sql, AEAPBlobStorage_test.sql, AEAPBlobStorage_UAT.sql, AEAPBlobStorage_stage.sql, and AEAPBlobStorage_prod.sql) and call the applicable script by name.

    What determines build config? How is this controlled? Thank you for providing additional information about the environment this is being implemented.



    Wednesday, October 9, 2019 11:41 PM
  • Hello Mike,

    Creation of separate SQL scripts cannot be the scenario since that itself is being called in external tables as

       DATA_SOURCE = [AEAPBlobStorage],
       LOCATION = N'/Cognos/Finance/ProjectRevenue/Active/',
       FILE_FORMAT = [CapPipeDelimited],
       REJECT_VALUE = 0

    So by creation of separate SQL scripts you are asking to maintain different database projects for each environment (since we would have to create separate table scripts for each environment) which would be a tedious task.

    So isnt there any way by which we can have it in a single database project?

    Thursday, October 10, 2019 3:08 AM
  • Hi Nandan,

    I believe you can create a database reference via the Database Reference Dialog. Please see: Project-Oriented Offline Database Development

    At the bottom of the document is: How to: Specify Predeployment or Postdeployment Scripts

    You can use the :setvar to establish your various environment variables and then run your IF statement to determine what environment it is and then pass in the correct CREATE EXTERNAL DATA T-SQL statement.

    Please let us know if you have additional questions.



    Friday, October 18, 2019 12:51 AM