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.

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 🙂
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

Book Review – Microsoft Power BI Performance Best Practices

I was asked to do a review of the book Microsoft Power BI Performance Best…

2 days ago

Create a Numbers Table in Power Query

This is a quick blog post, mainly so I have the code available if I…

7 days ago

Microsoft finally adds Tenant Switcher for Fabric / Power BI

Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…

1 week ago

Book Review – Humanizing Data Strategy by Tiankai Feng

This book was making its rounds on social media, and the concept seems interesting enough…

1 week ago

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

3 weeks ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

1 month ago