What I wanted to do is to create a process in SSIS that did not use code to create new SSAS Partitions using XMLA.


NOTE: This is quite a long blog post, but it has all the details from start to finish to get this completed.



·         We will be using the AdventureWorks2012DW data warehouse and Cube.

·         We will be creating new Partitions starting from 01 Jan 2010 on our Internet Sales Measure Group.

o    When creating our Partitions we will be using the query binding because our underlying SQL Server Table will be partitioned by DateKey.

§  NOTE: Our SQL Server Table would also have a Clustered Index created on the DateKey.

o    For our query in Analysis Services Partition we will be using the first day and the last day of the month in order to get our required data into our Partition.

o    Along with this when we create our Partition we will also use the Slice Function to further help Analysis Services query the data as quickly as possible.

o    So our Start Datekey will be 20100101

o    And our End DateKey will be 20100131

·         Our Partitioning naming scheme will be the Measure group name and year and month

o    So for our example it will be Internet Sales 2010-01


You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012


Creating our Partitioned Table and starting point.

Below are the steps where we will create our table and our starting point for our example above.


1.       Below is the script that we will use on our AdventureWorksDW2012 SQL Server database which will be explained below:

— =======================================================================

— 1. This will create our Table

— =======================================================================












— =======================================================================

— 2. Then we insert our starting point into our Partitioned Table

— =======================================================================


Select 2009 as[YearOfLastPartition]




      ,‘Internet Sales – 2009-12’[PartitionName]

      ,‘Adventure Works’as[SSAS_DatabaseName]


— =======================================================================

— 3. Then we do a select to ensure that the details inserted are correct

— =======================================================================



a.        As you can see above in Section 1 is where we are creating our table.

b.       In Section 2 is where we are inserting the data which is easier to explain:

                                                               i.      clip_image001

                                                              ii.      NOTE: We will be using the above columns to generate our requirements for when creating our next partition.

1.       Which you can view here in section later in the document.

                                                            iii.      As you can see above because we want our next partition to be for Jan 2010, we need to start our details one month earlier. EG; December 2009


Getting details from a stored procedure to be used in SSIS to create our next partition

In the steps below we are going to show the stored procedure and explain how we get our details for our next partition.

We will then use these details and in the next step put this all into SSIS to create our partition.


1.       Below is the entire script and each section is explained in the comments section

a.        NOTE: A quick overview is that based on the information in the Mart_TD_Max_SSAS_PartitionDetails, we are getting the next month’s values for the following:

                                                               i.      Our Start and End DateKeys, Year Month for the Partition Name, as well as the year and Month name which are used in the proc.

b.       clip_image002




/****** Object:  StoredProcedure [dbo].[prc_GetNextPartitionValues_ForSSAS_SQL]    Script Date: 2014-04-09 10:46:59 AM ******/







— ===============================================================================================================

— Disclaimer

— The sample scripts are provided AS IS without warranty of any kind.

— I further disclaims all implied warranties including, without limitation, any implied warranties of

— merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance

— of the sample scripts and documentation remains with you.

— In no event shall I, its authors, or anyone else involved in the creation, production, or delivery of

— the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business

— profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use

— of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the

— possibility of such damages.

— ===============================================================================================================


— ===============================================================================================================

— Author:       Gilbertq

— Create date: 09 Apr 2014

— Description:  This is where we get the data from the Current or Last Partition and then get into variables

—              the next partition values to create the SSAS Partitions and SQL Partitions if required in SSIS

— ===============================================================================================================








— ==================================================================================================================

— 1. We are getting the Year Value of the last partition that was created from Mart_TD_Max_SSAS_PartitionDetails

— ==================================================================================================================





          from[dbo].[Mart_TD_Max_SSAS_PartitionDetails]with (nolock)



— ====================================================================================================================

— 2. We are getting the Month Name Value of the last partition that was created from Mart_TD_Max_SSAS_PartitionDetails

— ====================================================================================================================





          from[dbo].[Mart_TD_Max_SSAS_PartitionDetails]with (nolock)



— ====================================================================================================================

— 3. Here we are getting the Actual Date from our previous values so that it can be used in the next steps.

—    Below we are always getting the last day of the month for our current values.

— ====================================================================================================================




          Selecttop 1 [FullDateAlternateKey]

          fromDimDatewith (nolock)






— ====================================================================================================================

— 4. Now here we are getting the next date value, which will be the first day of the Next Month

— ====================================================================================================================





          fromDimDatewith (nolock)




— ====================================================================================================================

— 5. Next we are getting the Year value for our Next Month’s value

— ====================================================================================================================





          fromDimDatewith (nolock)




