Add role to Analysis Services Database

Does anyone know if there is a way to use a SSIS task to add a role to SSAS cube? At the moment I use Management Studio, Right Click "Roles" under the cube and go through New Role wizard, but I'd like to be able to include this in a SSIS package instead.

Thanks

Richard

You can do it through the Script task, employing the AMO (Analysis Management Objects) library. I recommend creating the role in SSMS first but instead of submitting the change to the system, clicking the Script button at the top of the New Role dialog box to view the XMLA\ASSL script it creates. The script can be a guide for your AMO development (as AMO is just a wrapper for the XMLA\ASSL languages).

Good luck,

Bryan

|||

Following along with what Bryan had mentioned you could script a role using Management Studio to use as a template then use and expression variable and an Analysis Services Execute DDL Task in SSIS to accomplish this as an ongoing solution. The Analysis Services Execute DDL Task will allow you to customize and execute the role creation/modification command and avoid having to use the script task.

Hope that helps!

|||

Thanks! Wow. Cool. Kind of got it to work but don't know quite how!

In SSMS scripting the role worked nicely and was able to run it in a query window and it did exactly what was needed.

I'm pretty useless at VB.NET so trying the Script Task in SSIS, I hit design script, added AMO as a reference and pasted the SSMS script where it says "Add your code here" inside of Public Sub Main(). Of course it doesn't work that simply. But is there a really simple answer to what I need or am I going to have to go off and learn some VB.NET ?

So then I tried Execute DDL task, and muddled my way around it. I save the original script as a .xmla file in the file system. Then in DDL task under DDL selected File Connection as SourceType and connected to this file. Executed task and it did what was needed.

I guess this is good enough as it works, but I wouldn't mind understanding better what I am doing!!

Thanks

Richard

|||

Here is a code sample from a past project where we created a role for each entry in a table. (Just sharing that last part so you understand the database query in the code.)

Code Snippet

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Public Sub Main()

Dim server As New Microsoft.AnalysisServices.Server

Try

' CONNECT TO THE SQL SERVER ANALYSIS SERVICES (SSAS) SERVER

server.Connect("localhost")

' THE SSAS DATABASE TO CONNECT TO

Dim database As New Microsoft.AnalysisServices.Database

database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' GET A LIST OF ROLES TO CREATE

' Connection String comes from the 'METADATA' Connection Manager

Dim myConnection As New SqlConnection(Dts.Connections.Item(0).ConnectionString.ToString())

Dim myCommand As SqlCommand = New SqlCommand("SELECT ID FROM dbo.Roles (NOLOCK) WHERE ID > 0", myConnection)

myConnection.Open()

' FILL THE DATAREADER

Dim dr As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

' LOOP THROUGH THE DATAREADER AND BUILD A ROLE

If (dr.HasRows) Then

While (dr.Read())

CreateRole("Role for ID ", dr("ID").ToString, database)

End While

End If

' CLOSE THE DATAREADER

dr.Close()

' CLOSE THE SQL SERVER DB CONNECTION

myConnection.Close()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)

Finally

' DISCONNECT THE SSAS SERVER

server.Disconnect()

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

Private Sub CreateRole(ByVal rolePrefix As String, ByVal dsNumber As String, ByVal asDatabase As Database)

Try

' WILL CREATE A ROLE IN THE ASSIGNED DB WITH THE NAME AND KEY VALUE

Dim newRole As Role

' CREATE THE NEW ROLES NAME

newRole = asDatabase.Roles.Add(rolePrefix + " " + dsNumber)

newRole.Description = "Role for " + rolePrefix + " " + dsNumber

newRole.Update()

Catch ex As Exception

Throw New Exception(ex.Message.ToString())

End Try

End Sub

End Class