#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
AS
	SELECT [CustomerId], [CountryCode], [FirstName], [Surname], [isActive], [CustomerTypeCode], [Twitter] 
	FROM [$(CRM)].dbo.Customer;
RETURN 0

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.

Publish

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?

*** Error parsing connection string: An item with the same key has already be added..

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:

<ItemGroup>
  <ProjectReference Include="..\CRM\CRM.sqlproj">
    <Name>CRM</Name>
    <Project>{891a1f33-a9b8-4a4d-b2b8-e45c917a8b0d}</Project>
    <Private>True</Private>
    <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
    <DatabaseSqlCmdVariable>Crm</DatabaseSqlCmdVariable>
  </ProjectReference>
</ItemGroup>
<ItemGroup>
  <SqlCmdVariable Include="Crm">
  <DefaultValue>CRM</DefaultValue>
  <Value>$(SqlCmdVar__1)</Value>
  </SqlCmdVariable>
</ItemGroup>
</Project>

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!

Updated @ 08/11/2019
This post is already part of the whole series about SSDT.