#TSQL2sDay – How SQLPackage.exe can spoil your deployment

#TSQL2sDay – How SQLPackage.exe can spoil your deployment

Today, I’d like to describe briefly one thing with SQLPackage.exe.
I know… It’s not precisely related to PowerShell (this week topic), but… you can use PowerShell to run it (and honestly I’m doing it via Octopus).
This application can be used to deploy (publish) Microsoft SQL database saved in SSDT.
In many ways. No matter what type of script you’re using: batch (old-fashioned, I know), PowerShell (cool!) or you’ll run this exe from other custom app written in C#, for example. All the ways leading to the goal are fine.
A compiled version of such database dwells as a DACPAC file form, which contains all objects of database required to its reflect into the target.
What’s important in this post’s context – the file can contain relations to the other databases:

CREATE PROCEDURE [dbo].[getCustomerInfo]
	@customerId int
	SELECT [CustomerId], [CountryCode], [FirstName], [Surname], [isActive], [CustomerTypeCode], [Twitter] 
	FROM [$(CRM)].dbo.Customer;

Action: Report, script, publish

There are more than 3 actions possible with SQLPackage.exe, but from a deployment perspective, we are interested in:
• DeployReport – creates an XML report file with describes changes that would be done during publishing
• Script – creates an incremental update T-SQL script that updates the target schema of a target database
• Publish – incrementally updates a database schema to match the schema of a source .dacpac file.
Since the update of October 2016 – all above actions can be done in one pass. Believe me, it saves a lot of time.


So, how to publish database? You can use Visual Studio, right-click on selected database and choose to Publish:

Then you can click “Generate Script” or “Publish”. Everything gonna be fine.

But hey… We love automation, right?

Let’s have a look on that batch script (@Rob [B|T], I promise to deliver PowerShell version soon 😉 ):

rem DeployDACPAC.bat
echo off
set prg="c:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe"
set dir=Q:\Dropbox\Post\
set output=Q:\Dropbox\Post\output\

SET prgtmp=%prg:\=\\%
echo This DacFX will be used: %prg%
WMIC DATAFILE WHERE name=%prgtmp% get Version /format:Textvaluelist

set db=ContosoRetailDW

set dacpac="%dir%%db%\bin\Debug\%db%.dacpac"
set profile="%dir%%db%\%db%.publish.xml"

echo === Database: %db% ===
echo Start:        %date% %time%
echo Profile:      %profile%

%prg% /Action:Publish /SourceFile:%dacpac% /Profile:%profile% /dsp:"%output%%db%.publish.sql" /drp:"%output%%db%.report.xml"

Wait, wait… What’s that error?

As you can see on the picture below – the project contains two variables possessing the same value. It can be a value for some field, part of stored procedure or function, or (more frequently used) – database name referral.
This is a very simple example, but in the real world scenario – you can have tons of such variables. Those variables can be scattered after many database projects related to each other. Hence, the variables can come from many (related) projects.

What goes wrong?

The publication from UI (Visual Studio) would be gone properly with exactly the same projects, with exactly the same publish profile.
What we’ve done wrong? Where is the bug?
Apparently, SQLPackage.exe is case-sensitive, whereas Visual Studio does not.

How to fix it?

You must normalize that variables in all your database projects. I’d suggest using upper-case always:

Unfortunately, sometimes it’s not possible to change it from UI (for some enigmatic reasons), then you can edit (be careful) project file using Notepad++ or Visual Studio Code. It would be enough to change attribute “Include” in “SqlCmdVariable” element:

  <ProjectReference Include="..\CRM\CRM.sqlproj">
  <SqlCmdVariable Include="Crm">

Then you need to remove the duplicated key from the publish file:

What next?

Do you need more? No problem at all.
I’ll prepare the whole series about SSDT and Continuous Delivery for databases soon. Stay tuned!

Previous CSV reader from SDU Tools
Next ASF 003: Stephanie Locke interview

About author

Kamil Nowinski
Kamil Nowinski 145 posts

Blogger, speaker. Data Platform MVP, MCSE. Senior Data Engineer & data geek. Member of Data Community Poland, co-organizer of SQLDay, Happy husband & father.

View all posts by this author →

You might also like

T-SQL Tuesday 0 Comments

T-SQL Tuesday #97 – My learning plans for 2018

T-SQL Tuesday #97 is hosted by Mala Mahadevan [B|T] and this time the topic is “Setting learning goals for 2018“. Mala ask about our next year’s plans in terms of

T-SQL Tuesday 0 Comments

T-SQL Tuesday #108 – Non SQL Server Technologies

It’s T-SQL Tuesday #108. This month’s topic is hosted by Malathi Mahadevan (B|T) and she asks us to… “pick one thing you want to learn that is not SQL Server.

SSDT (SqlPackage) thread crashes with error when publishing

In this post, I will show you one of the scenarios when SSDT (SQL Server Data Tools) can fail unexpectedly while publishing database project to target server or preparation of


No Comments Yet!

You can be first to comment this post!

Leave a Reply

Protected with IP Blacklist CloudIP Blacklist Cloud