Categories: SSASSSIS

Change SSAS Tabular Data Source using TOM

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.

Afbeeldingsresultaat voor facepalmAfbeeldingsresultaat voor facepalm

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 🙂
FacebookFacebookMastodonMastodonEmailEmailShareShare
AddThis Website Tools
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

View Comments

  • 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.

Recent Posts

Execute Fabric Data Pipeline from Azure Data Factory

In the blog post Call a Fabric REST API from Azure Data Factory I explained…

2 weeks ago

Azure Data Factory Pipeline Debugging Fails with BadRequest

I recently had a new pipeline fail. It was actually a copy of an old…

1 month ago

Call a Fabric REST API from Azure Data Factory

Suppose you want to call a certain Microsoft Fabric REST API endpoint from Azure Data…

1 month ago

Cool Stuff in Snowflake – Part 14: Asynchronous Execution of SQL Statements

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse).…

2 months ago

How I passed the DP-700 Exam

I recently took and passed the DP-700 exam, which is required for the Microsoft Certified:…

2 months ago

Take over Ownership in Microsoft Fabric

When you create an item in Microsoft Fabric (a notebook, a lakehouse, a warehouse, a…

3 months ago