Database Partitions Task
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
Use the Operation page of the Database Partitions Task Editor dialog to configure the partition action you would like to perform.
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
Although the partitioning support in SQL 2005 is powerful, many find it difficult to use since Microsoft has not provided an easy to use point-and-click interface. The COZYROC Database Partitions task makes the the process of implementing partitions much simpler to both set up and maintain.
The task is using the connection object provided by the connection manager (AcquireConnection). It doesn't instantiate a new connection object based on a connection string. We went thru all this hassle to make sure we DO support transactions properly.
No. You have to define your partitioning function and schema and create table based on it. The task will assist you, by manipulating needed partitioning ranges and switching in/out partitions and data.
We will explain it, by describing a typical scenario in a data warehouse implementation. We are talking in particular about SSIS workflow, which process data every day. In such case you usually have your table partitioned by date. Every time your run the SSIS process, it creates a new partition for the new date. By the end of the week you will have 7 partitions. By the end of the month you will have 30 partitions. For a whole year you will run into more than 360 partitions. With such an arrangement you will easily end up with hundreds of partitions to manage and we are not even talking about hitting the internal partitions number limit in SQL 2005. This is where this task action comes handy. It will let you control the granularity of the partitions, say having a partition per week of data or partition per month of data. This will also make your task easy rolling-off and backing up your old data.
Unfortunately, the current incarnation of SSIS 2005 has incomplete connections support. ODBC is one of these connections. The workaround doesn't work because the connection returned by the manager is not of type OdbcConnection. We are working on possible solutions to be delivered in a future version.
- 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.
Ready to give it a try?
COZYROC SSIS+ Component Suite is free for testing in your development environment.