Microsoft Fabric Warehouse development – deploying project with SQLPACKAGE

We continue our adventure with the Visual Studio database project. Today, we’ll take a look at the sqlpackage.exe tool, which will allow us to automate the deployment of the project to the target server. I warmly invite you and, from here, encourage you to subscribe to stay up-to-date with our materials.

Before we dive into presenting this tool, let’s first show how to obtain it. If you don’t have it on your system, you can find all the installers (including for Linux and macOS) on the documentation page dedicated to this tool (link).

Sqlpackage.exe is a command-line tool that enables a range of operations related to SQL Server or Azure SQL Database. SQLPackage is particularly useful in all sorts of deployment automation processes, but not only that. With it, you can perform the following operations:

Create a DACPAC file, which is an artifact containing the structure based on a SQL Server or Azure SQL DB database,
Create a BACPAC file, which is an artifact containing both the structure and data based on a SQL Server or Azure SQL DB database,
Publish the database structure to a server based on a DACPAC file,
Publish the database structure and data to a server based on a BACPAC file,
Generate an SQL script containing the operations needed to deploy the structure from the source DACPAC to the target server,
Generate XML reports summarizing the changes that will be applied to the target server.

As you can see, the range of possibilities is quite extensive. It’s easy to notice that most operations rely on artifacts like DACPAC, which are generated as a result of the Build operation performed on a Visual Studio database project.

To build a database project, I can do it using the dotnet.exe tool with the build switch or utilize the graphical interface. Earlier, I prepared a simple database project whose target destination will be Fabric Warehouse.

Script is pretty simple

dotnet.exe  build "c:\\repos\\personal\\FabricWarehouseProject\\FabricWarehouse\\FabricWarehouse.sqlproj" /p:NetCoreBuild=true /p:SystemDacpacsLocation="c:\\Users\\XATAACD\\.vscode\\extensions\\ms-mssql.sql-database-projects-vscode-1.4.5\\BuildDirectory"

dotnet.exe is a standard command-line tool that comes with the .NET Framework installed on your machine. We need to provide a build command along with the path to the .sqlproj file of our project. Additionally, we must specify whether it should be a .NET Core build and provide a build directory where our output artifact, the ‘dacpac,’ will be located. That’s all. As you can see in the screenshot below, everything succeeded, and there are no errors in our project.

Having the artifact at our disposal, we can now proceed to upload it to the server using SQLPackage. In this case, the syntax called from PowerShell is quite simple and looks as follows:

sqlpackage.exe /Action:Publish /SourceFile:"C:\repos\personal\FabricWarehouseProject\FabricWarehouse\bin\Debug\FabricWarehouse.dacpac" /TargetConnectionString:"Data Source=uv74vxxjxy2edhjg4ii7wl7joy-e5bjgggeru788nnr4vq5eccw4i.datawarehouse.fabric.microsoft.com;Initial Catalog=dw01;Multiple Active Result Sets=False;Connect Timeout=30;Encrypt=True;Trust Server Certificate=False;Authentication=Active Directory Interactive"

We have few parameters to set:

Action – in this case it will be publish,
SourceFile – path to the dacpac file produced by our build process,
TargetConnectionString – connection string of Fabric Warehouse

You can ask where we will get this connection string from, and I will show you. You have to open your warehouse, and it can be copied from there:

The initial catalog will be the name of your warehouse (in my case, dw01). Also add Multiple Active Result Sets=False;Connect Timeout=30;Encrypt=True;Trust Server Certificate=False;Authentication=Active Directory Interactive, which are self-explanatory. Of course, when we automate it from, for example, Azure DevOps, we will need to use a different authentication method, but for the purpose of this article, it is acceptable to set it like this.

After executing such a command we will be asked to login and after that we will receive a response on the screen with information about which objects were created, how much time has passed, etc. Below, you can see part of this information:

Was the target database created from scratch, or perhaps added incrementally? Were the properties of the database itself included? To these few questions, we can answer that everything depends on the project settings. To avoid dwelling on this too long, let’s connect a previously created profile (I described how to do this here). This operation shouldn’t cause anyone any trouble, as it simply involves using the /Profile: parameter.

sqlpackage.exe /Action:Publish /SourceFile:"C:\repos\personal\FabricWarehouseProject\FabricWarehouse\bin\Debug\FabricWarehouse.dacpac" /Profile:"C:\repos\personal\FabricWarehouseProject\FabricWarehouse\FabricWarehouse_01.publish.xml"

As can be noticed in the above command, we no longer have the TargetDatabaseName and TargetServerName parameters because the indication of the target database is contained within the profile file itself. I also had a setting there stating that the database should be deployed incrementally (only changes), which is why I received a fairly short and concise message on the screen about the actual changes:

Generally speaking, I recommend using profiles because then, if the need arises, we can easily manipulate the profiles and modify the settings they contain. That’s pretty much it when it comes to deploying a database to a server from the command line. As you might guess, most of the work is related to preparing the database project and the profile itself, since SQLPackage is merely a command that allows you to specify its location and select a particular DACPAC.

Here’s a grammatically corrected version of your text:

You can ask how to create a profile file and what exactly this file is. It is just a set of settings saved so you don’t have to specify all of them every single time. My profile looks like this:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>dw01</TargetDatabaseName>
    <TargetConnectionString>Data Source=uv74vxxjxy2edhjg4ii7wl7joy-e5bjgggeru7u5nnr4vq5eccw4i.datawarehouse.fabric.microsoft.com;Initial Catalog=dw01;Connect Timeout=30;Authentication=ActiveDirectoryInteractive;Application Name=sqltools;Command Timeout=30</TargetConnectionString>
    <AllowIncompatiblePlatform>True</AllowIncompatiblePlatform>
    <DropPermissionsNotInSource>True</DropPermissionsNotInSource>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DropRoleMembersNotInSource>True</DropRoleMembersNotInSource>
    <IgnoreKeywordCasing>False</IgnoreKeywordCasing>
    <IgnoreSemicolonBetweenStatements>False</IgnoreSemicolonBetweenStatements>
    <AllowDropBlockingAssemblies>True</AllowDropBlockingAssemblies>
    <ExcludeAudits>True</ExcludeAudits>
    <ExcludeCredentials>True</ExcludeCredentials>
    <ExcludeDatabaseScopedCredentials>True</ExcludeDatabaseScopedCredentials>
    <ExcludeCryptographicProviders>True</ExcludeCryptographicProviders>
    <ExcludeDatabaseAuditSpecifications>True</ExcludeDatabaseAuditSpecifications>
    <ExcludeEndpoints>True</ExcludeEndpoints>
    <ExcludeErrorMessages>True</ExcludeErrorMessages>
    <ExcludeEventNotifications>True</ExcludeEventNotifications>
    <ExcludeEventSessions>True</ExcludeEventSessions>
    <ExcludeFiles>True</ExcludeFiles>
    <ExcludeLinkedServerLogins>True</ExcludeLinkedServerLogins>
    <ExcludeLinkedServers>True</ExcludeLinkedServers>
    <ExcludeLogins>True</ExcludeLogins>
    <ExcludeRoutes>True</ExcludeRoutes>
    <ExcludeServerAuditSpecifications>True</ExcludeServerAuditSpecifications>
    <ExcludeServerRoleMembership>True</ExcludeServerRoleMembership>
    <ExcludeServerRoles>True</ExcludeServerRoles>
    <ExcludeServerTriggers>True</ExcludeServerTriggers>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

You can, of course, prepare such a file manually; there is nothing wrong with this approach. Alternatively, you can use a graphical interface for deployment, and VS Code will ask you if you want to save your settings as a profile.

Let’s supplement the information about the tool with an example of how to extract a DACPAC from an already existing database on a server. It’s very simple—this time, in the /Action parameter, we pass the value “Extract,” followed by the connection details of the database we’re interested in, as well as the location where the generated DACPAC should be saved:

sqlpackage.exe /Action:Extract /SourceConnectionString:"Data Source=uv74vxxjxy2edhjg4ii7wl7joy-e5bjgggeru7u5nnr4vq5eccw4i.datawarehouse.fabric.microsoft.com;Initial Catalog=dw01;Multiple Active Result Sets=False;Connect Timeout=30;Encrypt=True;Trust Server Certificate=False;Authentication=Active Directory Interactive" /TargetFile:"C:\temp\MyFabricWarehouse.dacpac"

If we want to extract not only the structure but also the data, the command looks very similar, except instead of “Extract” we use the keyword “Export,” and the result is the creation of a file with the BACPAC extension:

sqlpackage.exe /Action:Export /SourceConnectionString:"Data Source=uv74vxxjxy2edhjg4ii7wl7joy-e5bjgggeru7u5nnr4vq5eccw4i.datawarehouse.fabric.microsoft.com;Initial Catalog=dw01;Multiple Active Result Sets=False;Connect Timeout=30;Encrypt=True;Trust Server Certificate=False;Authentication=Active Directory Interactive" /TargetFile:"C:\temp\MyFabricWarehouse.bacpac"
Connecting to database 'dw01' on server 'uv74vxxjxy2edhjg4ii7wl7joy-e5bjgggeru7u5nnr4vq5eccw4i.datawarehouse.fabric.microsoft.com'.

The export operation will, of course, be significantly more time-consuming than extraction and depends on how much data we have in our database.

Of course, each of the above operations comes with a range of parameters that can be used, among other things, to properly authenticate to the database—I won’t describe all of them here, but instead refer those interested to the documentation. For now, we already know how to automate deployment—in the near future, we’ll go over the next steps needed to automate our project. Cheers!