Database Partitions Task

SQL Server 2005, 2008, 2008R2, 2012, 2014

SQL Server 2005, 2008, 2008R2, 2012, 2014

All Downloads ----- Join VIP ----- Questions?

Overview

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

  • Create Partition
  • Insert Partition
  • Remove Rartition
  • Combine Partitions
  • Get Partition

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

Note: The task supports Microsoft SQL Server 2005 and later versions.

Demonstration

Setup

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

Parameters

Action

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

Option Description
Create Partition This action creates a new staging partition table with the same structure as the target table, without the constraints.

This action is usually executed before you start loading your data and it is needed because SQL server doesn't provide a command with this functionality.

Selecting this action displays the dynamic parameters TargetTable, PartitionTable, CreatePrimaryKeys (1.5 SR-2), Filegroup (1.5 SR-1).

Insert Partition This action inserts the staging partition table into the target partition table. The partition table should contain a slice of the data for only the selected partition key. The action performs the following sub-steps:

  • Find where the 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 the 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 action is executed usually after you finish loading data, to make your staging area part of your target table.

Selecting this action displays the dynamic parameters TargetTable, PartitionTable, PartitionKey, PartitionKeyNext (1.5 SR-1).

Remove Partition This action removes the partition from the selected target partition table. The partition key of the partition you want removed from the target table must be selected.

The PartitionTable parameter is optional for this action. If it is specified, the task will create a table with the removed partition data. This action can be used when you need to roll-off and backup the data.

Selecting this action displays the dynamic parameters TargetTable, PartitionTable, PartitionKey.

Combine Partitions This action is a maintenance action used to combine multiple smaller sequential partitions into a bigger partition. This action is important for your data warehouse because it keeps your partitions manageable and easier to back up at a later time.

This action is executed usually in a maintenance workflow at the end of a business period (week, month, quarter).

Selecting this action displays the dynamic parameters TargetTable, Granularity.

Get Partition (1.6 SR-1)

This action returns the partition number of the specified partition key. If partition key doesn't exist, the result is empty string.

Selecting this action displays the dynamic parameters PartitionKey, Result.

TargetTable

Select the target table on which you are performing an action.

PartitionTable

Specify the partition table name.

CreatePrimaryKeys (1.5 SR-2)

Indicate whether or not the primary keys are created during the staging table creation. This parameter has the options listed in the following table.

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

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

PartitionKey

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

PartitionKeyNext (1.5 SR-1)

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

Granularity

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

Result (1.6 SR-1)

Select an existing user-defined variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services Variables, Add Variable