3

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Once deployment variables are in place, does SSDT support changing the path of a filegroup?
@AaronHudon not sure I follow you - can you give an example?
@KevinCunnane Is it possible to use a DeploymentPlanModifier without the ceremony (separate .dll / signing / move to specific location) when directly referencing the DacFX .dlls in a .NET project (aka not using SqlPackage.exe) ?
@Guillaume86 in the next release SqlPackage.exe (and the Dac APIs) will have an AdditionalDeploymentPaths argument. You can put it anywhere you like and just use that. If you ask the team on twitter they should be able to point to the doc as it changes.
@KevinCunnane thank you for the information. I was hoping the API would expose something accepting types or objects directly but it will work anyway.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.