Load Cube Metadata Into SQL database

Howdy Folks !
Hope this will be Helpful

Way of Finding Cube Meta Data Using SSIS Script Task
Context: To Find the Metadata about the Analysis Services Cube Many Approaches can be taken.
This approach doesn’t store cube metadata in the Cube itself as Hidden Measure but in a Log Table which is more accessible.

 Step 1: Create a New table in SQL Database .

TABLENAME(LOG.CUBEMETADATA) Column Name LastProcessedDate LastSchemaUpdate RequiredFurtherParameter* Column Type String String As per Requirement

Step 2: Create a New Script Task in SSIS Package.

Create a new ADO.net Connection in Your Package before you do this  Don’t Forget to Add Required Assemblies in Script Task (Copy Paste the Code in Script Task ) ' 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.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.AnalysisServices Imports System.Data.SqlClient Imports System.Xml Public Class ScriptMain ' 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. Dim asServer As Server = New Server() Dim connection As SqlConnection Dim command As SqlCommand Dim adapter As SqlDataAdapter Dim Query As String Dim CubeLastProcessed As String Dim LastCubeSchemaUpdate As String Dim StrLastProcessed As String Dim StrLastSchemaUpdate As String Public Sub Main() 'For Logging all the Connection Managers in the Existing Package-Omit if You Dont Need it If Dts.Connections("(YourADO.net Connection Name)").ConnectionString Is Nothing Or Dts.Connections("(YourADO.net Connection Name)").ConnectionString = String.Empty Then MsgBox("No Connection In Package found") Else connection = CType(Dts.Connections("(YourADO.net Connection Name)").AcquireConnection(Dts.Transaction), SqlConnection) End If asServer.Connect(Dts.Connections("(Your Analysis Services Connection Name)").ConnectionString) 'If Your Database Has Many Cubes find the RElevent cube you Need For Each cube As Cube In asServer.Databases("(Your Analysis Services Database Name)") If (cube.Name = "(Your Cube Unique Name)") Then LastProcessed = cube.LastProcessed LastSchemaUpdate = cube.LastSchemaUpdate Else Me.LogMessage("No Update Found !") End If Next StrLastProcessed = (CType(CubeLastProcessed, String)) StrLastSchemaUpdate = (CType(LastCubeSchemaUpdate, String)) Query = "Insert into LOG.CUBEMETADATA(LastProcessedDate,LastSchemaUpdate) Values ('" & StrLastProcessed & "','" & StrLastSchemaUpdate & "')" command = New SqlCommand(Query, connection) command.ExecuteReader() Dts.TaskResult = Dts.Results.Success End Sub End Class

Step 3: Change the variable Names As per Requirement.
Step 4: Run and Voila Your Table has been Updated !