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
