title: Is it possible to transform connection strings when deploying reports? (SSDT BIDS For VS 2012/SSRS)
body: <p>Firstly, this is not my area of expertise, so I apologise in advance for my lack of intuition and naivety when it comes to this sphere. I am merely trying to help out a colleague. Also, please let me know if this fits better on the DBAdmin StackExchange site. Apologies and disclaimers notwithstanding... </p>
  
  <p>I have been watching a non-programming colleague of mine struggle endlessly with hard coded connection strings in his datasets and projects in 2008 Business Intelligence Development Studio (BIDS) during creation of Reports. It has reached the point whereby he maintains three versions of absolutely everything (one for each of our environments, with specific connections hard-coded into each). In the last few days, he has taken the plunge and upgraded to SSDT BIDS for VS 2012.</p>
  
  <p>Edit to add:</p>
  
  <hr>
  
  <p>He has a single report server instance, where he has three versions of everything for each of the environments that connect to it, he doesn't have a report server for each of the environments. So, to publish everything correctly, he has to maintain three identical (bar the connection string) projects and datasets and modify all three when a change is needed. What he'd like to be able to do, is not to have to maintain three report projects, but merely change a setting and redeploy his report for that environment. I think we're swimming upstream on this issue and are willing to consider redesigning the architecture of our reporting.</p>
  
  <hr>
  
  <p>Coming from the web sphere, my initial blind thought was: if you're using Visual Studio, why not store the connections strings in config files and transform them on publish using <a href="http://visualstudiogallery.msdn.microsoft.com/69023d00-a4f9-4a34-a6cd-7e854ba318b5" rel="nofollow">SlowCheetah</a> to transform any type of config file? The answer that came back from the IDE was: not so fast, seeing as connection strings are stored within the user interface, inside Properties windows of the objects themselves.</p>
  
  <p>Soldiering on through endless web searches, I discovered another technique called <a href="http://msdn.microsoft.com/en-us/library/ms156450.aspx" rel="nofollow">Expression-based Connection Strings</a> (bottom of the page), but this seems a little insecure to my untrained eye, but is this what I have been looking for?</p>
  
  <p>I appreciate that this is not necessarily the archetypal SO question, but if anyone has any knowledge to impart on how to do transformed deployments in BIDS so that we can reduce our duplication threefold, I would be eternally grateful.</p>