31

I've been successfully publishing DACPACs to SQL Server 2008-2012 instances using SqlPackage.exe, as installed by SQL Server Data Tools (and typically found in C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin). However, in attempting to publish a 2014-targeted DACPAC to a SQL Server 2014 instance using this same SqlPackage.exe, I get the following:

*** Could not deploy package.
Internal Error. The database platform service with type Microsoft.Data.Tools.
Schema.Sql.Sql120DatabaseSchemaProvider is not valid. You must make sure the
service is loaded, or you must provide the full type name of a valid database
platform service.

I've found minimal info regarding this; the closest I have found is was a problem publishing to Azure.

I've kept up to date with SSDT patches but would guess that the SqlPackage.exe I have (which shows an 11.0.2902.0 version) is simply incompatible. I am able to publish to this same instance using Visual Studio 2012's Publish command so the instance itself does not seem to be the issue.

Is there a newer version of SqlPackage available that would support publishing a 2014 DACPAC to a 2014 server? Or another scriptable way to do this?

Nick Jones
  • 4,395
  • 6
  • 33
  • 44

3 Answers3

59

Yes, there is a new version supporting SQL Server 2005-2016 available and it installs into a different location than the previous (SQL Server 2012 and lower) version. In fact, you'll have different install locations depending on if you just use SSDT or if you install it as part of SSMS or the standalone installer.

  • SSDT installs the Dac DLLs inside Visual Studio in the latest releases. This is to avoid side by side issues (Visual Studio 2012 vs 2013 vs SSMS) that required all to be updated to use the latest code.

    • If you have updated to the latest SSDT, you'll find SqlPackage.exe and the related DLLs in the VS Install Directory\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130. For VS2013 the VS install directory is C:\Program Files (x86)\Microsoft Visual Studio 12.0, and it's 14.0 for VS2015.

  • SQL Server Management Studio (SSMS) and the standalone Dac Framework MSI both install to the system-wide location. This is C:\Program Files (x86)\Microsoft SQL Server\130\Dac\bin.

Kevin Cunnane
  • 8,020
  • 1
  • 32
  • 35
  • Wow, we accidentally dropped some users (in test) because the old version doesn't honor the latest DoNotDropUsers setting! – user917170 Mar 18 '15 at 00:45
  • 6
    On my (64-bit) version of Windows Server 2008 R2, the DAC Framework MSI put SqlPackage.exe in the C:\Program Files folder, *not* the C:\Program Files (x86) folder. Make sure you check both locations. – JamesQMurphy Apr 16 '15 at 14:33
  • this worked for us after hours of troubleshooting. thank you for your help! – sheldonhull Oct 02 '15 at 18:01
  • Which feature in the SQL 2014 installation feature tree do you need to select to get this installed? – Pete Stensønes Jul 18 '16 at 10:46
  • 2
    @PeteStensønes tools are separate from SQL Server (and are backwards compatible). For the latest SSDT go to https://msdn.microsoft.com/en-us/library/mt204009.aspx. For SSMS go to https://msdn.microsoft.com/en-us/library/mt238290.aspx. These are for SQL Server 2016. I'll also update this answer to reflect the fact the latest tools are 130 – Kevin Cunnane Jul 19 '16 at 01:43
  • @KevinCunnane, I'm seeing a lack of backwards-compatibility in the very latest SSDT update for VS 2013 (12.0.60629.0), which seemingly [doesn't work](http://stackoverflow.com/q/39354558/466874) with SqlPackage.exe. – Nick Jones Sep 06 '16 at 19:26
  • Thanks @NickJones, this is a bug that has been fixed internally and will be included in our upcoming 16.4 release. We will update our blog as soon as this is available (expect this in the 2nd half of September) - see https://blogs.msdn.microsoft.com/ssdt/2016/08/15/sql-server-data-tools-16-3-release/ for our last release information. You'd installed the VS2013 update from there. – Kevin Cunnane Sep 07 '16 at 01:28
  • What is the solution? We just need to update the SSDT version? – Alisson Reinaldo Silva Mar 07 '18 at 14:52
9

There is now an official Microsoft.Data.Tools.Msbuild NuGet package that contains SqlPackage.exe and a blog post with the details:

This NuGet package contains all the required components to build and publish SQL Projects (.sqlproj). This supports continuous integration and continuous deployment scenarios – SQL Projects can be built on a local build agent without installing the full Visual Studio or SQL Server Data Tools (SSDT) products.

Jason
  • 171
  • 1
  • 5
  • How do you use this package? You can't add NUGET packages to database projects... – Mark Walsh Nov 14 '16 at 14:29
  • 2
    @MarkWalsh, I ran `nuget.exe install Microsoft.Data.Tools.Msbuild` on the commandline. You could also just install it to a different project - All you need is the EXE pulled down to a local folder. – CrazyPyro Jul 07 '17 at 15:58
  • 1
    it's a shame seeing this post too late after installing the SQL Server Data Tools – Zinov Oct 08 '17 at 18:54
1

SSDT installs the Dac DLLs inside Visual Studio in the latest releases. This is to avoid side by side issues (Visual Studio 2012 vs 2013 vs SSMS) that required all to be updated to use the latest code. If you have updated to the latest SSDT, you'll find SqlPackage.exe and the related DLLs in the VS Install Directory\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130. For VS2013 the VS install directory is C:\Program Files (x86)\Microsoft Visual Studio 12.0, and it's 14.0 for VS2015. SQL Server Management Studio (SSMS) and the standalone Dac Framework MSI both install to the system-wide location. This is C:\Program Files (x86)\Microsoft SQL Server\130\Dac\bin.

**Ans of Mr. Kevin Cunnane. is a right process but if the database in sql2016 use 140\dac **

Deb Kumar Roy
  • 11
  • 1
  • 3