Generate multiple SqlCmdVars files in your database projects

Earlier today I was doing a little work using datadude/DBPro/Visual Studio Database Tools/pick your name and had a need to write a Powershell script that I think might be useful to other folks so I’m sharing it here.

Often when you’re putting together database projects you will have a need for multiple .sqlcmdvars files – one for each environment that you are deploying to. It can be a real pain in the neck maintaining multiple .sqlcmdvars files because you need to make sure that:

each file has the same list of sqlcmd variables in it

the variables that need to be different per environment have got the correct values in them

If you have multiple environments and multiple projects then this can become a real headache to maintain – indeed, the project that I am working on at the moment has got 192 sqlcmdvars files. You can trust me that that is pretty excessive however this problem is still one that many people are going to encounter.

So, when you have 192 occurences of the same problem it seems logical to write a script to carry out the repetitive task of editing those files and that is exactly what I did today. My Powershell script requires that you maintain ONE .sqlcmdvars file per project and will then take care of generating the others and also replacing environment-specific variable values where appropriate. You need to supply a list of a projects, a list of environments and, for each environment, a value for each variable that you want to change. the example below has only one project, three environments (thus three .sqlcmdvars files) and one environment-specific variable. If you have anything more complex than that then the script is very easy to change and is self-explanatory.

Here’s a screenshot of my demo project:

Note the following:

There are three sqlcmdvars files {development.sqlcmdvars,production.sqlcmdvars,test.sqlcmdvars}

development.sqlcmdvars contains a variable called $(EmailAddress) that is not in either of the other two

It has an outer loop for all the projects and an inner loop for each environment. On each iteration it copies the contents of development.sqlcmdvars into $EnvName.sqlcmdvars and then updates the file with the new value for $(EmailAddress). If you have more variables that you need to edit then its not a great hardship to adapt the script accordingly.

Upon completion our test.sqlcmdvars and production.sqlcmdvars files have been edited as so:

UPDATE: I have discovered an alternative method courtesy of Robert Robelo and I like this one because it uses XPath which to me feels like a purer way of doing this – I can’t explain why so don’t ask! Here’s is Robert’s code which achieves the same as above: