In this post we would like to explain one of the interesting issue that we encountered while deploying a DACPAC from sqlpackage.exe.

Symptoms

Cannot Deploy DACPAC Extracted from SQL 2012 Server from .NET custom code or from SQLPackage.exe command to SQL 2014

C:Program Files (x86)Microsoft SQL Server110DACbin>SqlPackage.exe /Action:Publish /SourceFile:”C:tempAgentLink2_11.0.6020.dacpac” /tsn:”RAGHAVSDC” /TargetDatabaseName:TestACM

Publishing to database ‘TestACM’ on server ‘RAGHAVSDC’.
The dac history table will not be updated.
Initializing deployment (Start)
Initializing deployment (Failed)
*** Could not deploy package.
Unable to connect to target server.

Cause

We don’t have a DAC Folder at location C:Program Files (x86)Microsoft SQL Server120 in the system but have the folder C:Program Files (x86)Microsoft SQL Server110DACbin (We can successfully publish to SQL 2012 but not SQL 2014)

Resolution

To reproduce the issue, please find a DAC folder at location C:Program Files (x86)Microsoft SQL Server110DACbin

Open a CMD with administrator privileges and navigate to this path and run the sqlpackage.exe to publish it to a SQL 2014/2016 server and we will get the same exact error

“*** Could not deploy package.

Unable to connect to target server.”

The above looks like to be a connectivity error at our first glance but this is not the case here. We tested the connectivity for this on multiple machines and didn’t find an issue with it. The solution to the problem is we need to install the DAC Framework https://www.microsoft.com/en-in/download/details.aspx?id=42293 and once installed, we will be able to see the DAC Folder at C:Program Files (x86)Microsoft SQL Server120DAC

We then can try to publish the DACPAC from sqlpackage.exe from the 120 Location and it gets published successfully.

More Information:

In the above scenario, we noticed that we can only publish the DACPAC for the version the DACPAC file was created for.

If we have taken a DACPAC for SQL 2012, then we can publish is to any higher version of SQL but it needs to be published from the 120 folder (C:Program Files (x86)Microsoft SQL Server120DACBin) if we want to publish to SQL 2014. If we are trying to publish the DACPAC taken from SQL 2012 to 2016 then we need to publish the package from the 130 Folder (C:Program Files (x86)Microsoft SQL Server130DACBin)

DACPAC is a feature of our Data Tier application which will allow us to backup the schema of our database. In simple terms, it is only database schema (definition without the data) which can be used on higher versions of SQL Server. SQLPackage.exe is a utility which allows us to automate database development and projects in our environment.

Related articles:

SQLPackage.exe: https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

Data Tier Applications: https://msdn.microsoft.com/en-us/library/ee210546.aspx

Design and Implementation for DACPAC: https://technet.microsoft.com/en-us/library/ee210546(v=sql.110).aspx

DAC Framework download: https://www.microsoft.com/en-in/download/details.aspx?id=42293

Written by – Ujjwal Patel, Support Engineer.
Reviewed by – Raghavendra, , Sr. Support Engineer.