Get Analysis Services Object Property Task

Library references

• System
• System.Data
• Microsoft.SqlServer.ScriptTask
• Microsoft.SqlServer.ManagedDTS
• Microsoft.AnalysisServices
• CozyRoc.SSISPlus

ScriptMain

' Microsoft SQL Server Integration Services Script Task ' ' Copyright (c) 2008 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.ComponentModel Imports System.Collections Imports System.Windows.Forms Imports System.Reflection Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.AnalysisServices Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() {"Type", "Connection", "Database", "Cube", "Dimension", "Name", "ResultVariable"})> _ <FilterProperties("GetProperties")> _ <ValidateProperties("Validate")> _ Public Class ScriptMain ' Types of object supported. Enum ObjectType Server = 1 Database = 2 Cube = 3 Dimension = 4 End Enum ' ObjectType ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Sub Main() Dim result As Integer Dim propValue As Object Dim vars As Variables Try Select Case Me.Type Case ObjectType.Server ' Get server. Using server As Server = GetServer_() propValue = GetPropertyValue_(server) End Using Case ObjectType.Database ' Get database. Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) propValue = GetPropertyValue_(database) End Using End Using Case ObjectType.Cube ' Get cube. Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) Using cube As Cube = database.Cubes(Me.Cube) propValue = GetPropertyValue_(cube) End Using End Using End Using Case ObjectType.Dimension ' Get dimension. Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) Using cube As Cube = database.Cubes(Me.Cube) Using dimension As CubeDimension = cube.Dimensions(Me.Dimension) propValue = GetPropertyValue_(dimension) End Using End Using End Using End Using End Select ' Store property value in package variable. Call Dts.VariableDispenser.LockOneForWrite(Me.ResultVariable, vars) Try vars(Me.ResultVariable).Value = propValue Finally Call vars.Unlock() End Try result = Dts.Results.Success Catch ex As Exception result = Dts.Results.Failure Call Dts.Events.FireError( _ 0, _ String.Empty, _ ex.Message, _ String.Empty, _ 0) End Try Dts.TaskResult = result End Sub ' Main ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList ' Base properties. Call result.Add("Type") Call result.Add("Connection") Call result.Add("Name") Call result.Add("ResultVariable") Select Case Me.Type Case ObjectType.Server ' Nothing to do here. Case ObjectType.Database Call result.Add("Database") Case ObjectType.Cube Call result.Add("Database") Call result.Add("Cube") Case ObjectType.Dimension Call result.Add("Database") Call result.Add("Cube") Call result.Add("Dimension") End Select Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Dim props As Object() Try props = GetProps_() If String.IsNullOrEmpty(Me.Name) Then Throw New ApplicationException("Please specify property.") End If result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Object")> _ <Description("Specifies analysis services object.")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property Type() As ObjectType Get Type = m_object End Get Set(ByVal value As ObjectType) If m_object <> value Then m_object = value Me.Name = String.Empty m_cachedProps = Nothing End If End Set End Property ' Type ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Connection("AsConnectionType")> _ <Category("Analysis Services")> _ <Description("Specifies connection to Analysis Services server.")> _ Public Property Connection() As String Get Connection = m_connection End Get Set(ByVal value As String) If m_connection <> value Then m_connection = value Me.Database = String.Empty m_cachedDatabases = Nothing Me.Name = String.Empty m_cachedProps = Nothing End If End Set End Property ' Connection ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <List("GetDatabases")> _ <Category("Analysis Services")> _ <Description("Specifies Analysis Services database.")> _ Public Property Database() As String Get Database = m_database End Get Set(ByVal value As String) If m_database <> value Then m_database = value Me.Cube = String.Empty m_cachedCubes = Nothing Me.Name = String.Empty m_cachedProps = Nothing End If End Set End Property ' Database ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <List("GetCubes")> _ <Category("Analysis Services")> _ <Description("Specifies Analysis Services cube.")> _ Public Property Cube() As String Get Cube = m_cube End Get Set(ByVal value As String) If m_cube <> value Then m_cube = value Me.Dimension = String.Empty m_cachedDims = Nothing Me.Name = String.Empty m_cachedProps = Nothing End If End Set End Property ' Cube ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <List("GetDimensions")> _ <Category("Analysis Services")> _ <Description("Specifies Analysis Services dimension.")> _ Public Property Dimension() As String Get Dimension = m_dimension End Get Set(ByVal value As String) If m_cube <> value Then m_dimension = value Me.Name = String.Empty m_cachedProps = Nothing End If End Set End Property ' Dimension ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <List("GetObjectProperties")> _ <Category("Property")> _ <Description("Specifies object property.")> _ Public Property Name() As String Get Name = m_propName End Get Set(ByVal value As String) m_propName = value End Set End Property ' Name ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Variable()> _ <Category("Property")> _ <Description("Specifies package variable where you would like to store the property value.")> _ Public Property ResultVariable() As String Get ResultVariable = m_resultVariable End Get Set(ByVal value As String) m_resultVariable = value End Set End Property ' ResultVariable #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property AsConnectionType() As String() Get AsConnectionType = New String() {"MSOLAP90"} End Get End Property ' FtpConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get connected AS Server object. Private Function GetServer_() As Server Dim result As Server ' Get connection string. Dim manager As ConnectionManager = Dts.Connections(Me.Connection) Dim connString As String = manager.ConnectionString result = New Server() result.Connect(connString) GetServer_ = result End Function ' GetServer_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get list of selected connection databases. Public Function GetDatabases() As Object() Dim arrDatabases As ArrayList = New ArrayList() Dim dbCount As Integer Cursor.Current = Cursors.WaitCursor Try If String.IsNullOrEmpty(Me.Connection) Then ' User has to specify connection. Throw New ApplicationException("Please specify connection.") End If If m_cachedDatabases Is Nothing Then ' Cache databases. Using server As Server = GetServer_() dbCount = server.Databases.Count For dbIndex As Integer = 0 To dbCount - 1 Using db As Database = server.Databases(dbIndex) Call arrDatabases.Add(db.Name) End Using Next End Using m_cachedDatabases = arrDatabases.ToArray() End If Catch ex As Exception Call MessageBox.Show(ex.Message) Finally Cursor.Current = m_currentCursor End Try GetDatabases = m_cachedDatabases End Function ' GetDatabases ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get list of selected database cubes. Public Function GetCubes() As Object() Dim arrCubes As ArrayList = New ArrayList() Dim cubesCount As Integer Cursor.Current = Cursors.WaitCursor Try If String.IsNullOrEmpty(Me.Database) Then ' User has to specify database. Throw New ApplicationException("Please specify database.") End If If m_cachedCubes Is Nothing Then ' Cache cubes. Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) cubesCount = database.Cubes.Count For cubeIndex As Integer = 0 To cubesCount - 1 Using cube As Cube = database.Cubes(cubeIndex) Call arrCubes.Add(cube.Name) End Using Next End Using End Using m_cachedCubes = arrCubes.ToArray() End If Catch ex As Exception Call MessageBox.Show(ex.Message) Finally Cursor.Current = m_currentCursor End Try GetCubes = m_cachedCubes End Function ' GetCubes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get list of selected cube dimensions. Public Function GetDimensions() As Object() Dim arrDims As ArrayList = New ArrayList() Dim dimsCount As Integer Cursor.Current = Cursors.WaitCursor Try If String.IsNullOrEmpty(Me.Cube) Then ' User has to specify cube. Throw New ApplicationException("Please specify cube.") End If If m_cachedDims Is Nothing Then ' Cache dimensions. Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) Using cube As Cube = database.Cubes(Me.Cube) dimsCount = cube.Dimensions.Count For dimIndex As Integer = 0 To dimsCount - 1 Using dimension As CubeDimension = cube.Dimensions(dimIndex) Call arrDims.Add(dimension.Name) End Using Next End Using End Using End Using m_cachedDims = arrDims.ToArray() End If Catch ex As Exception Call MessageBox.Show(ex.Message) Finally Cursor.Current = m_currentCursor End Try GetDimensions = m_cachedDims End Function ' GetDimensions ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get list of selected object properties. Public Function GetObjectProperties() As Object() Cursor.Current = Cursors.WaitCursor Try GetObjectProperties = GetProps_() Catch ex As Exception Call MessageBox.Show(ex.Message) Finally Cursor.Current = m_currentCursor End Try End Function ' GetObjectProperties ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get list of selected object properties. Public Function GetProps_() As Object() Dim arrProps As ArrayList = New ArrayList() Dim props As PropertyInfo() If m_cachedProps Is Nothing Then ' Cache properties. ' Get object. Select Case Me.Type Case ObjectType.Server ' Get server. If String.IsNullOrEmpty(Me.Connection) Then Throw New ApplicationException("Please specify connection.") End If Using server As Server = GetServer_() props = server.GetType().GetProperties( _ BindingFlags.Instance Or BindingFlags.Public) End Using Case ObjectType.Database ' Get database. If String.IsNullOrEmpty(Me.Database) Then Throw New ApplicationException("Please specify database.") End If Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) props = database.GetType().GetProperties( _ BindingFlags.Instance Or BindingFlags.Public) End Using End Using Case ObjectType.Cube ' Get cube. If String.IsNullOrEmpty(Me.Cube) Then Throw New ApplicationException("Please specify cube.") End If Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) Using cube As Cube = database.Cubes(Me.Cube) props = cube.GetType().GetProperties( _ BindingFlags.Instance Or BindingFlags.Public) End Using End Using End Using Case ObjectType.Dimension ' Get dimension. If String.IsNullOrEmpty(Me.Dimension) Then Throw New ApplicationException("Please specify dimension.") End If Using server As Server = GetServer_() Using database As Database = server.Databases(Me.Database) Using cube As Cube = database.Cubes(Me.Cube) Using dimension As CubeDimension = cube.Dimensions(Me.Dimension) props = dimension.GetType().GetProperties( _ BindingFlags.Instance Or BindingFlags.Public) End Using End Using End Using End Using End Select ' Load properties. For Each prop As PropertyInfo In props Call arrProps.Add(prop.Name) Next Call arrProps.Sort() m_cachedProps = arrProps.ToArray() End If GetProps_ = m_cachedProps End Function ' GetProps_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Get current selected property value. Private Function GetPropertyValue_(ByVal obj As Object) As Object GetPropertyValue_ = obj.GetType().InvokeMember( _ Me.Name, _ BindingFlags.Instance Or BindingFlags.Public Or BindingFlags.GetProperty, _ Nothing, _ obj, _ New Object() {}) End Function ' GetPropertyValue_ #End Region ' Internals #Region "Attributes" Private m_object As ObjectType Private m_connection As String Private m_database As String Private m_cube As String Private m_dimension As String Private m_propName As String Private m_resultVariable As String Private m_cachedDatabases As Object() Private m_cachedCubes As Object() Private m_cachedDims As Object() Private m_cachedProps As Object() Private m_currentCursor As Cursor = Cursor.Current #End Region ' Attributes End Class ' ScriptMain