We’ve all been there. You made some adjustments to your Tabular model and you deploy it to the production server using Visual Studio. Whoops, you forgot to change the server name in the data sources, so now your Tabular model in production is trying to read from the test database. Either it crashes – depending on your impersonation settings – or it doesn’t and it can take a while before someone notices the data in production is a little off.
There are many ways to avoid this scenario. The first one is to not deploy from Visual Studio at all, but rather use a script or the wizard. But you know those rogue BI developers, there will always be one who just right-clicks the Tabular project in Visual Studio and chooses “Deploy”. You could also use configurations in Visual Studio, which were introduced in Tabular 2016. You can find more info in this article. But of course you can still forget to pick the correct configuration before you deploy.
So I created a little script that can be executed in a SSIS script task. You execute it right before you start processing your models in your daily/nightly/hourly/weekly/… batch run. It will loop over the databases in your Tabular server using the Tabular Object Model (TOM), inspect the connections and make sure they are all set to point to the correct server. In the script, I assume SSAS is installed on the same server as the data sources. If this isn’t the case, it’s pretty easy to change. The server name is passed along as a SSIS project variable.
To make the script work, you need to install the correct libraries. More info can be found here. The script itself can be downloaded from Github.
------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
You could also disable the deployment rights of (rogue) BI developers and set up a CI/CD build orchestrator (like VSTS) with a build step that runs your script.