Use Results of an XMLA query in Integration Services

Question

I have an XMLA query which returns the State and Last Processed date of an Analysis Services cube as XML, like so:

Query:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_XML_METADATA</RequestType>
  <Restrictions >
    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
      <DatabaseID>SSAS - Premium and Claims V2</DatabaseID>
      <CubeID>PDW04 1</CubeID>
      <ObjectExpansion>ReferenceOnly</ObjectExpansion>
    </RestrictionList>
  </Restrictions>
  <Properties />
</Discover>

Result:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    <xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
      <xsd:element name="root">
        <xsd:complexType>
          <xsd:sequence minOccurs="0" maxOccurs="unbounded">
            <xsd:element name="row" type="row" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:simpleType name="uuid">
        <xsd:restriction base="xsd:string">
          <xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
        </xsd:restriction>
      </xsd:simpleType>
      <xsd:complexType name="xmlDocument">
        <xsd:sequence>
          <xsd:any />
        </xsd:sequence>
      </xsd:complexType>
      <xsd:complexType name="row">
        <xsd:sequence>
          <xsd:element sql:field="METADATA" name="METADATA" type="xmlDocument" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>
    <row>
      <xars:METADATA xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:xars="urn:schemas-microsoft-com:xml-analysis:rowset">
        <Cube>
          <Name>Premium</Name>
          <ID>PDW04 1</ID>
          <CreatedTimestamp>2008-11-23T22:31:06</CreatedTimestamp>
          <LastSchemaUpdate>2009-01-22T00:50:13</LastSchemaUpdate>
          <LastProcessed>2009-01-07T22:28:34</LastProcessed>
          <State>Processed</State>
        </Cube>
      </xars:METADATA>
    </row>
  </root>
</return>

I would like to be able to use this XMLA query in an Integration Services package, parsing and storing the result in a SQL Server database table. However the only tasks that seems to execute an XMLA query is the "Analysis Services Execute DDL" task, which does not have a method of returning results of the query as far as I can tell.

Does anyone have any suggestions on how else to achieve this?

Answer 1

While you can run "regular" MDX/XMLA through various mechanisms in SSIS, including via the Execute SQL task, it will always wrap it inside . is a top-level method just like and cannot be wrapped inside , hence the problem.

You've got two possible solutions:

1. Wrap your XMLA inside an Execute SQL task using an OPENQUERY call inside your relational database. You'd do something like SELECT * from OPENQUERY() and you can actually do a good job of parsing the XML resultset inside SQL Server.
2. Open up you SSAS servers by configuring HTTP access (http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx) so you can send XMLA as a web service call. There is a web service task in SSIS that you can use to execute your and consume the results.

Both of these options obviously have some downside.

Answer 2

For Discover commands:

This works very well for me:

Create a script task using XMLA names space issue the discover query. Once you receive the response parse the xmla and add to VB.NET Datatable and insert the parsed data into sql table.

Answer 3

Please Nagendra help me

You can show me an example of your sript task, I need it urgent

Answer 4

You can also use the new DMV syntax in SSAS 2008 or the DMV function from ASSP to in SSAS 2005 to return this data in a tabular format.