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
• My Powershell script is stored as a solution file

Here is the script:

#This script copies the contents of each <defined>.sqlcmdvars into all the other .sqlcmdvars files for that project
#It should live in the root folder of your solution
#If your sqlcmdvars files are under source control don’t forget to check them out
cls
$masterSqlCmdVars = "development"    #Edit these two
$variableName = "EmailAddress"        #values accordingly

$DbArray = ('20110119 Test Sqlcmdvars Generator') #List all your .dbproj files
foreach ($Db in $DbArray)
{
    #List of environments and the property values for each respective environment
    $EnvArray = (
                    ('test','test@example.com'),
                    ('production','production@example.com')
                )
    foreach ($Env in $EnvArray)
    {
        #STEP 1 - copy the file
        $EnvName = $Env[0]
        $VarValue = $Env[1]
        $source = "$Db\Properties\$masterSqlCmdVars.sqlcmdvars"
        $destination = "$Db\Properties\$EnvName.sqlcmdvars"
        "Copying $source to $destination"
        copy $source $destination

        #STEP 2 - edit with new variable values
        $xml = [xml](Get-Content $destination)
        $root = $xml.get_DocumentElement();
        foreach ($property in $root.Properties.ChildNodes)
        {
            if ($property.PropertyName -eq $variableName)
            {
                $property.Propertyvalue = $VarValue
            }
        }
        "Saving modified XML to $destination"
        $destination = resolve-path $destination
        $xml.Save($destination)
    }
}

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:

That’s it. Pretty simple. I won’t go deep into explaining the script because if this is useful to you you will probably want to do that yourself. This demo project, including the Powershell script, is available for download at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110119/20110119%20Test%20Sqlcmdvars%20Generator.zip.

@Jamiet

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:

cls
$masterSqlCmdVars = "development"    #Edit these two
$variableName = "EmailAddress"        #values accordingly

$DbArray = ('20110119 Test Sqlcmdvars Generator') #List all your .dbproj files
foreach ($Db in $DbArray)
{
    #List of environments and the property values for each respective environment
    $EnvArray = (
                    ('test','test@example.com'),
                    ('production','production@example.com')
                )
    foreach ($Env in $EnvArray)
    {
        #STEP 1 - copy the file
        $EnvName = $Env[0]
        $VarValue = $Env[1]
        $source = "$Db\Properties\$masterSqlCmdVars.sqlcmdvars"
        $destination = "$Db\Properties\$EnvName.sqlcmdvars"
        "Copying $source to $destination"
        copy $source $destination
        
        [xml]$xml = Get-Content $destination
        $nsMgr = New-Object Xml.XmlNamespaceManager $xml.NameTable
        $prefix = 'ns'
        $uri = 'urn:Microsoft.VisualStudio.Data.Schema.Package.SqlCmdVars'
        $nsMgr.AddNamespace($prefix, $uri)
        $xpath = "//${prefix}:Property[./${prefix}:PropertyName='$variableName']"
        $node = $xml.SelectSingleNode($xpath, $nsMgr)
        $node.PropertyValue = $VarValue
        $xml.Save($destination)
        Get-Content $destination
    }
}

I have also updated the downloadable file at http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110119/20110119%20Test%20Sqlcmdvars%20Generator.zip with Robert’s code.