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)
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])
CREATE TABLE [TBL_NAME] (
) 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)
DELETE [TBL_NAME] WHERE COD_MES = 201001
Alter table [tempPartition] add constraint new_month_date
check (COD_MES <= 201001 and COD_MES >= 201001 and COD_MES is not null)
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?
And the answer is... there is.
After searching on the net for a solution I found this blog
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.