— ====================================================================================================================

— 6. Next we are getting the Month Name value for our Next Month’s value

— ====================================================================================================================





          fromDimDatewith (nolock)




— ====================================================================================================================

— 7. Next we are getting the first day of Next Month’s value

— ====================================================================================================================




          Selecttop 1 DateKey

          fromDimDatewith (nolock)






— ====================================================================================================================

— 8. Next we are getting the last day of Next Month’s value

— ====================================================================================================================




          Selecttop 1 DateKey

          fromDimDatewith (nolock)






— ====================================================================================================================

— 9. Next we are getting the Year Month Value for Next Month’s Partition

— ====================================================================================================================




          Selecttop 1 convert(Varchar(4),CalendarYear)+‘-‘+RIGHT(REPLICATE(‘0’,5)+CONVERT(VARCHAR(2),MonthnumberofYear),2)

          fromDimDatewith (nolock)





— ====================================================================================================================

— 12. And finally we are getting our 3 values for Next month which we can then use in our SSIS package for multiple

—     loads

— ====================================================================================================================















2.       Run this against your SQL Server AdventureWorksDW2012 database.


SSIS to create new Partitions for SSAS

In the steps below we will use SSIS to create our new Partitions for our SSAS Cube.


1.       The first thing to do is to create the following variables which will be used for our entire SSIS Package:

a.        clip_image003

b.       NOTE: The reason that I set them all to the Data type of string is so that when they are used later in the expressions we do not have to cast the values.

                                                               i.      This is because all our expressions will either be passed to SQL server or Analysis Services which only accepts it in a string format.

                                                              ii.      We also put in defaults so that when we Evaluate the Expressions later we can see values.

2.       Next we are going to get all our variables for our stored procedure above and put them into the above variables.

a.        Drag in your Execute SQL task and as with our example we gave it the following name:

Get Variables for Next Months SSAS Partition for Internet Sales

b.       Next right click and select Properties.

c.        You will have to create your OLE DB connection to your SQL server where you have your AdventureWorksDW2012 database.

                                                               i.      clip_image004

d.       Then configure the General window with the following as shown below:

                                                               i.      clip_image005

e.       Then click on the Result Set on the left hand side and configure it with the following to map our variables from our Stored Procedure to our Variables in SSIS

                                                               i.      clip_image006

                                                              ii.      NOTE: All of the above will be used in different sections throughout the SSIS Package.

f.        Click Ok.

g.        Then right click and select Execute Task, to test that it runs correctly.

                                                               i.      You should see the following below:

                                                              ii.      clip_image007

3.       Now in the following steps I am going to explain how initially to manually create our Partition so that we can then use this for our SSIS in the next step.

a.        Log into your SSAS Server and go into the Measure Group and then Partition where you want to automate your partition creating.

b.       NOTE: As with our example we are going to create a new Partition on the Internet Sales Measure Group for December 2009

                                                               i.      clip_image008

c.        Right click on Partitions and select New Partition, this will open the Partition Wizard.

                                                               i.      We selected the Internet Sales Facts table as shown below and clicked Next

1.       clip_image009

                                                              ii.      On the Restrict Rows we selected Specify a query to restrict rows

1.       NOTE: We are doing this due to wanting to specify and our start and end DateKey

2.       clip_image010

3.       We then scrolled to the end of the Query and put in the following as per our example

a.        clip_image011

                                                            iii.      We accepted the defaults until we got to the final window. And put in the details below.

1.       clip_image012

                                                            iv.      We then clicked Finish.

d.       You will now see the partition we created

                                                               i.      clip_image013

4.       Next is where we are going to script out our Partition so that we can then use this within SSIS

a.        Right click on the partition we created above and select Script Partition as, Create To, New Query Editor Window.

                                                               i.      clip_image014

                                                              ii.      You should see the following below which is not the complete script

1.       clip_image016

b.       Next you will first have to do a find and replace on all the double quotation so that it can be escaped in SSIS.

                                                               i.      Press Control+H and complete the following below:

1.       clip_image017

                                                              ii.      Then click Replace All.

c.        If you have any singles quotes in your Query Definition you will also have to change them to have 2 single quotes so that when this is parsed by TSQL it will work.

                                                               i.      So as with our above example we also had to change the following from:

1.  + ‘Line ‘  +

2.       To: + ”Line ”  +

d.       Now open a TSQL Query and first put in the following:


                                                               i.      Then take your XMLA script from above and insert it in between the single quotes above.

                                                              ii.      Now run the TSQL Select to ensure that it will run successfully

1.       clip_image018

                                                            iii.      NOTE: The reason that we parse this in TSQL is so that in SSIS we can then put in our variables as required.

e.       Now take the entire TSQL statement from step 4c above and copy it.

f.         Now go back into SSIS and open your variables.

                                                               i.      Where it has the variable name of XMLAQuery_CreateSSASPartition click on the Ellipses under Expression

                                                              ii.      Then in the Expression Window put in a double quote at the start and insert your TSQL query from step 4d above:

1.       clip_image019

                                                            iii.      Then scroll right to the end and put in another double quote at the end

1.       clip_image020

                                                            iv.      Then click on Evaluate Expression to ensure that so far it is all correct.

g.        Now the final part in creating our script is to put in the required variables.

                                                               i.      The first section where we are going to add the variables is for the ID and name of our partition.

                                                              ii.      As with our example we put in the following:

1.       What it looked like before the change:

<ID>Fact Internet Sales 2009-12</ID>

<Name>Fact Internet Sales 2009-12</Name>

2.       And what it was with the variables inserted:

<ID>Fact Internet Sales “+ @[User::YearMonthForNextPartition]  +”</ID>

<Name>Fact Internet Sales “+ @[User::YearMonthForNextPartition]  +”</Name>

                                                            iii.      In the next section we are going to put in our Start and end DateKey’s for our Query Definition

1.       What it looked like before the change:

WHERE orderdatekey between 20091201 and 20091231</QueryDefinition>

2.       And what it looked like after the change:

WHERE orderdatekey between “+ @[User::StartDateKeyForNextPartition]  +” and “+ @[User::EndDateKeyForNextPartition]  +”</QueryDefinition>

                                                            iv.      Now what we are going to add is our Slice Property to our Partition.

1.       So after the section below is where you will put in the Slice Property


2.       As you can see we have put in the Slice Property with the variables already inserted:


<Slice>{[Date].[Date].&amp;[“+ @[User::StartDateKeyForNextPartition]  +”],[Date].[Date].&amp;[“+ @[User::EndDateKeyForNextPartition]  +”]}</Slice>

                                                              v.      Now click on Evaluate Expression to ensure that everything is correct.

1.       If you scroll down you should see the following

a.        clip_image021

b.       NOTE: This was due to us creating the values for our variables in Step 1 above.

5.       Next we are going to be taking our script which has been populated with the details above and put this into a variable which can then be passed to our Analysis Services Execute DDL Task.

a.        Drag in an Execute SQL Task.

b.       We then gave it the following name:

                                                               i.      Get XMLA for SSAS to create new Partition

c.        Next right click and go into the Properties and configure it with the following as shown below:

                                                               i.      clip_image022

                                                              ii.      NOTE: As you can see above we are using the variable which we populated with our XMLA script in step 4 above.

                                                            iii.      We are also setting the Result Set to a Single row. This is so that we can populate our XMLA Script which will then be passed to Analysis Services.

1.       Just to ensure the understanding is that this Execute SQL Task will populate our XMLAQuery_CreateSSASPartition variable with the details.

2.       After which we want the output to be inserted into our XMLA Script which will then be used in Analysis Services.

d.       Now click on Result Set and configure it with the following below:

                                                               i.       Click On Add and select the following:

                                                              ii.      clip_image023

e.       Then Click Ok.

6.       Next we will need to configure our Analysis Services Execute DDL Task with the following steps below.

a.        Drag in the Analysis Services Execute DDL Task

b.       Right click and select Properties to go into the Properties.

c.        Under Name we gave it the following name as per our Example:

                                                               i.      Run XMLA Script to create new SSAS Partition

d.       Then click on DDL.

                                                               i.      In the right hand side where it says Connection you are going to have to create a connection to your SSAS, and Database

                                                              ii.      Click on New Connection

                                                            iii.      As with our example we created our connection

1.       clip_image024

                                                            iv.      Then Click Ok Twice to get back.

                                                              v.      Then next to Source click on the Drop down and select the Variable that we populated in the previous steps.

                                                            vi.      As with our Example we are going to select the variable that we populated in step 5 above.

                                                           vii.      So once complete it will look like the following:

1.       clip_image025

                                                         viii.      Then Click Ok.

7.       Next we need to create our Update TSQL Statement for our Mart_TD_Max_SSAS_PartitionDetails table so that we can dynamically update this after the partition above has been created.

a.        Go into your variables and click on the ellipses button next to Query_Update_Mart_TD_Max_SSASPartitionDetails

b.       Then we put in the following for our Update Statement in the Expression Window:

