Conditional logic in migration scripts using ReadyRoll

Question

I have a project that uses ReadyRoll and a script with a migration header which uses a condition to either run the script or not based on a variable defined in the SQLCMD Variables section of the project properties ($(Environment).

-- <Migration ID="bf593e36-5883-4fff-9c6d-223f7449fccf" Condition="'$(Environment)' = 'DEV'" />

<some sql scripts here>

When I deploy this project to a clean sql server instance, no matter what Environment I specify it still deploys this script.

I'd like to know how I can run a script based on a condition with ReadyRoll. The script currently resides in the Migrations folder...I'm not sure if it may need to be relocated or not.

I've had a look at these links so far but they haven't helped:

• https://trello.com/c/ZzZr8Tkm/153-180-allow-migrations-to-be-conditionally-deployed-sqlcmd-packages-only
• https://readyroll.uservoice.com/forums/204125-readyroll-product-feedback/suggestions/7374679-synchronize-transactional-data-as-part-of-migratio

Answer 1

I have been able to get this to work by adding an if statement above the sql I wanted to run which mirrors the guard specified by the migration tag.

The final code snippet looks like this:

-- <Migration ID="bf593e36-5883-4fff-9c6d-223f7449fccf" />
if '$(Environment)' = 'DEV'
BEGIN
  <some sql scripts here>
END

Answer 2

This approach works perfectly fine for Octopus deploy:

-- <Migration ID="bf593e36-5883-4fff-9c6d-223f7449fccf" Condition="'$(Environment)' = 'DEV'" />

The only thing is that you need to create the variable with the same name "Environment" in your ReadyRoll project(SQLCMD Variables) and in Octopus. And assign separate values(DEV, TEST, PROD etc.) for each environment.