Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vk_pbi
Resolver II
Resolver II

PowerShell Script to export Database Connection strings in XMLA

Hi there,

I am working on a automation project, where i have a requirement to update the connection strings and credentials via powershell.

The process flow would likely to be 

>Connect SQL Server Analysis Services

>Select an Database

>Export the connection details into a xmla file.

 

Currently we are doing it manually via SSMS

> Opening SSMS --> Connect to SQL Server Analysis Services

>Select required Database

>Expand Connections folder --> Select and Right click a connection

>Right Click --> Script Connection as --> CREATE OR REPLACE To --> New Query Editor Window

 

This will generate a xmla scirpt of that connection. I want to achieve this process via powershell.

 

Thanks

10 REPLIES 10
lbendlin
Super User
Super User

How is this question related to Power BI?  I assume you are aware of the Power BI REST API?

Its not directly related.. Yes I am aware of Power BI Rest Api, but it doesn't work with Onprem report server.

So I need to achieve this without using webservice

Run two DMV queries. 

 

select * from $SYSTEM.TMSCHEMA_PARTITIONS

select * from $SYSTEM.TMSCHEMA_EXPRESSIONS

How can i run these queries ?

You can run them from any xmla client tool, including Powershell.

Invoke-ASCmd -Server "powerbi://api.powerbi.com/v1.0/myorg/<workspace>" -Database "<dataset>" -Query "select * from `$SYSTEM.TMSCHEMA_PARTITIONS" | Out-File -FilePath c:\users\<user>\Downloads\dmv.xml

Hi @lbendlin 

I tried your code, but it throws error like System Cube not available. I am accessing this command on Onprem Report server.

Not sure about the syntax there but it should work the same if you properly specify server and cube

If powershell is not a requirement maybe you could think to use Tabular Editor and a combiantion of Advanced Scripting and Command Line interface.

I've not tested with your usecase but I did something similar in the past: https://medium.com/p/49e8f4a236f9

Riccardo Perico
BI & Power BI Enginner @ Lucient Italia

Blog | GitHub

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your suggestion, but currently I can't use Tabular Editor due to some limitations.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.