ASP.NET MVC 5 DACPAC Sql140DatabaseSchemaProvider is not valid

Question

I get this error when I run dacpac from VS Community 2017 MVC 5 project.

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

The code

            var dacpacName = "setup.dacpac";                 
            var dacpacPath = Path.Combine(Server.MapPath("~/assets/dacpac"), dacpacName);
            var dp = DacPackage.Load(dacpacPath);
            var dbDeployOptions = new DacDeployOptions
            {
                BlockOnPossibleDataLoss = false,
                ScriptDatabaseOptions = false,
                GenerateSmartDefaults = true,
                CreateNewDatabase = true
            }; 
            var dbServices = new DacServices(setupDbConn.ConnectionString);
            dbServices.Deploy(dp, newDatabaseName, true, dbDeployOptions);

The error happens after this code:

var dbServices = new DacServices(setupDbConn.ConnectionString);

My SqlExpress is 2017. I've regenerated the dacpac from SqlExpress 2017 as well. The dacpac works without error when I use it in SqlExpress.

I've been googling for hours but can't seem to find the right answer to this. I'm thinking this is some kind of compatibility issues but can't figure out how to fix the error.

Hope someone have experienced this and can help me fix this issue.

Answer 1

I've tried everything I can think of. I've installed different versions of dacfx. Also tried generating dacpac from 2008. All with no luck.

I then resorted to other ways instead of using dacpac. I've generated a script for recreating all the tables and run that instead.

Server myServer = new Server(serverName);

//Using windows authentication
bool integratedSecurity = Convert.ToBoolean(ConfigurationManager.AppSettings["integratedSecurity"]);    
myServer.ConnectionContext.LoginSecure = integratedSecurity;        
myServer.ConnectionContext.Connect();    

// check that database doesn't already exists
if (myServer.Databases.Contains(newDatabaseName))
{
    return new HttpStatusCodeResult(400, "Database has already been created");
}

//Define a Database object variable by supplying the server and the database name arguments in the constructor.   
Database db;
db = new Database(myServer, newDatabaseName);

//Create the database on the instance of SQL Server.   
db.Create();

string dbscript = System.IO.File.ReadAllText(Server.MapPath("~/assets/dbscript/createAllTables.sql"));    
myServer.Databases[newDatabaseName].ExecuteNonQuery(dbscript);
myServer.ConnectionContext.Disconnect();