.xmla deployment to SQL using C#

Question

I use Invoke-ASCmd in PowerShell right now to create a database in SQL Server, like this:

Invoke-ascmd -Query $MyScript -Server $ASServer

Where $MyScript is a string holding the contents of an .xmla file that I read in previously.

This works great. Now I need to do something similar in C#, but am unable to find a simple solution like the one that exists in PowerShell.

I see some people using a Microsoft DLL called Microsoft.AnalysisServices.XMLA.dll, but it's not supported, and the class in question is "internal", so I can't even reference it.

I found this DLL Microsoft.AnalysisServices.AdomdClient.dll while searching around, but don't see any of the classes being relevant to what I need: https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.adomdclient?view=analysisservices-dotnet

Have you tried using Microsoft.AnalysisServices.dll, as this link's selected answer did...https://stackoverflow.com/questions/33260674/run-xmla-file-analysis-services-from-command-line — quaabaam, Jul 30 '19 at 18:34
The commands in Power Shell are really running the sqlcmd.exe command line utility (https://learn.microsoft.com/en-us/sql/tools/command-prompt-utility-reference-database-engine?view=sql-server-2017). So you can either setup a bat file with the same commands that are in your Power Shell and execute with Process class or make the Power Shell Parameters Parameters to the Process class. I usually put the output of sqlcmd.exe into a CSV file and then read the results of the csv into my c# program. — jdweng, Aug 02 '19 at 14:17

score 1 · Accepted Answer · answered Aug 05 '19 at 16:56

1

using Microsoft.AnalysisServices.AdomdClient;

try
{
    var xmlaFileContents = File.ReadAllText("path/to/your/file.xmla");

    using (AdomdCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = xmlaFileContents;
        cmd.ExecuteNoQuery();
    }
}
catch(Exception)
{
}

** please note that I have not run this code **

answered Aug 05 '19 at 16:56

john-g

874
6
14

I'll try this. Thanks! – coinbird Aug 06 '19 at 13:30
1

fyi my answer was pieced together from this MSDN [forum post](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c4c28b83-bc79-4400-bae9-709f533d66fb/how-to-run-xmla-file-in-c-sharp?forum=sqldatamining). – john-g Aug 07 '19 at 15:03

score 1 · Answer 2 · answered Aug 06 '19 at 15:17

As the AdomdConnection is inherited from IDbConnection, it's pretty similar to how the SqlConnection works, and therefor, similar syntax can be used, as @jogi presented for you.

I wrote a PS function a few years back which we use in TFS builds. It uses the .NET assemblies rather than the PS layer, so I figured since you seem savvy in PS, you can perhaps get something out of it. Still essentially the same as what @jogi wrote, only wrapped in PS.

function Invoke-XmlaScript {
[CmdletBinding()] param (
    [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$ServerInstance,
    [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string]$XmlaScript
)
process {
    $connection = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=$ServerInstance;Provider=MSOLAP.4;Integrated Security=SSPI;Impersonation Level=Impersonate;")
    $connection.Open()
    try {
        $command = $connection.CreateCommand()
        $command.CommandTimeout = 20000
        $command.CommandType = [System.Data.CommandType]::Text
        $command.CommandText = $Xmla
        $reader = $command.ExecuteXmlReader()
        if($reader.Read()) {
            Write-Output $reader.ReadOuterXml()
        }
    }
    catch { }
    $connection.Dispose()
}

}

.xmla deployment to SQL using C#

2 Answers2