Dynamic database reference in SSDT (dacpac) project

Question

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?

Answer 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.

Answer 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).

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