Overriding DefaultDataPath and DefaultLogPath variables when using SqlPackage to publishing a dacpac

3
votes

The environment:

  1. Using a SQL Server Database Tool in Visual Studio 2013, Update 4 to create a DacPac for publishing a database.
  2. Using TFS build to build and drop the database project.
  3. Using SqlPackage version to publish the dacpac to SQL Server 2012.

Using the SqlPackage Action "Publish", the script for deployment is generated using the following variables based on the target database's defaults:

:setvar DefaultDataPath "H:\YourServerDefault"
:setvar DefaultLogPath "H:\YourServerDefault"

I would like to override these locations to land in a specific pair of directories where we have some external encryption configured.

I have attempted to override these SQLCMD values in a couple ways:

  1. Override on the command line:

    SQLPackage /Action:Publish /SourceFile:./Db.dacpac /Profile:./MyProfile.publish.xml /Variables:DefaultDataPath=H:\MyNewLocation

  2. Add an override in the publish profile (snippet below):

    <ItemGroup>
      <SqlCmdVariable Include="DefaultDataPath ">
        <Value>H:\YourServerDefault</Value>
      </SqlCmdVariable>
    </ItemGroup>

While I can use either technique to override my own defined SQLCMD variables, the built in ones like DefaultDataPath and DefaultLogPath are ignored.

Is there a way to override these values when publishing with SqlPackage?

1
dacpacsqlpackage

1 Answers

3
votes

There is no built in support in SqlPackage but you can do this using a deployment contributor. Take a look at the DbLocationModifier sample on GitHub that solves exactly this issue. To actually use this, you need to:

  • Download the code from Github
  • Add a signing key to the project
  • Install to the correct extensions directory, or put it in the same folder as SqlPackage itself.
  • Pass additional arguments to SqlPackage specifying your contributor name and arguments - see the test code for how to do this via the API, the SqlPackage calls are essentially the same.

For more information about contributors and how to use them you can read the tutorial I wrote or read the walkthroughs on MSDN.

Disclosure: I work on the SQL Server tools team.