sql server - Use Results of an XMLA query in Integration Services -

I have an XMLA query which gives an analysis of services the state of the cube and the final processing date as XML, such as:

Question:

  & lt; Discover XMLNs = "Color: Schema-Microsoft-Com: XML-Analysis" & gt; & Lt; RequestType & gt; DISCOVER_XML_METADATA & lt; / RequestType & gt; & Lt; Ban & gt; & Lt; Restriction list xmlns = "karash: schema-microsoft-com: xml-analysis" & gt; & Lt; DatabaseID & gt; SSAS - Premium and Claims V2 & lt; / DatabaseID & gt; & Lt; CubeID & gt; PDW04 1 & lt; / CubeID & gt; & Lt; ObjectExpansion & gt; ReferenceOnly & lt; / ObjectExpansion & gt; & Lt; / RestrictionList & gt; & Lt; / Ban & gt; & Lt; Properties / & gt; & Lt; / Discover & gt;  

Result:

  & lt; Back to xmlns = "urn: schemas-microsoft-com: xml-analysis" & gt; & Lt; Root xmlns = "Stigma: schemas-microsoft-com: XML-analysis: Rauseset" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns: xsd = "http: // www. W3.org/2001/XMLSchema"> & Lt; xsd: schema Lkshyanomspes = "urn: schemas-microsoft-com: XML-analysis: Raveset" Aksmelans: sql = "urn: schemas-microsoft-com: XML-SQL" Ttwform default = "qualified" & gt; & Lt; Xsd: element name = "original" & gt; & Lt; XSD: complexType & gt; & Lt; Xsd: sequence min ockers = "0" maxOccurs = "unbounded" & gt; & Lt; Xsd: element name = "line" type = "row" /> & Lt; / XSD: Sequence & gt; & Lt; / XSD: complexType & gt; & Lt; / XSD: element & gt; & Lt; Xsd: Simple Type Name = "uuid" & gt; & Lt; Xsd: ban base = "xsd: string" & gt; & Lt; xsd: pattern value = "[0-9-a-zA-Z] {8} - [0- 9-a-zA-Z] {4} - [0- 9-a-zA-Z] {4} - [0- 9A-GAAA-Z] {4} - [0- 9A-GAAA-Z] {12} "/> & Lt; / XSD: Ban & gt; & Lt; / XSD: simpleType & gt; & Lt; Xsd: complex type name = "xml document" & gt; & Lt; XSD: Sequence & gt; & Lt; Xsd: Any & gt; & Lt; / XSD: Sequence & gt; & Lt; / XSD: complexType & gt; & Lt; Xsd: complex type name = "row" & gt; & Lt; XSD: Sequence & gt; & Lt; Xsd: element sql: field = "METADATA" name = "METADATA" type = "xmlDocument" /> & Lt; / XSD: Sequence & gt; & Lt; / XSD: complexType & gt; & Lt; / XSD: Schema & gt; & Lt; Line & gt; & Lt; xars: METADATA xmlns = "http://schemas.microsoft.com/analysisservices/2003/engine" xmlns: DDL 2 = "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: Raisset" & gt; & Lt; Cube & gt; & Lt; Name & gt; Premium & lt; / Name & gt; & Lt; ID & gt; PDW04 1 & lt; / Id & gt; & Lt; CreatedTimestamp & gt; 2008-11-23T22: 31: 06 & lt; / CreatedTimestamp & gt; & Lt; LastSchemaUpdate & gt; 2009-01-22T00: 50: 13 & lt; / LastSchemaUpdate & gt; & Lt; LastProcessed & gt; 2009-01-07T22: 28:34 & lt; / LastProcessed & gt; & Lt; State & gt; Processed & lt; / State & gt; & Lt; / Cube & gt; & Lt; / Xars: metadata & gt; & Lt; / Row & gt; & Lt; / Root & gt; & Lt; / Return & gt;  

I would like to be able to parse this XMLA query in a Unity Services package and store results in a SQL Server database table. However, there is only one task that executes XMLA queries "Analysis Services Execute DDL" is the job, which does not have any method to return the results of the query as far as I can tell.

Anyone have any other suggestions on how to achieve this?

When you can run "regular" MDX / XMLA through various means, the mechanism in SSIS, in which the SQL function Is executed, it always wraps it inside it. There is a top-level method, as can not be wrapped inside and inside, so the problem

You get two possible solutions:

1. An Exclute SQL to your XML function to use an OPENQUERY call inside your relational database. You can do something like SELECT * from OPENQUERY () and you can actually do a good job of parse XML resultset inside SQL Server.
2. You can open the SSAS server by configuring HTTP access so that you send XMLA as a web service call. There is a web service function in SSIS that you can use to perform and consume the result.

Both of these options clearly have some negative impact.