Retrieve SSAS Properties Using Analysis Services Management Objects and SSIS

By:   |   Updated: 2018-03-28   |   Comments   |   Related: > Analysis Services Administration


I want to be able to query details about my SQL Server Analysis Services (SSAS) database and automate this process with a SQL Server Integration Services (SSIS) Package. How can this be done?


In this tip, we will talk about AMO (Analysis Services Management Objects) that you can use to get values of SSAS objects, process databases, cubes, dimensions, etc. You can also create objects, drop objects and more. We will use C# to get SSAS properties and store the values in a SQL Server table.

In this example, we will do the following:

  1. Create a SQL Server relational table to store the SSAS properties
  2. Create a SSIS package and variables to get SSAS values
  3. In SSIS we will use the script task to write C# code
  4. Add the Microsoft.AnalysisServices reference
  5. Get the values and store in the variables using C#
  6. Use the Execute SQL Task to store the values in the table


  1. SQL Server 2008 or later. In this example, we are using SQL Server 2016.
  2. SSDT installed
  3. An AdventureworksDW database
  4. A SSAS Adventureworks database installed
  5. SSIS installed

Building the SSIS Package

Create a SQL Server relational table to store SSAS properties

We will first create a table named ssas_properties in the SQL Server database engine:

CREATE TABLE [dbo].[ssas_properties](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [Database name] [nchar](100) NULL,
   [CompatibilityLevel] [int] NULL,
   [LastProcessed] [datetime] NULL,
   [LastUpdate] [datetime] NULL,
   [State] [smallint] NULL,
   [id] ASC

In this table named ssas_properties we will store the following SSAS properties:

  1. An id
  2. The database name
  3. The compatibility levels
  4. Last Process date
  5. Last Update date
  6. The state of the database

In SSIS create variables to get SSAS values

In SSDT, create a new SSIS project.

In the SSIS project go to the SSIS menu and Variables and create the following variables. These variables will be used to store the SSAS properties.


In SSIS we will use the script task to write code

In the SSIS project drag and drop the Script Task to the design pane:

script task

On the Script page, press the browse button in the ReadWriteVariables as shown below:


Select the following user variables and click OK:

compatibility level

Press the Edit Script button:

write variables

In the code, expand the namespace section:


In the namespaces add the following namespace. This namespace will help us to work with SSAS.

using Microsoft.AnalysisServices;			

Add the Microsoft.AnalysisServices reference

In Solution Explorer, right click on references and select Add Reference:

In Assemblies, select Extensions and check the Analysis Management Objects as shown below:

add reference

By default, the Analysis Services Management Objects are installed with SQL Server 2016, if it is not installed, you can download them here: Microsoft® SQL Server® 2016 Feature Pack.

In the Reference Manager press Browse:


Add the Microsoft.AnalysisServices.Core.dll in SQL Server, it is usually in a path like the following:

  • C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.AnalysisServices.Core.DLL

Get the values and store in the variables using C#

In the Script Task go to the code section and add the following code:

public void Main()
    // TODO: Add your code here

        // TODO: Add your code here
        //Create a new Analysis Server
        Microsoft.AnalysisServices.Server DM_Server = new Server();

        //Create a new database instance
        Database AS_Database = new Database();

        //Specify connection properties 
        DM_Server.Connect("Data Source=local;Initial Catalog=Adventureworks");

        //Get the AS Database
        AS_Database = DM_Server.Databases["Adventureworks"];

        //Save the SSAS properties in SSIS variables
        Dts.Variables["User::databaseName"].Value = AS_Database.Name;
        Dts.Variables["User::compatibilityLevel"].Value = AS_Database.CompatibilityLevel;
        Dts.Variables["User::lastProcessed"].Value = AS_Database.LastProcessed;
        Dts.Variables["User::lastUpdate"].Value = AS_Database.LastUpdate;
        Dts.Variables["User::State"].Value = AS_Database.State;
    catch (NullReferenceException e)
        Dts.Events.FireError(18, "Null Error: ", e.ToString(), "", 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    catch (Exception ex)
        Dts.Events.FireError(18, "An error occurred: ", ex.ToString(), "", 0);
        Dts.TaskResult = (int)ScriptResults.Failure;

This code will get the database name, compatibility level, process date, last update date and database status and store in the variables that were created. These values will be stored later in a SQL Server table.

If there are errors, the catch functions will show the errors.

Use the Execute SQL Task to store the values in a table

Drag and drop the Execute SQL Task to the design pane and connect the script task with the Execute SQL Task as shown below.


In the connection specify the SQL Server name and the database name. In this example, we are using the local host as the SQL Server and Adventureworks as the database name. Adventureworks is the database where we created the table.  In the SQL Statement, we will write an insert statement that will insert the SSIS variables into a SQL Server table named ssas_properties:

INSERT INTO [dbo].[ssas_properties] VALUES (?,?,?,?,?)			

We are inserting into the table created at the beginning of the tip with the values of the 5 SSIS variables that were created. To do this, we need to use 5 "?", one for each of the 5 parameters:

task editor

On the Parameter Mapping page, add the 5 variables with the direction, data type and parameter name as specified below:

database name

Now that the package is complete, we can run the package:

.control flow

In SSMS, or any tool of your preference, run the following query against the ssas_properties table:

SELECT TOP (1000) 
  ,[Database name]
FROM [Adventureworks].[dbo].[ssas_properties]			

The following values will be displayed:


In this tip, we learned how to get SSAS properties using the SSIS Script Task with C# code and we stored the SSAS properties in SSIS variables and then inserted the variable values into a table named

Next Steps

Last Updated: 2018-03-28

get scripts

next tip button

About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips

Comments For This Article


Recommended Reading

Troubleshooting Common SQL Server Analysis Services Connectivity Issues

SQL Server Analysis Services SSAS Hardware Configuration Recommendations

SQL Server Analysis Services Cube Processing Status Report

How to change compatibility level of a SSAS Tabular Database using SSDT

How to restore a SQL Server Analysis Services Database

get free sql tips
agree to terms

Learn more about SQL Server tools