Unable to import SQL Azure V12 BACPAC: "type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid"

Question

I am trying to import the exported BACPAC from an SQL Azure (v12) database into a local SQL Server 2012 instance, but I keep getting the error below. I have tried installing the DAC and SSDT updates linked from this blog post, but it's not helping.

How can I fix this?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Count not load schema model from package. (Microsoft.SqlServer.Dac)

------------------------------
ADDITIONAL INFORMATION:

Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider 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. (Microsoft.Data.Tools.Schema.Sql)

------------------------------
BUTTONS:

OK
------------------------------

Kevin Cunnane · Accepted Answer · 2015-12-14T19:19:34.793

41

Updated: The new SQL Server Management Studio Preview is the best way to Import to Azure SQL DB. It has support for all the latest Azure SQL DB features and validations. In addition it has a standalone web installer that is automatically updated each month as new features become available. Given comments below mentioning the difficulty of installing a CU update, this would be a simpler & quicker solution to the problem.

Original Answer: If you are using SQL Server Management Studio to perform the Import, you must have SSMS 2014 CU5 or CU6 installed. Information on installing CU6 is available here. The error shown in your question indicates you're using an older version of SSMS.

**Update: ** In response to Martin's answer below, I'd like to clarify 2 things.

SSMS for SQL Server 2014 is the only version of SSMS with full support of the new Azure SQL DB v12 features, notably Import/Export against this target. This is because v12 has (almost) feature parity with SQL Server 2014 and older versions of the tooling do not have support for this. Note that SSMS 2014 is fully backwards compatible with SQL Server 2005 and up.
There was a separate, temporary issue that caused problems with databases upgraded Azure SQL DB v12. This has been resolved and the correct place to find information about solving this are in section C3 of the Plan and Prepare to Upgrade page. In summary if you've exported a bacpac that is failing to import due to this issue you can download the latest DacFramework.msi from here to fix this issue in SSMS.

Full disclosure: I work on the SQL Server tools team.

edited Dec 14 '15 at 19:19

answered Feb 17 '15 at 18:46

Kevin Cunnane

8,020
1
32
35

28

There is some work to be done on making these "hotfixes" more available. I can't believe that I'm being e-mailed a link to download the hotfix like I'm some 18th century French peasant. – stimms Mar 04 '15 at 14:36
Using SSMS for SQL Server 2014 worked for me. When trying to use SSMS bundled with 2012, I kept getting errors like `Warning SQL72012: The object [PRIMARY] exists in the target...` when trying to import a BACPAC file from SQL Azure. – Barrie Mar 18 '15 at 10:43
I was unable to install CU6. no info other than `There are no SQL Server instances or shared features that can be updated on this computer.` Maybe it's up to date? I don't know, it's not telling me that. Why is my SSMS installation is unselectable as a feature to modify via the CU6 installer? I see instances and two boxes, but they are unclickable. one of them is MSSQLSERVER – FlavorScape Dec 03 '15 at 23:41
@FlavorScape - I would guess that CU requires an existing release that it updates. The new best practice is to install the SSMS Preview, which has all the latest support, does not require an engine install, and gets automatic updates every month. You can get it from https://msdn.microsoft.com/en-us/library/mt238290.aspx – Kevin Cunnane Dec 14 '15 at 19:17
1

As mentioned below, the problem for me was that I needed to install **BOTH x86 and x64** versions of DacFramework.msi! – adhie Jan 06 '16 at 14:01
Preview SSMS as linked in the update worked. Thanks @KevinCunnane – David Peden Jan 07 '16 at 03:04
@KevinCunnane Is this going to come to VS2015? I have not installed SSMS on my machine since 2008, since all needed SQL Tools are available in VS Studio. I can not find anything to take a SQL Azure export and import it locally. – Rudi Apr 26 '16 at 12:46
1

