SSIS has limited operations for object data type and an object data type variable cannot be assigned to task expressions.Д¬reate a SSIS package as shown below in screen capture 1 Take note to return the XMLA as output parameter instead of a record set (SELECT If returned as record set, SQL Server implicitly converts the XMLA string into XML, which SSIS interprets as object data type instead of string. Where DatabaseID CubeID = MeasureGroupID = PartitionID = CreatedDateTime is null ,AggregationDesignID as AggregationDesignID ,'ProactiveCachingInheritedBinding' as as 'ProactiveCaching/Source/NotificationTechnique' ,'MolapOnly' as 'ProactiveCaching/AggregationStorage' ,'-PT1S' as 'ProactiveCaching/SilenceOverrideInterval' ,'-PT1S' as 'ProactiveCaching/SilenceInterval' , PartitionQuery as 'Source/QueryDefinition' , PartitionSourceType as DataSourceID as 'Source/DataSourceID' Select as DatabaseID, as CubeID, as MeasureGroupID

TakeВ note that SQL operators >,>=,Д¬reate procedure as as as as as varchar(max) output The nodes, elements and attributes of the create partition XMLA command will form the metadata of the data-driven model as shown below.

Internet Sales 1 Server MolapOnly -PT1S -PT1S WHERE OrderDateKey > = '20070101' and OrderDateKey < = '20071231' Adventure Works DW Fact Internet Sales 1 AdventureWorksDW

The XMLA will be similar to the one shown below This is done by scripting out the XMLA from an existing partition.

MetadataДЇirst step is to identify the required data elements. The rest of this post explains the steps to create partitions using metadata and SSIS package. Partitions are created using XMLA commands.

Usually cubes are developed with few basic partitions to begin with and new partitions are added over time. Partitions improve the query response of SSAS cube by narrowing the MDX query to a pre-defined subset of data.

Download sample ssis package