Update SSAS Tabular connection string during azure pipeline release

Question

We are deploying Tabular SSAS instance as part of on-premise azure build and release pipeline, using Microsoft.AnalysisServices.Deployment.exe with .asdatabase file.

I am trying to figure out a way to update (relational DB) data source connection string during the release stage.

Most of the solutions I could find use SSIS, which we don't, so I would strongly prefer not to introduce it for this task.

I can see that connections are defined in .asdatabase file:

"dataSources": [
  {
    "name": "sourceDB1",
    "connectionString": "Data Source=xxxx",
    ...
  },
  {
    "name": "sourceDB2",
    ...
  }
 ]

I use this powershell script to update connection strings :

if($args[0] -eq $null)
{
    Write-Host "Updates .asdatabase connection string. Parameters: %source name% %source connection string%"
}
else{

    $source=$args[0]
    $connectionstring=$args[1]

    write-host "updating "  $source  " connection string..." 

    $a = Get-Content 'Model.asdatabase' -raw | ConvertFrom-Json
    $a.model.datasources | % {if($_.name -eq $source){$_.connectionString=$connectionstring}}
    $a | ConvertTo-Json -depth 100| set-content 'Model2.asdatabase'

    If ($?)
    {
        write-host "updated successfully"
    }
}

Is there a more sane/sustainable approach?

Related Question: Continuous integration and Deploy SSAS tabular to Azure Analysis Services

Hi, friend, may I know how's the status of this? Free to share your comment or any of question below:-) — Mengdi Liang
– Mengdi Liang, Commented Feb 25, 2020 at 10:31
Hi Merlin, thanks for your "replace text" extension suggestion.For now I use the powershell script above, it's safer as it parses the json, as opposite to direct string replacement. I would still prefer a cleaner / more reliable approach, but haven't found one yet. — user5226582
– user5226582, Commented Feb 25, 2020 at 14:07

Mengdi Liang · Accepted Answer · 2020-02-19 03:52:52Z

What about consider to make use one extension which name is Replace Text in Source Files.

It contains 2 method, and the first one is replace by search pattern.

Search Start of Text

As the description says you're looking in each line of the source code for a text starting with what is entered here.

Sample Line -> [assembly: AssemblyInformationalVersion("1.1.1.1")]

So if you enter 'AssemblyInformationalVersion("' it will look for a line containing that text and stopping just at the end before the version 1.1.1.1.

Search End of Text

As the description says the task is looking for the 'End of Text' but only on the lines where the Start of Text already found a result.

Sample Line -> [assembly: AssemblyInformationalVersion("1.1.1.1")]

So if you enter '")]' it will look for the position just at the end of version 1.1.1.1.

Also, you can use the another task Replace In Files Text By Text. What its work logic is same with replace A with B directly, so it needs you input the original connection string value every times.

Just based on your scenario and your actual considering to choose one of them.

StuartLC · Accepted Answer · 2024-01-02 17:17:57Z

Seemingly the Microsoft.AnalysisServices.Deployment.exe tool hasn't changed since 2014, and is still able to deploy SSAS 2022 (Compatibility 1600) Models.

So to change the upstream inbound datasources on the Cube database, instead of attempting to patch the Model.asdatabase json file at deploy time, what you can instead do is add a Model.configsettings xml file to your deployment artifacts (alongside Model.deploymentoptions and Model.deploymenttargets).

If Microsoft.AnalysisServices.Deployment.exe finds a Model.configsettings file, it will overwrite the values in the JSON Model.asdatabase file as it deploys the model.

An example of this file (connecting to an upstream SQL Server using the SSAS Service Account):

<ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Database>
    <DataSources>
      <DataSource>
        <ID>MySSASDataSource</ID>
        <ConnectionString>Provider=MSOLEDBSQL;Data Source=MyServer;Persist Security Info=false;Integrated Security=SSPI;Initial Catalog=MySourceDB</ConnectionString>
        <ImpersonationInfo>
          <ImpersonationMode xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">ImpersonateServiceAccount</ImpersonationMode>
          <ImpersonationInfoSecurity xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">Unchanged</ImpersonationInfoSecurity>
        </ImpersonationInfo>
      </DataSource>
    </DataSources>
  </Database>
</ConfigurationSettings>

Notes:

There's no need to provide 100's of XML namespaces - as with any XML file, only need to provide the xmlns actually used in the file.
The format seems to have changed since Compatibility 1400 - notably, the MySSASDataSource in the ID element needs to match the friendly name of the upstream datasource (it used to be referenced by as a Guid in the older XML Model.bim format)
Model.configsettings doesn't seem to be used at design time by VS / SSDT, so you can tokenize it in your code repo and substitute the connection string in your CI/CD pipeline without breaking your development cycle.

Collectives™ on Stack Overflow

Update SSAS Tabular connection string during azure pipeline release

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related