MS Web Deploy Package ignoring databases when using connectionstring configsource

Question

I am having a problem with Web Deploy packages recognizing SQL. I am using Visual Studio 2015, SQL Server 2014, SSDT Database Project. The workflow is, create a MVC project, create a Database project, and use the Web Deploy Package to run SQL queries on the DB project. I plan to Publish the MVC project into a web deploy package and add queries to the deployment as part of the package. (I am not trying DACPACs at the moment because I ran into issues where they were not including pre and post deployment scripts in them when using Web Deploy Tool).

The problem happens when publishing a web deploy package using the connection string below.

  <connectionStrings configSource="connectionsettings.config">

That in the web.config prevents web deploy from detecting a database to add to the web package for web deploy packaging.

deloy package missing database

Whenever I change to a normal connection string, even something as simple as this:

  <connectionStrings>
<add name="Default" connectionString="" providerName="System.Data.SqlClient" />

Then web deployment package works and you get the proper UI.

deloy package working

Anyone know how to get the database to show up using a configsource in the web.config connectionstrings? As an alternative, I was looking into getting parameters.xml to replace the blank connection string to include the configSource above, but have had no luck.

The idea was to remove the Default connection and replace it with the config source. Removing the default connection works but the configSource does not.

  <parameter name="RemoveConnectionString" defaultValue="">
    <parameterValidation kind="AllowEmpty" />
    <parameterEntry kind="XmlFile"
      scope="Web\.config$"
      match="//configuration/connectionStrings/add[@name='Default']" />
  </parameter>

  <parameter name="AddConfigSource" defaultValue="connectionsettings.config">
    <parameterEntry kind="XmlFile"
      scope="\\web.config$"
      match="/configuration/connectionStrings/@configSource" />
  </parameter>

Web Deploy Package seems undeveloped for SQL, but is there a fix for my issue or a better way to do an all-in-one deploy for IIS & SQL?

Answer 1

Use WebDeploy Parameterization to manually create parameters for the separate config file.

Skip down to "Step 3 - Create a parameter file" in the following link:

http://www.iis.net/learn/publish/using-web-deploy/web-deploy-parameterization

This may also be helpful:

http://www.dotnetcatch.com/2014/09/08/parameterizationpreview-visual-studio-extension/