SSIS Package to drop and create partitions based on data in SQL Server table

This script is described in my blog post "SSIS Package to drop/create partitions based on partition list in the SQL Server table".

This package expects that in SQL Server database you maintain table that lists all partitions for all measure groups in one SSAS database. Table structure is:

CREATE TABLE [dbo].[iwSSASPartitions](
 [Partition] [varchar](100) NOT NULL,
 [CubeName] [varchar](100) NOT NULL,
 [MeasureGroup] [varchar](100) NOT NULL,
 [SQL] [varchar](4000) NOT NULL,
 [NeedsReProcessing] [bit] NOT NULL,
 CONSTRAINT [PK_iwSSASPartitions] PRIMARY KEY CLUSTERED ( [Partition], [CubeName], [MeasureGroup])
)

Based on data in this table, when you run this package, it drops partitions that are not in the list and create new partitions that does not exists in SSAS database. This is very easy way to create new partitions based on data in your SQL database.

You can download this SSIS package here.

Important note: Use at your own risk!

If you will run this package with empty iwSSASPartitions table, all partitions in SSAS database will be dropped!