Has anyone ever used Microsoft.AnalysisServices.Scripter class to programtically script SSAS objects? I am looking for a little bit of sample code to get me started. My goal here is to script some of the SSAS objects out into XML and store the XML in a table that can be queried to produce some meta data reports. I plan on doing this using AMO inside of a Script Task of a SSIS package.
So here are my two main questions:
Can anyone provide me with some sample code (I am not that good with .NET) using the Scripter class to script an SSAS object? Can anyone tell me a better way to build reports based on the meta data of my SSAS objects?
Thank you in advance!
Here is some sample code to use Scripter to script Alter command for your database into a file. You can later have SSIS to load content of the file into a table.
using Microsoft.AnalysisServices;
........
XmlTextWriter xmlwrite = new XmlTextWriter( "MyDatabase.XMLA", System.Text.Encoding.UTF8);
xmlwrite.Formatting = Formatting.Indented;
xmlwrite.Indentation = 2;
Scripter.WriteAlter(xmlwrite, myDataBase, true, true);
xmlwrite.Close();
|||
Edward,
Thanks for the code sample. This should do the trick. However, I have one more follow up question for you. How do I write the XML into a String variabe instead of to a file? Thanks again.
|||
Take a look at the functionality provided to you by XMLTextWriter.
http://msdn2.microsoft.com/en-us/system.xml.xmltextwriter.aspx
You shoudl be able to use different constructor for it.Search for sample of using XMLTextWriter.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
Edward,
Thanks again for the help. Here is the code that I came up with, it is in VB.NET and will work inside of a SSIS Script Task. It should help anyone trying to do the same thing. Thanks you for your help on this one.
' 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.IO
Imports System.Data
Imports System.Math
Imports System.Xml
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices
Class ScriptMain
Public Sub Main()
' Get Server and Database name from DTS connection object
Dim oDTSASConnection As ConnectionManager = Dts.Connections("AS Server")
Dim sASServer As String = CStr(oDTSASConnection.Properties("ServerName").GetValue(oDTSASConnection))
Dim oASServer As New Microsoft.AnalysisServices.Server
' Connect to the requested server
oASServer.Connect(sASServer)
' Loop trough all of the dbs on the server and write xmla to db
Dim oASDatabase As New Microsoft.AnalysisServices.Database
For Each oASDatabase In oASServer.Databases
Call ScriptASDatabase(oASServer, oASDatabase)
'' Loop trough all of the cubes in the db and write xmla to db
Dim oASCube As New Microsoft.AnalysisServices.Cube
For Each oASCube In oASDatabase.Cubes
Call ScriptASCube(oASServer, oASDatabase, oASCube)
Next
' Loop trough all of the dimensions in the db and write xmla to db
Dim oASDim As New Microsoft.AnalysisServices.Dimension
For Each oASDim In oASDatabase.Dimensions
Call ScriptASDim(oASServer, oASDatabase, oASDim)
Next
Next
' Clean Up
oASServer.Disconnect()
Dts.TaskResult = Dts.Results.Success
End Sub
Public Sub ScriptASDatabase(ByVal oASServer As Microsoft.AnalysisServices.Server, ByVal oASDatabase As Microsoft.AnalysisServices.Database)
' Create wrtiters and set formating for xml
Dim stringWriter As New System.IO.StringWriter()
Dim xmlWriter As New System.Xml.XmlTextWriter(stringWriter)
xmlWriter.Formatting = Formatting.Indented
xmlWriter.Indentation = 2
Dim sXMLA As String
' Script dbs on the server and write to db
Scripter.WriteCreate(xmlWriter, oASServer, oASDatabase, False, False)
sXMLA = stringWriter.ToString
Call DBUpsert(oASServer.Name.ToString, oASDatabase.Name.ToString, oASDatabase.Name.ToString, oASDatabase.ID.ToString, oASDatabase.ID.ToString, "Database", sXMLA.ToString)
' Clean Up
stringWriter.Close()
xmlWriter.Close()
End Sub
Public Sub ScriptASCube(ByVal oASServer As Microsoft.AnalysisServices.Server, ByVal oASDatabase As Microsoft.AnalysisServices.Database, ByVal oASCube As Microsoft.AnalysisServices.Cube)
'Create wrtiters and set formating for xml
Dim stringWriter As New System.IO.StringWriter()
Dim xmlWriter As New System.Xml.XmlTextWriter(stringWriter)
xmlWriter.Formatting = Formatting.Indented
xmlWriter.Indentation = 2
Dim sXMLA As String
' Script cubes in the db and write to db
Scripter.WriteCreate(xmlWriter, oASDatabase, oASCube, False, False)
sXMLA = stringWriter.ToString
Call DBUpsert(oASServer.Name.ToString, oASDatabase.Name.ToString, oASCube.Name.ToString, oASCube.ID.ToString, oASCube.Parent.ID.ToString, "Cube", sXMLA.ToString)
' Clean Up
stringWriter.Close()
xmlWriter.Close()
End Sub
Public Sub ScriptASDim(ByVal oASServer As Microsoft.AnalysisServices.Server, ByVal oASDatabase As Microsoft.AnalysisServices.Database, ByVal oASDim As Microsoft.AnalysisServices.Dimension)
'Create wrtiters and set formating for xml
Dim stringWriter As New System.IO.StringWriter()
Dim xmlWriter As New System.Xml.XmlTextWriter(stringWriter)
xmlWriter.Formatting = Formatting.Indented
xmlWriter.Indentation = 2
Dim sXMLA As String
' Script cubes in the db and write to db
Scripter.WriteCreate(xmlWriter, oASDatabase, oASDim, True, False)
sXMLA = stringWriter.ToString
Call DBUpsert(oASServer.Name.ToString, oASDatabase.Name.ToString, oASDim.Name.ToString, oASDim.ID.ToString, oASDim.Parent.ID.ToString, "Dimension", sXMLA.ToString)
' Clean Up
stringWriter.Close()
xmlWriter.Close()
End Sub
Public Sub DBUpsert(ByVal sASServer As String, ByVal sASDatabase As String, ByVal sASName As String, ByVal sID As String, ByVal sParentID As String, ByVal sObjectType As String, ByVal sXMLA As String)
' Create a connection to the db
Dim oDTSDBConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = Dts.Connections("Local.MetaData")
Dim sDBServer As String = CStr(oDTSDBConnection.Properties("ServerName").GetValue(oDTSDBConnection))
Dim sDBDatabase As String = CStr(oDTSDBConnection.Properties("InitialCatalog").GetValue(oDTSDBConnection))
Dim oBuilder As New SqlConnectionStringBuilder()
oBuilder.DataSource = sDBServer
oBuilder.InitialCatalog = sDBDatabase
oBuilder.ConnectTimeout = 1000
oBuilder.IntegratedSecurity = True
oBuilder.ApplicationName = "ImportSSASXml"
Dim oDBConnection As New SqlConnection(oBuilder.ConnectionString.ToString)
' Create Sql Command
Dim cmd As New SqlCommand("dbo.etl_ImportSSASXml", oDBConnection)
cmd.CommandTimeout = 60
cmd.Connection = oDBConnection
cmd.CommandType = CommandType.StoredProcedure
' Add parameters and their values
cmd.Parameters.Add(New SqlParameter("@.Server", SqlDbType.VarChar, 255)).Value = sASServer
cmd.Parameters.Add(New SqlParameter("@.Database", SqlDbType.VarChar, 255)).Value = sASDatabase
cmd.Parameters.Add(New SqlParameter("@.Name", SqlDbType.VarChar, 255)).Value = sASName
cmd.Parameters.Add(New SqlParameter("@.ID", SqlDbType.VarChar, 255)).Value = sID
cmd.Parameters.Add(New SqlParameter("@.ParentID", SqlDbType.VarChar, 255)).Value = sParentID
cmd.Parameters.Add(New SqlParameter("@.ObjectType", SqlDbType.VarChar, 255)).Value = sObjectType
cmd.Parameters.Add(New SqlParameter("@.XMLA", SqlDbType.VarChar)).Value = sXMLA
' Open the connection
oDBConnection.Open()
' Execute the command
cmd.ExecuteNonQuery()
' Clean Up
oDBConnection.Close()
End Sub
End Class
I will be posting a comprehensive solution to building meta data reports on/about ones' SSAS objects on my blog in the next few weeks. Check it out if you are interested!
