How to programmatically create "Script Database As"-script?

For various reasons, like being able to modify a single object within the database, we wanted an application that created one XMLA file per object. Thanks to your code above we were able to write the following little tool to extract the XMLA files (in VB.NET).

Edit: Changed the script dependencies setting from True to False, so that Cube scripts are generated correctly.

' Creates XMLA files from an Analysis Services database

' Author: Lars R?nnb?ck

' Naming convention: my for class members, a for temporary variables, the for parameters or arguments

Module AS2XMLA

Dim myScripter As Scripter

Dim myXmlWriterSettings As XmlWriterSettings

Dim myXmlWriter As XmlWriter

Dim myScriptInfo As ScriptInfo

Sub New()

myScripter = New Scripter

myXmlWriterSettings = New XmlWriterSettings

myXmlWriterSettings.OmitXmlDeclaration = True

myXmlWriterSettings.ConformanceLevel = ConformanceLevel.Auto

End Sub

Public Sub Main()

If My.Application.CommandLineArgs.Count < 2 Then

Console.WriteLine("Syntax: AS2XMLA <server name> <database name>")

Else

Dim aDatabase As Database

Try

Dim theAnalysisServicesServer As String = My.Application.CommandLineArgs.Item(0)

Dim theAnalysisServicesDatabase As String = My.Application.CommandLineArgs.Item(1)

Dim aServer As Server = New Server

aServer.Connect("Data Source=" + theAnalysisServicesServer + ";PROVIDER=MSOLAP.3;Impersonation Level=Impersonate;")

aDatabase = aServer.Databases.GetByName(theAnalysisServicesDatabase)

Debug("Creating directory: " + theAnalysisServicesDatabase)

Directory.CreateDirectory(theAnalysisServicesDatabase)

MajorObjectCollectionToXMLA(theAnalysisServicesDatabase + "\DataSources", aDatabase.DataSources)

MajorObjectCollectionToXMLA(theAnalysisServicesDatabase + "\DataSourceViews", aDatabase.DataSourceViews)

MajorObjectCollectionToXMLA(theAnalysisServicesDatabase + "\Dimensions", aDatabase.Dimensions)

MajorObjectCollectionToXMLA(theAnalysisServicesDatabase + "\Cubes", aDatabase.Cubes)

MajorObjectCollectionToXMLA(theAnalysisServicesDatabase + "\MiningStructures", aDatabase.MiningStructures)

MajorObjectCollectionToXMLA(theAnalysisServicesDatabase + "\Roles", aDatabase.Roles)

Console.WriteLine("All objects written to files")

Catch theException As Exception

Console.WriteLine(theException.Message)

If My.Application.CommandLineArgs.Count > 2 Then

Debug(theException.ToString)

End If

End Try

End If

End Sub

Private Sub MajorObjectCollectionToXMLA(ByRef theDirectory As String, ByRef theMajorObjectCollection As MajorObjectCollection)

Dim aMajorObject As MajorObject

If theMajorObjectCollection.Count > 0 Then

Debug("Creating directory: " + theDirectory)

Directory.CreateDirectory(theDirectory)

End If

For Each aMajorObject In theMajorObjectCollection

MajorObjectToXMLA(theDirectory, aMajorObject)

Next

End Sub

Private Sub MajorObjectToXMLA(ByRef theDirectory As String, ByRef theMajorObject As MajorObject)

myXmlWriter = XmlWriter.Create(theDirectory + "\" + theMajorObject.Name + ".xmla", myXmlWriterSettings)

Console.WriteLine("Writing file: " + theDirectory + "\" + theMajorObject.Name + ".xmla")

myScriptInfo = New ScriptInfo(theMajorObject, ScriptAction.CreateWithAllowOverwrite, ScriptOptions.Default, False)

myScripter.Script(New ScriptInfo() {myScriptInfo}, myXmlWriter)

myXmlWriter.Flush()

myXmlWriter.Close()

End Sub

Private Sub Debug(ByRef theMessage As String)

If My.Application.CommandLineArgs.Count > 2 Then

Console.WriteLine("[DEBUG] " + theMessage.ToString)

End If

End Sub

End Module

lasa at 2007-8-30 >

This sample code here is exactly for scripting with AMO (instead of WriteProcess method, use WriteCreate, WriteAlter or WriteDelete):

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607442&SiteID=1

Adrian Dumitrascu

AdrianDumitrascu at 2007-8-30 >