“Update [dbo].[Mart_TD_Max_SSAS_PartitionDetails]

 Set         [YearOfLastPartition] = “+ @[User::YearForNextPartition]  +”

                                ,[MonthNameOfLastPartition] = ‘”+ @[User::MonthNameForNextPartition]   +”‘

                                ,[YearMonthOfLastPartition] = ‘”+ @[User::YearMonthForNextPartition]  +”‘

                                ,[DateTimeInsertedIntoTable] = getdate()

                                ,PartitionName = ‘Internet Sales “+ @[User::YearMonthForNextPartition]  +”‘

                                ,SSAS_DatabaseName = ‘Adventure Works'”

                                                               i.      Then click on Evaluate Expression to ensure that it is valid and you should see the following:

1.       clip_image026

c.        Then click Ok.

8.       The next part is to update our Mart_TD_Max_SSAS_PartitionDetails with our last Partition that was created in the steps above. This is so that we have a starting point for the next time this runs.

a.        Drag in an Execute SQL Task and give it the following name:

                                                               i.      Update Mart_TD_Max_SSAS_PartitionDetails with Last Partitions created

b.       Then configure it with the following:

                                                               i.      clip_image027

                                                              ii.      NOTE: The variable that we selected is called: Query_Update_Mart_TD_Max_SSASPartitionDetails

c.        Then click Ok

9.       Now the final part is where if the create partition script fails to then send an email so that the person responsible is made aware of the error.

a.        Drag in your Send Mail Task and drag it under your Run XMLA Script to create new SSAS Partition

b.       Drag the precedence constraint to your Send Mail Task and change it from success to failure.

                                                               i.      clip_image028

c.        Then configure your Send Mail Task as you require for the person responsible.

10.    So once complete it looks like the following:

a.        clip_image029

11.    Now that it is complete you can run the package.

a.        So when we looked at our Partitions before we ran the package it looked like the following:

                                                               i.      clip_image030

                                                              ii.      NOTE: The reason for our Fact Internet Sales 2009-12 was so that we could create our script.

b.       Now after running the package it completes as shown below:

                                                               i.      clip_image031

c.        Now if we look at our Partitions again we will see the following after refreshing it:

                                                               i.      clip_image032

d.       And then finally if we look at our Mart_TD_Max_SSAS_PartitionDetails it should have the details of our Partition we created above:

                                                               i.      clip_image033



