Tuesday, March 09, 2010

SSIS 2005: Partition Table

This is my recent problem:

I'm trying to work with partitions tables in SSIS 2005 by using the SQL Task tool instead of a script. Sorry for the redundancy in this matter but I wanted to ask your opinion on this one, since you posted a great response.

My challenge is that I have to load monthly files to a table, so I would like it to be partitioned by month (COD_MES is my key), but I do not want to create blank partitions, only the ones I need, because my files may not have values for a specific month.

What I did was to create a sequence of SQL Tasks to do this:

1) default declaration, when creating the data base tables from a script file
(this is not a SQL Task)

IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'funcPart_NAME')
CREATE PARTITION FUNCTION [funcPart_NAME](int) AS RANGE LEFT FOR VALUES (200911)
GO

IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'schePart_NAME')
CREATE PARTITION SCHEME [schePart_NAME] AS PARTITION [funcPart_NAME]
ALL TO ([PRIMARY])
GO

CREATE TABLE [TBL_NAME] (
[COD_MES] INTEGER,
[DT_CARREGAMENTO] DATETIME
) ON [schePart_NAME]([COD_MES])


In the SSIS, using SQL Tasks and after the data has been loaded into tempPartition table:

2) add the partition to the partition function
ALTER PARTITION FUNCTION funcPart_NAME() split RANGE (201001)

3)
DELETE [TBL_NAME] WHERE COD_MES = 201001

4)
Alter table [tempPartition] add constraint new_month_date
check (COD_MES <= 201001 and COD_MES >= 201001 and COD_MES is not null)

5)
declare @num int
SELECT @num = $partition.funcPart_NAME(201001)
ALTER TABLE [tempPartition] switch TO [TBL_NAME] PARTITION @num


This works fine for my first partition them it stops with an error "...generates more partitions than there are file groups mentioned in the scheme..."

I understand the error, and I thought that, by changing the partition scheme creation (1) I could override this by adding the 'ALL TO' clause.

Bottom line is, is there a way of making the partitions to be created dynamically as needed?

------------------------------------

SOLUTION:

And the answer is... there is.

After searching on the net for a solution I found this blog

http://blogs.msdn.com/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

and made this change in point (2):

"
ALTER PARTITION SCHEME schePart_NAME NEXT USED [PRIMARY];

ALTER PARTITION FUNCTION funcPart_NAME() split RANGE (201001);
"

And that was it.

The NEXT command allows the expansion of the scheme that is related to the function.

hope it helps anyone that might have need for it.

Regards,

No comments: