0

I am trying to configure variables (Tokens) inside one of our Stored Procedures which is part of DACPAC project. I have attempted to do this in the same way you would with a config file. I.e

Create a duplicate file with the ext token. Replace the item to be replaced with TOKEN_NAME. In RM Create a variable for the component. However this does not seem to work for DACPAC solutions and the variable does not get replaced.

Is this possible to do with DACPAC projects? If not what approach can I take to add configurable items in stored procedures?

If this is possible where am I going wrong?

Daniel Mann
  • 57,011
  • 13
  • 100
  • 120
Darran Shelton
  • 3
  • 1

2 Answers2

3

This isn't a release management problem as much as it's a SSDT problem. You want to do something that's generally a really bad idea: Have your database differ from environment to environment. Continuous delivery is all about consistency. If you can't trust that you're "practicing" your production deployment every time you release your software to lower environments, you're setting yourself up for a really bad time when production deployment fails due to environment differences.

That said: There are several things you can do, listed in order of preference:

  1. Stop having your database differ between environments
  2. Since you can use SQLCmd variables within pre- and post-deployment scripts, make a post-deployment script that modifies your stored procedure, and pass in the value via RM. You can modify the arguments that RM passes in to sqlpackage.exe so that you can provide your SQLCmd variables at the time of publishing. It's still a really bad idea, but it should work for you.
  3. You can modify the Release Default Template and move the location of the token swapping actions. Token swapping normally happens after the build; you can move it to happen before the build.
Daniel Mann
  • 57,011
  • 13
  • 100
  • 120
0

I can't see any way for this to work using token files when publishing a DACPAC as there is no way for RM to interfere with what sqlpackage.exe is doing and even if it could it would cause the schema to stop compiling.

You might be able to write a component that unzips the DACPAC file, does the token replacement and then zips it back up but this sounds like hard work.

My advice is to apply environment specific requirements after the DACPAC runs using a tokenised SQL script. I describe this in a blog post here.

Graham Smith
  • 517
  • 2
  • 10