THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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:

image

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:

image

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.

Published Wednesday, January 19, 2011 9:24 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Claire said:

Thanks for the script, Jamie. 192 occurences!

January 20, 2011 11:54 AM
 

Peter Schott said:

I get the feeling I'll be using this soon. 7-10 DB Projects. 5 environments. Way too many configs. This seems like it will be useful when we start really preparing each project/config.

January 24, 2011 6:55 PM
 

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM
 

Gareth said:

Jamie, yet again I run into a problem and yet again you have the solution for me.

Probably symptomatic of the fact I'm around 2 years behind you technology and skills wise!

Thank you my friend, prolific and knowledgeable bloggers like you brighten up the working lives of us mere mortals :)

January 25, 2013 5:00 AM
 

jamiet said:

Hey Gareth, glad to be of service :)

January 25, 2013 5:15 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement