SSIS Package to drop and create partitions based on data in SQL Server table
User Rating: / 4
Written by Vidas Matelis   
Friday, 04 July 2008 03:01

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!


Comments (1)
1 Tuesday, 19 July 2011 10:11
Tryng to implement this code but iam confused what data should be there in iwssaspartitions table i mean ,[SQL]
,[NeedsReProcessing] data in the above column


Pyramid Analytics