{"id":13,"date":"2007-04-18T22:07:51","date_gmt":"2007-04-19T03:07:51","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=13"},"modified":"2009-07-05T20:10:12","modified_gmt":"2009-07-06T01:10:12","slug":"ssis-package-to-dropcreate-partitions-based-on-partition-list-in-the-sql-server-table","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/13_ssis-package-to-dropcreate-partitions-based-on-partition-list-in-the-sql-server-table","title":{"rendered":"SSIS Package to drop\/create partitions based on partition list in the SQL Server table"},"content":{"rendered":"

In my past blogs I showed how to use SSIS package to process dimensions, cubes or build aggregates. I am slowly rebuilding my existing packages thanks to points from Darren Gosbell<\/a> and Jess Orosz<\/a>. I found that using CaptureXML method is more convenient for me and it is definitely faster.
\n
\nJust to explain again about this method. I can change Analysis Services objects (create, drop, process) in the script using AMO commands. Normally each AMO command is executed in a separate batch. But AMO has an option to save XMLA script from the multiple AMO commands and then submit them in one batch. This is done my assigning Analysis Services server property CaptureXML value ‘True’ (to start capturing XMLA) or ‘False’ (to stop capturing XMLA). Then XMLA log can be executed using ExecuteCaptureLog statement where first parameter specifies if statements have to be run in batch.<\/p>\n

Past weekend I had some time to rewrite my partition synchronization package to use CaptureXML method. I maintain a list of partitions in SQL Server database table iwSSASPartitions. I use a SSIS script to drop partitions if they do not exists in my partition table and create new partitions if they do not exists in SSAS database. I have a SQL Server table with the list of partitions that should exist in SSAS database. Here is the script to create this table:<\/p>\n

CREATE TABLE [dbo].[iwSSASPartitions](
\n\u00a0[Partition] [varchar](100) NOT NULL,
\n\u00a0[CubeName] [varchar](100) NOT NULL,
\n\u00a0[MeasureGroup] [varchar](100) NOT NULL,
\n\u00a0[SQL] [varchar](4000) NOT NULL,
\n\u00a0[NeedsReProcessing] [bit] NOT NULL,
\n\u00a0CONSTRAINT [PK_iwSSASPartitions] PRIMARY KEY CLUSTERED ( [Partition], [CubeName], [MeasureGroup])
\n)<\/p><\/blockquote>\n

Normally I would deploy SSAS database (no processing) with just one partition per measure group. Then I would run my SSIS package to synchronize partitions between SQL Server table and SSAS database. For initial deployment I would drop about 35 partitions and then create about 250 new partitions. During normal monthly load I would need to create about 30 partitions.<\/p>\n

My old package was runnign XMLA scripts to create\/drop partitions. I was creating\/dropping partitions one by one. With old method my initial drop of 35 partition was taking about 5 min. Step to create new 250 partitions was taking about 15 min. As this is not done everyday, 20min to me seemed like a reasonable time. But after I re-wrote my package to take advantage of batch executions, step that drops partitions run for 15 seconds and step that create partitions run for 30 seconds. At first I actually thought that my code is not executing! So this is power of executing XMLA in the batch! <\/p>\n

Steps to create SSIS package that drops\/create partitions based on partition list in the SQL Server table:<\/p>\n