Execute SQL Task With XML Result Set With SSAS Database Script

Hi everybody,

For our customer we are trying to create dynamically local cube files. Because the requirements are complex and we need to generate a lot of cube files, we can't use the MDX script CREATE GLOBA CUBE to create the local cubes and we have to use SSIS to have it done automatically. These are the steps we are following:
1. In a SSIS package, through a Script task, we generate the ASSL script in order to create the database and we stores the script in a column of the XML datatype in SQL Server through a stored procedure "SSAS_TEST.InsertASSLScript". The following code is use in the script task:




Code Block
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
'Create writers and set formating for xml
Dim myScripter As Scripter
'Represents a writer to write information to a string
Dim myStringWriter As New System.IO.StringWriter()
Dim myStringWriterTrans As New System.IO.StringWriter()
'Represents a writer that provides a fast, non-cached, forward-only way of generating streams or files containing XML data that conforms to the W3C Extensible Markup Language (XML) 1.0 and the Namespaces in XML recommendations.
Dim myXmlTextWriter As System.Xml.XmlTextWriter
Dim myXmlTextWriterTrans As System.Xml.XmlTextWriter
Dim myXmlWriterSettings As XmlWriterSettings
Sub New()
myScripter = New Scripter
myXmlTextWriter = New System.Xml.XmlTextWriter(myStringWriter)
myXmlTextWriterTrans = New System.Xml.XmlTextWriter(myStringWriterTrans)
myXmlWriterSettings = New XmlWriterSettings
myXmlWriterSettings.OmitXmlDeclaration = True
myXmlWriterSettings.ConformanceLevel = ConformanceLevel.Auto
myXmlTextWriter.Formatting = Formatting.Indented
myXmlTextWriter.Indentation = 2
End Sub
Public Sub Main()
'Get Server name from DTS connection object and store in variable
Dim oDTSASConnection As ConnectionManager = Dts.Connections("SARP_Cubes")
Dim sASServer As String = CStr(oDTSASConnection.Properties("ServerName").GetValue(oDTSASConnection))
'MsgBox("Server " & sASServer & " has been connected")
Dim oASServer As New Microsoft.AnalysisServices.Server
'Connect to the requested server
oASServer.Connect(sASServer)
'Get Database name from DTS connection object and store in variable
Dim sASDBName As String = CStr(oDTSASConnection.Properties("InitialCatalog").GetValue(oDTSASConnection))
Dim oASDatabase As New Microsoft.AnalysisServices.Database
'MsgBox("InitialCatalog " & sASDBName & " has been found")
'Get database sASDBName and store in variable
oASDatabase = oASServer.Databases.GetByName(sASDBName)
'MsgBox("Database " & sASDBName & " has been connected")
'Get Cube
Dim CubName As String
If Dts.Variables.Contains("CubName") = True Then
CubName = CType(Dts.Variables("CubName").Value, String)
End If
Dim oASCube As New Microsoft.AnalysisServices.Cube
'MsgBox("Database " & sASDBName & " has been connected")
'Create a variable to store the create cube ASSL-script
'Dim sASSLCreateCub As String
'Store the create script in myXmlTextWriter
myScripter.ScriptCreate(New MajorObject() {oASDatabase}, myXmlTextWriter, False)
myXmlTextWriter.Flush()

'Create a string in order to manipulate the XML-string and append the Batch and process element
Dim sASSLString As String
sASSLString = "" & myStringWriter.ToString & "ProcessFull SARP_Cubes"

'Make a database conenction through connection manager
'Get Server name from DTS connection object and store in variable
Dim oDTSDBConnection As ConnectionManager = Dts.Connections("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 = "InsertASSLScript"
Dim oDBConnection As New SqlConnection(oBuilder.ConnectionString.ToString)
' Create Sql Command
Dim cmd As New SqlCommand("SSAS_TEST.InsertASSLScript", oDBConnection)
cmd.CommandTimeout = 60
cmd.Connection = oDBConnection
cmd.CommandType = CommandType.StoredProcedure
' Add parameters and their values
cmd.Parameters.Add(New SqlParameter("@COUNTRY_CODE", SqlDbType.VarChar, 255)).Value = "999"
cmd.Parameters.Add(New SqlParameter("@CUBE_XMLA", SqlDbType.VarChar)).Value = sASSLString
cmd.Parameters.Add(New SqlParameter("@DATABASE_ID", SqlDbType.VarChar, 255)).Value = sASDBName
cmd.Parameters.Add(New SqlParameter("@CUBE_ID", SqlDbType.VarChar, 255)).Value = "ALL CUBES"
' Open the connection
oDBConnection.Open()
' Execute the command
cmd.ExecuteNonQuery()
' Clean Up
myStringWriter.Close()
myStringWriterTrans.Close()
'myStringReader.Close()
'myXMLReader.Close()
myXmlTextWriter.Close()
'oDBConnection.Close()
oASServer.Disconnect()
Dts.TaskResult = Dts.Results.Success
End Sub

End Class





2. We manipulate the ASSL-script in order to create the cube that we want to have as local cube

3. We extract the final ASSL-script from the database through an "Execute SQL Task" with a XML Result Set. The SQL use in the task is:




Code Block
SELECT cast(CUBE_XMLA as varchar(max))
FROM SSAS_TEST.CUBE_XMLA
WHERE (COUNTRY_CODE = '500')




I have also tried




Code Block
SELECT CUBE_XMLA
FROM SSAS_TEST.CUBE_XMLA
WHERE (COUNTRY_CODE = '500')




and




Code Block
SELECT CUBE_XMLA
FROM SSAS_TEST.CUBE_XMLA
WHERE (COUNTRY_CODE = '500')
FOR XML AUTO




I always get the same error:
"Execute SQL Task: Executing the query "SELECT cast(CUBE_XMLA as varchar(max))

FROM SSAS_TEST.CUBE_XMLA

WHERE (COUNTRY_CODE = '500') " failed with the following error: "/ROOT/*[local-name()="Batch" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Create" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="ObjectDefinition" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Database" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Cubes" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Cube" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="MeasureGroups" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="MeasureGroup" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Source" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]

Type '{http://schemas.microsoft.com/analysisservices/2003/engine}MeasureGroupBinding' is not found in Schema."

Does anyone know how to handle this?

If we could use this XML variable, we will use another Script task to generate our Local Cube(s). The script task looks like this:




Code Block
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices.AdomdClient
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.
Public Sub Main()
'Declare variables
'Connection
Dim conn As AdomdConnection
'Command
Dim cmd As AdomdCommand
'Cellset
Dim cst As CellSet
Dim strFilename As String = "D:TestLocal.cub"
Dim strSource As String = Dts.Variables("ASSLCreateScript").Value.ToString()
'*-----------------------------------------------------------------------
'* Open connection.
'*-----------------------------------------------------------------------
Try
' Create a new AdomdConnection object, providing the connection
' string.
conn = New AdomdConnection("Data Source=" & strFilename)
' Open the connection.
conn.Open()
Catch ex As Exception
Throw New ApplicationException( _
"An error occurred while connecting.")
End Try
Try
'*-----------------------------------------------------------------------
'* Open cellset.
'*-----------------------------------------------------------------------
' Create a new AdomdCommand object, providing the ASSL query string.
cmd = New AdomdCommand(strSource, conn)
' Run the command and return a CellSet object.
cst = cmd.ExecuteCellSet()
'*-----------------------------------------------------------------------
'* Release resources.
'*-----------------------------------------------------------------------
conn.Close()
Catch ex As Exception
' Ignore or handle errors.
Finally
cst = Nothing
cmd = Nothing
conn = Nothing
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class





I hope that someone can help us. We browse the net without result,

Thanks in advance.