Currently I can easily setup Web.config transform based on build configuration, e.g. use connectionString=server;.. for Debug and connectionString=./SQLExpress;.. for Release.
But is it possible to do some Web.config transformation basing on web publish profile? I.e. use connectionString=server1;.. for profile Server1 and connectionString=server2;.. for Server2 ?
I believe that the publishing profiles are independent of the build profiles, which is a bit of a shame, as you could easily accidentally deploy a debug configuration to your production servers.
However, if you're using MSDeploy, there are ways to modify the web.config in there. See MSDeploy - Changing Connection string parameter after deploying the package for more details.
There could be a slightly different way to do this.
On your production servers create a dummy entry, for customdb, in the c:\windows\system32\drivers\etc\hosts file on each of the production machines. Each one pointing to the database that you want that machine to use. Then you only have to point to the connectionString=customdb; for all your production servers.
Only downside of this would be that you would need access to the hosts file and it would require you to use a db.
Hope this helps
We keep all machine/profile specific configuration in separate config files, then use configSource to include them like so...
<connectionStrings configSource="cstrings.config"/>
This way Web.config is the same and doesn't require any transformations. We do this for connection strings, smtp settings and app settings.
We version control Web.config and "machine specific" files such as cstrings.config.production, cstrings.config.staging, etc.
Once you have this structure it's easy to generate images for different profiles. We have deployment scripts on each machine that read an environment variable and deploy appropriately. For example, the staging server build script copies cstrings.config.staging to cstrings.config, etc.