SSIS Task: Process SSAS Cube and Parametrize Connection Strings

Question

I want to process an SSAS Cube in SSIS. Is there a way to parametrize connection strings for Datamart SQL Server data source? I want to be able to set/configure SQL Server Connecting strings for our SSAS Dev, Test, and Production Environments in Devops.

Currently datamart cubes have connections hardcoded in SSAS, SSAS does not seem to have project connection strings like SSIS.

Update:

I heard something about in SSIS ---> Analysis Services Execute DDL Task --> running an XMLA script to change the database connection string. Not sure how to conduct this.

Can someone provide direction or trim down this XMLA script to only change connection string (Sql server and Database name)? Just want to only change what is necessary. I am using SSAS 2016 so might need to update the schema xmlns.

Also receiving this error: Have SQL Server 2016 and 2016 SSAS

Errors in the metadata manager. The object definition supplied for the ALTER statement is of a different type that the object reference to be altered.

How would I fix this?

<Alter ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
    <Object>  
        <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
        <DataSourceID>AdventureWorksDW2012</DataSourceID>  
    </Object>  
    <ObjectDefinition>  
        <DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">  
            <ID>AdventureWorksDW2012</ID>  
            <Name>AdventureWorksDW2012</Name>  
            <ConnectionString>Data Source=fr-dwk-02;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=True</ConnectionString>  
            <ManagedProvider>System.Data.SqlClient</ManagedProvider>  
            <Timeout>PT30S</Timeout>  
        </DataSource>  
    </ObjectDefinition>  
</Alter>  

Following link below from Microsoft is using 2001-2003 schema and changing timeout seconds. I only want to change database source and server.

• Creating and Altering Objects (XMLA)

This is for TFS, however want to utilize SSIS:

• Deploy SSAS Project using TFS Command Line

Answer 1

Go to SSMS ---> Analysis Services

Script Datasource --> Alter to ---> New Query Window

Can change data source here

Copy Alter Scripts command into variable in SSIS, and use 'Execute Analysis Services DDL Task'