In many shops, I am guessing there are multiple servers. Development, Production, maybe a Staging, etc.
With SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS), you set up data sources, connection to databases. In SSAS you usually have a connection which then you build your data source view off of, and in SSIS you have connections from which you push data to and pull data from.
Another thing, in SSAS you can “deploy” right from Visual Studio (BIDS). All these things have a server name. What we have run into is this:
You develop on your local machine, pointing at development server. You deploy to development, your connections are pointing to development, and everything works great. When you deploy to production (usually planned, every 2 weeks, or whatever) you deploy your stuff and what ends up happening?
In SSIS your config files should have a connection string (or however you store it) and it should point to production. But in SSAS, if you deploy from BIDS, your data source will have to change and in the cube project properties you need to change your deployment server.
I have seen countless times, a cube or a connection in SSIS without a config that is running in production, yet pointing at development. We keep our dev data as fresh or very close so sometimes we don’t even notice, but then it happens, something weird is reported and we dig into it, and we find the erroneous connection string.
Here is my solution to the problem:
Developers – go to C:WindowsSystem32DriversEtc and open your Hosts file with notepad or text editor. You then add a couple of entries
#production
#xxx.xxx.xxx.xxx datawarehouse
#development
yyy.yyy.yyy.yyy datawarehouse
where xxx is the ip of your production system, and yyy is the ip of your dev system. the # is the rem/comment out symbol. You can see above I have everything commented out but the line for the dev system. But notice each is pointed to “datawarehouse” so if I ping or connect to “datawarehouse” from Management Studio, or whatever, it goes to the IP I have commented out.
Now, go on to each server, but only add the line that corresponds to that server in the hosts file, or better yet just
127.0.0.1 datawarehouse
Now, when you deploy to either server, and your connections, etc are set to connect to “datawarehouse” you ensure it will always connect to the local server. Brilliant!
Steve Novoselac's Blog 
4 replies on “Dev and Prod Systems, Using a HOST file to ensure your datasource is pointing the right system”
I would recommend you actually look into using package configurations rather than messing with the hosts files:http://msdn.microsoft.com/en-us/library/ms14168…As a former sysadmin, trying to make sure host files are kept up to date correctly is a logistical nightmare.Regards,B
LikeLike
yes, we use package configs, still then you have to maintain package configs everywhere, you just push the problem. With the HOSTS file solution, your connection strings between all systems can be the same.
LikeLike
Also, another concept to consider is using Visual Studio configurations to control the SSAS datasource:http://www.artisconsulting.com/blogs/greggallow…
LikeLike
Steve,Be careful with tinkering with the hosts file. As noted by b4pjs, maintaining the hosts file can be troublesome, as well as it can cause some maintenance issues if the fact that the hosts file has been edited is not well known.Also, having to set the hosts file points to a potential name resolution problem on the network. It might be worthwhile to troubleshoot why the DNS resolution isn't happening properly rather than fixing the symptom.The hosts file should really be retired.. it was meant for a simple networks where DNS wasn't needed.
LikeLike