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'

Answer 2

This can be done by storing the connection string DDL as the expression in an SSIS string variable and using a parameter (or variable) within this to hold the updated connection string. On the Analysis Services Execute DDL Task change the SourceType property to Variable and select the variable holding the connection string expression in the Source field. In the example expression below a package parameter ($Package::ConnectionString) is concatenated within the expression using the + symbols. An expression can be defined for a variable by clicking the ellipsis below the Expression field of the Variables pane in SSDT then entering an expression within double-quotes. All double-quotes and back slashes (\) inside the expression, not including the opening and closing double-quotes, will need to be escaped by a back slash character. If you haven't worked with expressions in SSIS before, I'd recommend verifying the result of expression using the Evaluate Expression button on the Expression Builder. This will show how the expression will be parsed during execution, however this can change depending on the parameters/variables within the expression and their values.

"<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>" + @[$Package::ConnectionString]   +  "</ConnectionString>  
            <ManagedProvider>System.Data.SqlClient</ManagedProvider>  
            <Timeout>PT30S</Timeout>  
        </DataSource>  
    </ObjectDefinition>  
</Alter>"