Click here to Skip to main content
14,326,161 members

"OSQL Utility" for SSAS 2005

Rate this:
3.00 (2 votes)
Please Sign up or sign in to vote.
3.00 (2 votes)
18 Jun 2008CPOL
A script that lets you run many XMLA files against a SSAS 2005 database.


One cool thing about Microsoft Analysis Services 2005 is that it lets you script many administrative tasks (such as role creation and cube processing). The script files are saved with an XMLA extension and are written in Analysis Services Scripting Language (ASSL) format.

The problem arises when you need to run a lot (100+) of these files against a SSAS database. Unfortunately, I could not find a utility to run these files programmatically. This article describes how to create a VBS script file to run many XMLA files against SSAS.


The trick is to use HTTP access to send the Execute XMLA request to SSAS. The execute request, along with letting you send MDX commands, lets you send ASSL commands.

Using the Code

Here is the code for the VBS script file:

Const ForAppending = 8
Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim sFolder: sFolder = fso.GetParentFolderName(WScript.ScriptFullName)
Dim oLogFile: Set oLogFile = _
    fso.CreateTextFile(WScript.ScriptFullName & ".log", True)
Dim sUrl
Dim sUserName
Dim sPassword


If WScript.Arguments.Count = 0 Then
 ProcessFolder sFolder & "\XMLA"
 For i = 0 to WScript.Arguments.Count -1
  ProcessFile WScript.Arguments(i)
End If

Log "Done!"


Sub ProcessFolder(sMyFolder)
 If Not fso.FolderExists(sMyFolder) Then
  Log "Folder does not exist: " & sMyFolder
  Exit Sub
 End If
 Dim oFolder, oFile
 Set oFolder = fso.GetFolder(sMyFolder)
    For Each oFile In oFolder.Files
        If LCase(Right(oFile.Name, 5)) = ".xmla" Then
   ProcessFile oFile.Path 
        End If
End Sub

Sub LoadConfigFile()
 Dim sConfigFile: sConfigFile = sFolder & "\XmlaConfig.xml"
 If Not fso.FileExists(sConfigFile) Then
  Log "Configuration file does not exist: " & sConfigFile
 End If
 Dim oDoc: Set oDoc = CreateObject("MSXML2.DOMDocument")
 If Not oDoc.Load(sConfigFile) Then
  Log "Configuration file could not be loaded: " & _
      sConfigFile & " " & oDoc.parseError.reason
 End If
 sUrl = oDoc.SelectSingleNode("settings/url").Text
 sUserName = oDoc.SelectSingleNode("settings/user").Text
 sPassword = oDoc.SelectSingleNode("settings/password").Text
End Sub

Sub ProcessFile(sFilePath)
 If Not fso.FileExists(sFilePath) Then
  Log ("File does not exist: " & sFilePath)
  Exit Sub
 End If
 Log "Executing: " & sFilePath
 Dim sConents: sConents = GetFileContents(sFilePath)
 Dim strQuery: strQuery = EncloseTag("Command", sConents)
 Dim strProps: strProps = EncloseTag("Properties", _
 Dim sNS: sNS = " xmlns:SOAP-ENV="""" 
     xmlns:xsi = """" 
 Dim sPayload: sPayload = "<?xml version='1.0'?>" & _
   "<SOAP-ENV:Envelope " & sNS & ">" & _
   "<SOAP-ENV:Body>" & _
   " <Execute xmlns=""urn:schemas-microsoft-com:xml-analysis"" >" & _
   strQuery + strProps & _
   " </Execute>" & _
   "</SOAP-ENV:Body>" & _
 Log PostData(sUrl, sPayload, sUserName, sPassword)
 Log ""
End Sub

Private Sub Log(sLine)
 oLogFile.WriteLine sLine
 'WScript.Echo sLine
End Sub

Private Function EncloseTag(sTag, sValue)
 EncloseTag = "<" & sTag & ">" & sValue & "</" & sTag & ">"
End Function

Private Function PostData(sUrl, sData, sUserName, sPassword)
 Dim oHttp
 'Set oHttp = CreateObject("Microsoft.XMLHTTP")
 Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
    oHttp.setTimeouts 0, 0, 0, 0
 oHttp.Open "POST", sUrl, False, sUserName, sPassword
 oHttp.setRequestHeader "SOAPAction", _
 oHttp.Send sData
 PostData = oHttp.responseText
 Set oHttp = Nothing
End Function

Public Function GetFileContents(sFilePath)
 Dim sContents
 Const ForReading = 1
 Const TristateMixed = -2
 Set oTextFile = fso.OpenTextFile(sFilePath, ForReading, False, TristateMixed)
 Do While Not oTextFile.AtEndOfStream
  sContents = sContents & oTextFile.ReadLine
 GetFileContents = sContents
End Function


The most difficult part is the configuration. Here are the steps:

  1. Setup HTTP access to SQL Server 2005 Analysis Services:
  2. Make sure that XMLA virtual directory is set to basic authentication only. Note that you can also use Anonymous access (with a user that has admin access to your SSAS server), but this option is less secure.
  3. Change the configuration file (xmlaConfig.xml) to point to your XMLA provider (like http://MyServer/xmla/msmdpump.dll). Set the Windows user name (domain\username) and password that has admin access to your SSAS server.
  4. Drop your XMLA files on top of the script file (xmla.vbs)
  5. Optionally, you can create a subfolder called XMLA in the same folder where the script file resides. The script file will look for the XMLA subfolder and run all XMLA files within it.

Points of Interest

Here is a code I wrote earlier to run multiple SQL files against a SQL Server database using the OSQL utility:


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Igor Krupitsky
Web Developer
United States United States
Igor is a business intelligence consultant working in Tampa, Florida. He has a BS in Finance from University of South Carolina and Masters in Information Management System from University of South Florida. He also has following professional certifications: MCSD, MCDBA, MCAD.

Comments and Discussions

QuestionWhat about ascmd Pin
Member 46495631-Mar-09 14:30
memberMember 46495631-Mar-09 14:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 18 Jun 2008

Tagged as


14 bookmarked