@Rudi SqlPackage.exe is installed in VS2015, which supports command line Import/Export. Longer term we're looking to align features across SSMS & SSDT more, so it's possible but not firmly funded on any roadmap yet. For right now dacpac publish/extract work in VS, but the Azure integration needed to properly support Import scenarios is missing so would need to be implemented. I'd recommend opening a Connect bug/DCR to help give requests like this visibility. – Kevin Cunnane Apr 26 '16 at 17:27
I have SSMS 2014 and i resolved this issue by installing BOTH DacFramework (x86 and x64)! – mr_squall Aug 17 '16 at 07:04

haldyr · Answer 2 · 2017-02-21T08:10:30.273

24

To fix import error with [sys].[script_deployment_databases] from exported V12 Database you have to install:

CU13
Microsoft SQL Server Data-Tier Application Framework (February 2015) (you must install BOTH the x64 and x86 versions).

EDIT: CU13 is not necessary, just try second link first!

edited Feb 21 '17 at 08:10

answered Mar 09 '15 at 07:31

haldyr

341
1
6

7

For me the second part helped. Thank you! – Sergey Jul 01 '15 at 20:41
Worked for me! Thanks! – ZeroBased_IX Oct 01 '15 at 12:44
@Sergey I was sceptical, but alas, glad to be proven wrong. – Phil Cooper Jan 13 '16 at 19:22
In my case, CU13 was not needed, but the Data-Tier application framework was (only installing the 32 bit version worked ; I use the 32 bits version of SQL server express 2014 and SSMS) – youen Jul 25 '16 at 14:20
#2 fixed first issue but introduced another which was like "the internal target plates does not support schema file version 3.5..." I then installed SSMS v17 which worked – Atta H. Jan 08 '18 at 18:17

score 5 · Answer 3 · answered Mar 26 '15 at 19:47

5

Install the following and it will work!
1) Have you installed Cumulative Update 5 for SQL Server Management Studio 2014. http://support2.microsoft.com/kb/3011055
2) Microsoft SQL Server Data-Tier Application Framework (February 2015) (you must install BOTH the x64 and x86 versions). http://www.microsoft.com/en-us/download/details.aspx?id=45886

answered Mar 26 '15 at 19:47

ActiveA

91
5

2

Key thing for me was adding both the x64 & x86 versions. Small but very vital key to get this working. Thank you! – Martin Noreke Aug 03 '15 at 15:53

score 2 · Answer 4 · edited Nov 06 '15 at 10:11

I had the same problem with my dataabse backup from SQL Azure (v12).

I've installed Microsoft® SQL Server® Data-Tier Application Framework (February 2015) (in order to work correctly you will need install BOTH the x64 and x86 versions). First I've installed x64 version and tried to restore the DB - but it didn't work. After that I've installed x86 version and I could successfully restore the database.

My SQL Server version: Microsoft SQL Server 2014 - 12.0.2269.0 (X64). OS: Windows 10 x64 build 10240.

Tnanks.

score 2 · Answer 5 · answered Jun 01 '16 at 16:39

2

BacPac restore from Azure DB fails after installing latest SQL Server Management Studio 2016 Preview. Solution was to set "Contained Database Authentication" = 1 for my local SQL DB instance. Read about solution here or run this script on your local instance:

USE master GO RECONFIGURE GO sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1

answered Jun 01 '16 at 16:39

CardiacTwinky

81
1
3

This worked for me!!! perfect thank you. For anybody else reference This fix worked for me and I'm running v17.9.1 of MSSMS. Although I did have to run the statements individually without the GO's. Thank you – DubMan Jan 23 '19 at 21:32

score 2 · Answer 6 · answered Aug 08 '16 at 06:51

I was using wrong SqlPackage.exe path.

Does not work:

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe

Works:

C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe

score 1 · Answer 7 · edited May 23 '17 at 12:18

1

I'm having the same issue.

Installing Cumulative update package 6 as suggested here helps to avoid the first bug but just to get stuck in the next one.

Unable to import SQL Azure V12 BACPAC: "type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid"

8 Answers8

Linked