title: Proper way to set connection strings in ci during msbuild/deploy
body: <p>I have a CI server (Bamboo, but I don't think that it matters) building and auto-deploying my application. During local development I use localdb (<code>&lt;connectionStrings&gt;</code> node in my <code>web.config</code>)</p>
  
  <pre><code>    &lt;add name="MyApp" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0;Integrated Security=True;Connect Timeout=30;Initial Catalog=MyApp" /&gt;
      &lt;add name="MyApp_Patients" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0;Integrated Security=True;Connect Timeout=30;Initial Catalog=AppleHms_MyApp" /&gt;
      ...
  </code></pre>
  
  <p>When deployed obviously it shouldn't use this. It should use my deployment sql server connection string.</p>
  
  <p>I know that I can write a web.config transform for this and I even know that I can encrypt the web.config but I'm not sure how the db connection-string is supposed to get in there properly. What makes sense to me is for the CI server to overwrite each connectionString - so my config transform should maybe look something like this</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform"&gt;
    &lt;connectionStrings&gt;
      &lt;add name="MyApp" providerName="System.Data.SqlClient" connectionString="${main-db-connectionstring}"  xdt:Transform="SetAttributes" xdt:Locator="Match(name)" /&gt;
      &lt;add name="MyApp_Patients" providerName="System.Data.SqlClient" connectionString="${patients-db-connectionstring}"  xdt:Transform="SetAttributes" xdt:Locator="Match(name)" /&gt;
      ...
    &lt;/connectionStrings&gt;
    &lt;system.web&gt;
      &lt;compilation xdt:Transform="RemoveAttributes(debug)" /&gt;
    &lt;/system.web&gt;
  &lt;/configuration&gt;
  </code></pre>
  
  <p>With the overrides coming from variables in my ci server (presumably some part of msbuild or deploy)?</p>
  
  <p>Is that correct that this is the "right" way of doing this? What exactly do I have to do with msbuild/deploy to make this happen?</p>