5

Is there a way where the database references in a SQL Server Database project (Dacpac) can be derived at run time?

We're building a product which uses Dacpac to deploy database objects.

Our product implementation teams also use Dacpac projects by adding database reference to the product Dacpac file and then adding their own additional objects to the project.

The problem we're facing is - every time the implementation needs to point to a newer product release version, the parent dacpac references in the implementation dacpac have to be changed manually to refer to the new file path of the new product dacpac (in the newer release). We've mutiple implementation teams and multiple database projects in each implementation.

Is there any way the database references (*.dacpac) in a database project can be derived at run time using a variable or parameter or something of that sort?

Ash
  • 241
  • 4
  • 15
  • 1
    put dacpacs to the shared location – Dmitrij Kultasev Nov 01 '17 at 08:05
  • just to clarify - do you mean when you deploy the project, you always want to deploy the latest dacpac or that when you build the implementation project you want to build against a dacpac that has some bits missing (from the old release)? – Ed Elliott Nov 01 '17 at 09:03
  • Dmitrij's comment is pretty much what we'd do. Build the appropriate version dacpac, put it in a shared location. Version control that as necessary and maybe make a note on which build(s) are needed in order for everything to work properly. Ideally the reference should be in a common path like "..\..\..\Schema\DB1.dacpac" or something similar so it's the same for all projects regardless. – Peter Schott Nov 01 '17 at 20:28
  • @EdElliott I would like to give the implementation team the flexibility to upgrade only when they want to. That means they should be able to add reference to any of the product (dacpac) release versions. At the momemnt,if they were pointing to product release 1.0.1 last month, and if they want to now point to 1.0.2 this month, they'll have to manually change all their SQLproj files to be able to refer to the 1.0.2 product dacpac. – Ash Nov 09 '17 at 08:39

2 Answers2

2

My understanding of your question is as follows:

You have a SSDT database project (see example image below), that has a database reference (similar to #1 below) with stored procedures and other db objects that access the reference database (similar to file #2 and code #3). You've 'built' the solution resulting in a DACPac and now you want to take this DACPac and reference other databases at deploy-time. Hopefully I understood your question correctly.

If so, then you can use the Publish Profiles feature to achieve this (similar to #4 below).

enter image description here

The code for this can be seen in my notes from my SSDT talk github project. If you look specifically at the demo04 solution file, you will see that I have a DEV_MJE.deploy.ps1 PowerShell file and a DEV_MJE2.deploy.ps1 file. These scripts run MSBuild to build the DACPac and then use SqlPackage to publish DEV_MJE.publish.xml or DEV_MJE2.publish.xml respectively. Note, if you run this example on your machine, you'll need to add MSBuild.exe and SqlPackage.exe to your path, as well as modifying the TargetConnectionString in the xml files to an existing development database.

As an example of how this works...When I use the Publish Profile DEV_MJE.publish.xml, the resulting GetDataFromAnotherTable.sql file contains:

SELECT [SomeData] FROM [AnotherDb_MJE].[dbo].[AnotherTable]

Whereas when I use DEV_MJE2.publish.xml the resulting GetDataFromAnotherTable.sql file contains:

SELECT [SomeData] FROM [AnotherDb_MJE2].[dbo].[AnotherTable]

Notice the database reference in the second has changed to AnotherDb_MJE2.

For a good description of how Publish Profiles relate to DACPacs and SSDT Database Projects, see this web page. It also contains information on alternative ways to deploy beyond SqlPackage.exe.

Something else to consider

Note, that using file paths to version control a DACPac is not really the best practice. Consider the DACPac artifact as similar to a .Net DLL. It is the biproduct of a build.

As such, a better approach is to use NuGet and tools like Octopus Deploy to store, track, and deploy DACPacs. See the stackoverflow answer for a good description of how this works.

Hope that this helps,

Michael

Michael Erpenbeck
  • 553
  • 4
  • 11
1

Thanks for the followup comment, I think what you are trying to do is when you write and deploy your code be able to use different dacpacs depending on the project?

Each implementation team might have a different version of the shared dacpac deployed so you can't just put the files in a shared location and call the dacpac "Product_Latest.dacpac" or something, so everyone always gets the latest version.

".sqlproj" files are standard msbuild scripts and references can be managed using msbuild properties so you can technically change the reference at runtime. If you edit your .sqlproj file and add a property in the first <PropertyGroup> tag, I used:

<ProdDacpacVersion Condition="'$(ProdDacpacVersion)' == ''">v1</ProdDacpacVersion>

v1 is the unique name for the version folder - you just need something to identify the dacpac you want.

I put the property just after TargetDatabaseSet and IncludeCompositeObjects.

If you then find the reference to the dacpac and instead of

<ArtifactReference Include="..\..\..\..\..\Desktop\prod\v1\Database2.dacpac"> <HintPath>..\..\..\..\..\Desktop\prod\v1\Database2.dacpac</HintPath> <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors> </ArtifactReference>

Use the property from above:

<ArtifactReference Include="..\..\..\..\..\Desktop\prod\$(ProdDacpacVersion)\Database2.dacpac"> <HintPath>..\..\..\..\..\Desktop\prod\$(ProdDacpacVersion)\Database2.dacpac</HintPath> <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors> </ArtifactReference>

Then the reference will use the property to get the path of the dacpac. There are a few ways you can set the property, you could edit the .sqlproj file when you want to take a new version of read the property from a file or environment variable or something (i'll leave the msbuild fun to you!).

I would, as a standard, everytime the reference was changed either reload the project or restart visual studio - it won't take long and it will save lots of cursing :)

When you deploy the dacpac's, the deploy looks in the same folder for references first so just make sure you copy the right one into the bin folder when you deploy.

Ed Elliott
  • 6,666
  • 17
  • 32