Database Partitions Task

Overview

Database Partitions is SSIS control flow task for creating and maintaing SQL Server 2005 partitions. Microsoft SQL Server 2005 introduced table partitioning feature for handling large amounts of data. The feature is supported through proprietary SQL syntax extension. While the extension is functionally complete, many database administrators find that it requires too much manual intervention. In order to aid common repetitive table partitioning tasks, we designed a component with four operations:

  • Create partition
  • Insert partition
  • Remove partition
  • Combine partitions

Each operation has user interface to improve usability and minimize the learning. The task configuration consists of General and Partition Operation pages.

Note: The task supports Microsoft SQL Server 2005 and up.

Demonstration

Setup

Use the Operation page of the Database Partitions Task Editor dialog to configure the partition operation you would like to perform.

Options

Action

Specify task action. This property has the options listed in the following table.

Value Description
Create Partition This operation creates a new staging partition table with same structure as the target table, without the constraints.
This operation is usually executed before you start loading your data and it is needed because SQL server doesn't provide command with this functionality.

Selecting this value displays the dynamic options TargetTable, PartitionTable, CreatePrimaryKeys (1.5 SR-2), Filegroup (1.5 SR-1).
Insert Partition This operation inserts staging partition table into target partitioned table. The partition table should contain slice of data only for selected partition key. The operation performs following sub-steps:

  • Find where selected partition table fits in the target table, modifying the partition function if needed.
  • Combine overlapping data in the partition table and target table.
  • Switch-out and remove old partition data.
  • Set constraints and indexes on selected partition table.
  • Switch-in the partition table. If you are familiar with the SQL syntax for partitioning, it will roughly translate to:
ALTER TABLE PartitionTable SWITCH TO TargetTable PARTITION PartitionKey

This operation is executed usually after you finish loading data, to make your staging area part of your target table.

Selecting this value displays the dynamic options TargetTable, PartitionTable, PartitionKey, PartitionKeyNext (1.5 SR-1).
Remove Partition This operation removes partition from selected target partitioned table. You have to select partition key of the partition you want removed from target table.
PartitionTable option is optional. If it is specified, the task will create a table with the removed partition data. You can use this option, when you need to roll-off and backup the data.

Selecting this value displays the dynamic options TargetTable, PartitionTable, PartitionKey.
Combine Partitions This operation is a maintenance operation used to combine multiple smaller sequential partitions into a bigger partition. This operation is important for your data warehouse because it keeps your partitions manageable and easier to backup at later time.
This operation is executed usually in a maintenance workflow at the end of a business period (week, month, quarter).

Selecting this value displays the dynamic options TargetTable, Granularity.
TargetTable

Select target partitioned table.

PartitionTable

Specify partition table name.

CreatePrimaryKeys (1.5 SR-2)

Indicates whether the primary keys are created during staging table creation. This property has the options listed in the following table.

Value Description
True The primary keys are created.
False The primary keys are not created.
Filegroup (1.5 SR-1)

Specify table file group. This property is optional and if not specified, [default] file group will be used.

PartitionKey

Specify partition key. Target table partition column type is the type of the key.

PartitionKeyNext (1.5 SR-1)

Specify next partition key. Target table partition column type is the type of the key. This property is mandatory if PartitionKey is border key. Otherwise it is optional.

Granularity

Specify a sequence of how many small partitions you want to be combined. If you partition your data by day and you have data arriving every day, by the end of the month you will have 30 partitions. To make you partitions contain weekly data, you have to specify 7.