none
Correct way to override SQLCommandVariables with TFS MSBuild values

    Question

  • I have created a new build definition for my TFS Project which also deploys the solution to the target server using the technique from this post:

    http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/d73a489f-1b03-4b42-ac2b-647b0d1b9462

    Deploying works just fine but need to take it to the next level.

    From past projects I implemented the suggestion from Barclay Hill for the DeployLog table to version stamp that deployment of the database.  I have a SQLCommand variable called $(BuildID) which is the placeholder for the pre-deploy script to populate the DeployLog table with the buildid from the build used to deploy to the CI server. 

    Question: how do is override the $(BuildId) command variable with the value $(BuildID) defined in the build template in TFS?  Does it require modifying the default build template to add the overrides? 

    Items tried:

    1. Using sqlcommandoverride in the project file and then on a separate test in the dev.publish.xml file:

    <ItemGroup>
      <SqlCommandVariableOverride Include="BuildId=$(MSBuildBuildID)" />
    </ItemGroup>
    

    2. Adding /p:BuildID=$(BuildID) to the msbuild Arguments in the build template - no change, value for $(BuildId) in deploy script was the default ('Unknown')

    3. Add /v:BuildID=$(BuildId) but will not work as /v is the option to specify verbosity in the build. 

    Thanks for any help.

    Marvin

    Friday, April 27, 2012 5:29 PM

Answers

  • Hello Marvin

    Using the publish target you cannot override the SqlCommandVariables other than by changing the values in the profile. To get your scenario working you have two options:

      + SqlPackage.exe
        - You can invoke SqlPackage.exe and override the SqlCmdVariables using the /v:BuildId=value syntax.

       + Deploy Target
           - The Publish target calls the Deploy target to do the actual deployment. The Deploy target does support providing SqlCommandVariables, however, it does not accept a profile so you must provide all options manually.

    To set the SqlCommandVariables using the Deploy target, you must look at the project file definition for the variable names to set. In the project fragment below, the variable names are in the value element of the SqlCommandVariable. You will need to override the variable defined in the value not the SqlCmdVariable directly.

    <ItemGroup>
        <SqlCmdVariable Include="BuildId">
          <DefaultValue>
          </DefaultValue>
          <Value>$(SqlCmdVar__1)</Value>
        </SqlCmdVariable>
    </ItemGroup>

    Given the fragment above, you can override the buildId SqlCommandVariable as follows:

    msbulid /t:Deploy /p:SqlCmdVar__1=BLDNUMBER  /p:TargetConnectionString="Data Source=SOMESERVER;Integrated Security=True;" /p:TargetDatabaseName=PUBLISH_DBNAME

    Good luck

    Lonny

    Friday, May 11, 2012 4:12 AM
    Owner

All replies

  • Hello Marvin

    Using the publish target you cannot override the SqlCommandVariables other than by changing the values in the profile. To get your scenario working you have two options:

      + SqlPackage.exe
        - You can invoke SqlPackage.exe and override the SqlCmdVariables using the /v:BuildId=value syntax.

       + Deploy Target
           - The Publish target calls the Deploy target to do the actual deployment. The Deploy target does support providing SqlCommandVariables, however, it does not accept a profile so you must provide all options manually.

    To set the SqlCommandVariables using the Deploy target, you must look at the project file definition for the variable names to set. In the project fragment below, the variable names are in the value element of the SqlCommandVariable. You will need to override the variable defined in the value not the SqlCmdVariable directly.

    <ItemGroup>
        <SqlCmdVariable Include="BuildId">
          <DefaultValue>
          </DefaultValue>
          <Value>$(SqlCmdVar__1)</Value>
        </SqlCmdVariable>
    </ItemGroup>

    Given the fragment above, you can override the buildId SqlCommandVariable as follows:

    msbulid /t:Deploy /p:SqlCmdVar__1=BLDNUMBER  /p:TargetConnectionString="Data Source=SOMESERVER;Integrated Security=True;" /p:TargetDatabaseName=PUBLISH_DBNAME

    Good luck

    Lonny

    Friday, May 11, 2012 4:12 AM
    Owner
  • Lonny,

    It got me through to the next step.  I see how this was a newbie question.  Now figuring out how to get the actual build number from the TFS build server is next.

    Thanks!

    Marvin

    Monday, May 14, 2012 9:39 PM
  • Hi Lonny,

    When I try sqlpackage.exe,

    It says the variable missing a value during the build, I could understand from other form that  we need to set all sqlcmd variables value there.Just overriding one variable cannot be done.

    any thoughts?

    Thanks

    Kannan M

    

    Monday, February 03, 2014 8:30 PM