0 thoughts on “SSIS – Creating new Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL”

  1. Hi Gilbert,

    your tutorial is very good and i have enjoyed the method you used but im having problem with creating xmla script in the variable Please can you send me the package.

    Many thanks,


    1. Hi there

      Thanks for the compliment.

      When you say you are having trouble creating the variable in the xmla script, it is when you are putting it in as part of the expression?

      Or when you are trying to create the variable?


  2. Hi Gilbert,

    yes it’s when I’m putting it in as part of the expression. using double quotes for + ” line” + section as you advised gives me an error, but when i use single + ‘ line ‘ + quote i don’t get any error,

    But when i then click on evaluate expression for variables Fact Internet Sales “+ @[User::YearMonthForNextPartition] +”
    etc, none of the variables return their respective values, so it just returns whats already been passed in Fact Internet Sales “+ @[User::YearMonthForNextPartition] +” instead of Fact Internet Sales 2009-12

    I’m really baffled as i have followed your instruction thoroughly. perhaps its a bug with my visual studio 2010

  3. Hi there Pascal, one thing to note is when you create a variable there is a column called “Value” and what happens is when you put something into the “Value” column, this means that when you click on Evaluate your expression, it will then use the “Value” to populate your variable. This is useful when you want to ensure that it is using the correct variable. As well as displaying what you expect.

    When the SSIS package runs at run time it will then dynamically pass through to your variable what you have defined.

    So I would suggest putting something into the “Value” column, then click on “Evaluate Expression”, if that works and you do not get an error, then try and run the entire SSIS package.

    Another thing that you can do is to put a breakpoint on the item where you want to check the variable at run time. (Let me know if you are unsure how to do this? Google can also help)


  4. Hi gilbert,
    First i’d like to say thank you for the amazing tutorial. That’s exactly what i was looking for to solve a problem in my job.

    I’m new to SSAS and trying to implement this example, but theres something going wrong here.
    I implemented with some variations and everything goes fine until I process the cube.

    I’m using SSIS to create the partition with the dll task and all the data is there, they are create.
    My problem is thate in the SSAS project the partitions created don’t appear, as they weren’t add in the project or They were not mapp there.

    Can you help me out?

    I’m using Visual Studio 2012 and SQL Server 2012. Is that a possibility that the different versions is causing this?

    Sorry my bad english, not my native language.

    1. Hi there, thanks for the comment.

      The first thing that I would like to ask is are you running Enterprise Edition of SQL Server? The reason that I ask is that if you are running Standard Edition or Business Intelligence Edition, then you will not have the option to partition your cube.

      If you can let me know and if you are still getting some issues, please let me know.


  5. Yes, i’m running in an enterprise Edition.
    We use cube partition in other projects, but this one specifically need to be partitioned dynamically.

    In my SSAS project i can create manually all the partitions that i need, but when i create a partition with SSIS by DLL task it’s not mapping the partition on SSAS project, besides all the data of partition was create.

    1. Hi there.

      Ok just so that I can understand your issue is that when the ddl task runs it is not creating the partition?

      Does the ddl task run successfully?

      If it does can you put in a breakpoint after the variable has been created in the step before the ddl task. Then view your variables by looking at the locals tab or watch1 tab at the bottom of the ssis project and see what is contained within the variable. You can take the variable output and run that in ssms under your ssas cube and see what happens when you execute that in ssms.

      If that does create the partition then there is an issue with the ddl task.

      If it fails when executing in ssms against the cube then possibly look to see why that is occurring and potentially update your xmla script. I am going to guess that there might be something missing or not correctly named. Such as the measure group name is different or the partition name is different.

  6. Hi Gilbert,

    it’s almost like you said, when the ddl task runs it is not creating the partition only in SSAS. The partition is created, just not add to project.

    Yes, the dll task run successfully.

    I tried to run with a breakpoint and confirm that the variables are correct.
    they end like this:

    Mdtr Cobertura Olap
    Mdtr Fato Cobertura Pdv

    Mdtr Fato Cobertura Pdv 201411
    Mdtr Fato Cobertura Pdv 201411

    Mdtr Olap





    So i think that is correct, the xmla is correctly created, and the dll task execute successfully, creating that partition, but not add on the project where the cube is, just creating the partitions on database.

  7. the xmla that i sent mixed with tags here… ill send again

    Create xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”
    DatabaseID ssas_mdtr DatabaseID
    CubeID Mdtr Cobertura Olap CubeID
    MeasureGroupID Mdtr Fato Cobertura Pdv MeasureGroupID
    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″ xmlns:ddl300=”http://schemas.microsoft.com/analysisservices/2011/engine/300″ xmlns:ddl300_300=”http://schemas.microsoft.com/analysisservices/2011/engine/300/300″ xmlns:ddl400=”http://schemas.microsoft.com/analysisservices/2012/engine/400″ xmlns:ddl400_400=”http://schemas.microsoft.com/analysisservices/2012/engine/400/400″
    ID Mdtr Fato Cobertura Pdv 201411 ID
    Name Mdtr Fato Cobertura Pdv 201411 Name
    Source xsi:type=”QueryBinding”
    DataSourceID Mdtr Olap DataSourceID
    QueryDefinition SELECT [dbo].[mdtr_fato_cobertura_pdv].[id_cobertura]
    FROM [dbo].[mdtr_fato_cobertura_pdv]
    INNER JOIN [dbo].[mdtr_dim_periodo_cobertura] p
    ON p.id_periodo = [dbo].[mdtr_fato_cobertura_pdv].id_periodo
    WHERE p.periodo_ano_mes = ‘201411’ QueryDefinition
    StorageMode Molap StorageMode
    ProcessingMode Regular ProcessingMode
    SilenceInterval -PT1S SilenceInterval
    Latency -PT1S Latency
    SilenceOverrideInterval -PT1S SilenceOverrideInterval
    ForceRebuildInterval -PT1S ForceRebuildInterval
    Source xsi:type=”ProactiveCachingInheritedBinding” /
    EstimatedRows 76242381 EstimatedRows


  8. Hi Gilbert,

    i figured out that all that process was creating a partition directly in my analysis services base, but nothing was changed in the archive.partitions that the SSAS project use. That’s why even that the partition exist it’s not appearing on SSAS.

    Any sugestion about how to make include this information on .partitions archive using SSIS?

    1. On Fri, Jan 9, 2015 at 4:46 AM, Gilbert Quevauvilliers – BI blog wrote:

      ​Hi there Andre, yes that is correct the SSAS Project would not be updated automatically. This is how SSAS projects are designed. If you wanted to get your SSAS Project aligned with what you have in your cube on your SSAS Server, you would have to create a new SSAS Project and when you are creating the project, one of the options is to “Import Analysis Services Database” You could do it this way, so that for the time being your SSAS project reflects your SSAS Cube.

      Thanks Gilbert

Leave a Reply

Your email address will not be published. Required fields are marked *