Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Retrieve SSAS Properties Using Analysis Services Management Objects and SSIS


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

Problem

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?

Solution

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

Requirements

  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,
 CONSTRAINT [PK_ssas_properties] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO	

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.

variables

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:

script

Select the following user variables and click OK:

compatibility level

Press the Edit Script button:

write variables

In the code, expand the namespace section:

namespace

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:

extensions

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

    try
    {
        // 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.

toolbox

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) 
   [id]
  ,[Database name]
  ,[CompatibilityLevel]
  ,[LastProcessed]
  ,[LastUpdate]
  ,[State]
FROM [Adventureworks].[dbo].[ssas_properties]			

The following values will be displayed:

messages

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 ssas.properties.

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools