• There are a few ways this can be achieved, depending on your data. I'll describe a solution I did for a client recently and hopefully it'll help you out....

    The way the client had their partitions set up was to have partitions by business month determined by the load date. In the package I had a number of variables which held xmla tasks to update, create and process partitions.

    I created a table in an "Admin" database to hold the partition names, start and end dates and the date of last process.

    The first thing the package does is gather data from this table to get the most recent partition names, start dates and the last processed date which are all passed to variables. Then an execute SQL task determines if the day that the package is running is the start of a new business month. If it is, then it uses the (dynamic) xmla to close off the previous partition and create a new one. The new partition are then logged in the admin table and are then processed. The dynamic XMLA variables are all run through an Analysis Services Execute DDL Task.

    Since one of the fact tables is processed incrementally without partitioning the last processed date is used to do this.

    Once it's finished the processing the last thing it does is update the last processed date in the admin table.

    I hope that helps, as I said there are a number of ways this can be done and it depends on the needs of the client.

    I would attach the package but it's not a permitted file type


    I'm on LinkedIn