0

I'm having an issue whereby I'm attempting to build up an XMLA command within an SSIS 2008 variable so that I can create/process Analysis Services 2008 partitions dynamically. The issue I'm facing is the XMLA command contains double quotes and I have tried escaping with \ but even though the SSIS expression box shows me the command correctly formatted, the ssis string variable shows the backslash.

e.g SSIS expression I enter is:

"<Create xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">"

SSIS expression is then evaluated correctly and shown as:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

but in the actual ssis variable (evaluated as an expression) the value is:

<Create xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">

Note the backslashes are visible.

Can anyone help?

Thanks

Dave Roberts
  • 1
  • 1

2 Answers2

0

We are currently doing this successfully by using quotes to escape the quotes. Here is an example from our working script task:

 "   xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">  "
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0
what worked in my case is :

1) wrap the XMLA in a select as below (taking  AdventureWorks for example and using variables as well):

"select
'<Create xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">
    <ParentObject>
        <DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID>
        <CubeID>Adventure Works</CubeID>
        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    </ParentObject>
    <ObjectDefinition>
        <Partition xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\" xmlns:ddl200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200\" xmlns:ddl200_200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200/200\">
            <ID>Internet_Sales_"+@[User::year_recorded]+"</ID>
            <Name>Internet_Sales_"+@[User::year_recorded]+"</Name>
            <Source xsi:type=\"QueryBinding\">
                <DataSourceID>Adventure Works DW</DataSourceID>
                <QueryDefinition>SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]
        FROM [dbo].[FactInternetSales]
                                WHERE "+@[User::WHERE_clause]+"</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <AggregationStorage>MolapOnly</AggregationStorage>
                <Source xsi:type=\"ProactiveCachingInheritedBinding\">
                    <NotificationTechnique>Server</NotificationTechnique>
                </Source>
            </ProactiveCaching>
            <EstimatedRows>1013</EstimatedRows>
            <AggregationDesignID>Internet Sales 1</AggregationDesignID>
        </Partition>
    </ObjectDefinition>
</Create>

'

as xmla"

and assign the above as string to a String variable .

2) drag in an execute SQl task and supply variable in 1) as 'SourceVariable' . Run this SQL task on a connection manager  for normal transactional DB , say 'master' for instance .
3) save the 'Single Row' resultset from 2) in another String variable.
4)supply the variable from 3) to another Execute SQL task using the connection manager for SSAS cube .

hope this helps anyone landing on same ...
Ankita Jhar
  • 1