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.
Setup
Use the Operation page of the Database Partitions Task Editor dialog to configure the partition action you would like to perform.
Parameters
General
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. Select the target table on which you are performing an action.
Specify the partition table name.
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. Specify the table file group. This parameter is optional and if not specified, [default] file group will be used.
Specify the partition key. Target table partition column type is the type of the key.
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.
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.
Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Knowledge Base
- Can the Database Partitions task convert an existing table into a partitioned table?
- Does the Database Partitions task support transactions?
- Why doesn't the Database Partitions task support the ODBC connection manager?
- Where can I find the documentation for the Database Partitions Task?
- Can you describe what the 'Combine Partitions' action does?
What's New
- Fixed: Failed to process tables with non-clustered primary keys (Thank you, Amir).
- Fixed: Failed to process tables with Columnstore Index (Thank you, Nigel).
- Fixed: Failed to process tables with datetime2 column types (Thank you, Doug).
- New: A new 'Get Partition' action to retrieve the partition number of existing partition.
- Fixed: Failed to process tables with foreign keys, which reference multiple columns (Thank you, Sergio).
- New: A new parameter CreatePrimaryKeys to create primary keys when using 'Create Partition' action.
- New: Support for SQL 2008 table compression option.
- Fixed: Various fixes (Thank you Suhail).
- New: A new parameter Filegroup when using 'Create Partition' action.
- New: A new parameter PartitionKeyNext when using 'Insert Partition' action.
- Fixed: Task was broken.
- New: Introduced task.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.