Why do SqlPackage Script Action and VisualStudio Generate Script produce different results?

Question

We have a Visual Studio database project with a publish profile that produces different change scripts depending on whether I use Visual Studio's Generate Script option or SQL Package from the command-line:

Visual Studio creates a 10,455KB file.
SqlPackage creates an 8,835KB file using the following command: SqlPackage /a:Script /sf:pub.dacpac /pr:pub.profile.xml

Any suggestions on how to observe the command-line VS uses to generate its script? Any ideas what the difference might be when both processes use the following publish profile?

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>nrc_gateway</TargetDatabaseName>
    <DeployScriptFileName>clm_model.publish.sql</DeployScriptFileName>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <ScriptDatabaseCompatibility>True</ScriptDatabaseCompatibility>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <DropDmlTriggersNotInSource>False</DropDmlTriggersNotInSource>
    <DropIndexesNotInSource>False</DropIndexesNotInSource>
    <TargetConnectionString>Data Source=.;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <AllowIncompatiblePlatform>True</AllowIncompatiblePlatform>
    <DropConstraintsNotInSource>False</DropConstraintsNotInSource>
    <DropExtendedPropertiesNotInSource>False</DropExtendedPropertiesNotInSource>
    <VerifyDeployment>False</VerifyDeployment>
    <BlockWhenDriftDetected>False</BlockWhenDriftDetected>
    <RegisterDataTierApplication>False</RegisterDataTierApplication>
    <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
    <ExcludeUsers>True</ExcludeUsers>
    <ExcludeDatabaseRoles>False</ExcludeDatabaseRoles>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <DoNotDropAggregates>False</DoNotDropAggregates>
    <DoNotDropApplicationRoles>False</DoNotDropApplicationRoles>
    <DoNotDropAssemblies>False</DoNotDropAssemblies>
    <DoNotDropAsymmetricKeys>False</DoNotDropAsymmetricKeys>
    <DropPermissionsNotInSource>False</DropPermissionsNotInSource>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
    <ScriptNewConstraintValidation>True</ScriptNewConstraintValidation>
  </PropertyGroup>
</Project>

What is different in the files? What does something like windiff think? — Ed Elliott, May 04 '17 at 15:03

Steven Green · Answer 1 · 2017-05-05T17:42:35.170

Visual Studio doesn't use the CLI to generate a script, so there's no option to observe the interaction. (It doesn't even use the public Microsoft.SqlServer.Dac API -- it has internals visible access to the assemblies that provide this functionality).

I'd probably start by verifying that the version of SqlPackage.exe and the version of SSDT are the same. An older version of SqlPackage.exe might not be scripting out some of the newer database options (for example, database-scoped configuration settings).

VS's copy of SqlPackage lives here:

c:\program files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SqlDB\DAC\140

(Note that final "140" folder might actually be 130 or even 120, depending on the specific version of SSDT that's installed.)

Most folks run SqlPackage.exe from the Sql Server directory here:

C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin

(Note, again, that 140 could be 130 or even 120, depending on the specific version of DacFramework.msi that's installed).

But wherever your SqlPackage.exe is located, check whether its file version matches that of Visual Studio's copy.

Why do SqlPackage Script Action and VisualStudio Generate Script produce different results?

1 Answers1