SSIS is an ETL (extract, transform, load). This is not what you want to do.
You just need to create DDL statements dynamically .
I work with quarter below but it works as well with 1, 2 or X months if you want.
If you want to partition the table, you first need to create the file, filegroups and partionned table and set the partitionning manually
Creation of N+1 partitions for 2015 Q1 (before, Q1 and after Q2) on a table with an int identity PK and a datetime2 partitioned column.
Update it to add months, make it monthly or whatever you need...
First create N files groups:
Alter Database [Test] Add Filegroup [Part_Before2015]
Go
Alter Database Test Add Filegroup [Part_201501]
Go
Alter Database Test Add Filegroup [Part_201504]
Go
Add a file for each filegroup:
Alter Database [Test] Add FILE ( NAME = N'Part_Before2015', FILENAME = N'...\Part_Before2015.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_Before2015]
Alter Database [Test] Add FILE ( NAME = N'Part_201501', FILENAME = N'...\Part_201501.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201501]
Alter Database [Test] Add FILE ( NAME = N'Part_201504', FILENAME = N'...\Part_201504.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_201504]
Create a partition function on a datetime2 type (or date or even datetime):
Create Partition Function RangePartFunction (datetime2)
as Range Right For Values ('20150101', '20150401')
Create a partition scheme using the partition function on each filegroup (N+1):
Create Partition Scheme RangePartScheme as Partition RangePartFunction
To ([Part_Before2015], [Part_201501], [Part_201504])
Create the partitioned table on its partition scheme:
Create TABLE [PartitionTable] (id int identity(0, 1) not null, date datetime2 not null, text char(8000))
On RangePartScheme (date) ;
Add a Clustered index on the partitioned column and partition scheme:
Create Clustered Index IDX_Part On dbo.PartitionTable(date)
On RangePartScheme (date);
Add a PK to the id column:
Alter Table dbo.PartitionTable Add COntraint PK_Part Primary Key Nonclustered(id, date);
Build the query used to add extra file groups after the right boundary and split the last partition
- Review partition scheme extension and partition function split
- Review DMV used
Review all of this and how to use it to create dynamic SQL
Declare @currentDate datetime2
Declare @endDate datetime2 = '20160701' -- new end date
Declare @dateAdd int = 3 -- Add 3 month = 1 Quarter
-- Get Current boundaries
Select @currentDate = DATEADD(MONTH, @dateAdd,Cast(MAX(value) as datetime2)) From sys.partition_range_values as r
Inner Join sys.partition_functions as f on r.function_id = f.function_id
Where f.name = 'RangePartFunction'
-- Get all quarters between max and end date
; with d(id, date, name) as (
Select 0, @currentDate, Convert(char(6), @currentDate, 112)
Union All
Select id+1, DATEADD(MONTH, @dateAdd, date), Convert(char(6), DATEADD(MONTH, @dateAdd, date), 112)
From d Where d.date <= @endDate
)
Select * From (
Select id = id*10, query = 'If Not Exists(Select 1 From sys.filegroups Where name = ''Part_'+name+''')
Begin
Print ''Create Filegroup [Part_'+name+']''
Alter Database [Test] Add Filegroup [Part_'+name+']
End
GO'
From d
Union All
Select id*10+1, 'If Not Exists(Select 1 From sys.sysfiles Where name = ''Part_'+name+''')
Begin
Print ''Create File [Part_'+name+'.ndf]''
Alter Database [Test] Add FILE ( NAME = N''Part_'+name+''', FILENAME = N''C:\DB\MSSQL11.MSSQLSERVER\MSSQL\DATA\Part_'+name+'.ndf'' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [Part_'+name+']
End
GO'
From d
Union All
Select id*10+2, 'Print ''Add Range [Part_'+name+']''
Alter Partition Scheme RangePartScheme Next Used [Part_'+name+']
Go'
From d
Union All
Select id*10+3, 'Print ''Split Function ['+Convert(char(8), date, 112)+']''
Alter Partition Function RangePartFunction() Split Range ('''+Convert(char(8), date, 112)+''');
Go'
From d
) as q order by id
the output of this query is a list of SQL queries that must be run in order.
Execute the dynamic SQL
- It can be executed manually (copy and past in SSMS)
- It can be executed in a while loop or with a cursor which will executed each row of the ouput table one by one (use sp_executesql)
Automation
- Create a SQL Server Job which excute SQL queries: run the query used to create the dynamic SQL, save its output to a table variable and then execute each statement with a loop/cursor
If you want to run it monthly and make sure the next 12 months are always created, use this Set @endDate = DATEADD(MONTH, 12, getdate())
Finally
It will output 4*N rows for the N missing quarters between the last boundary of the function and @endDate:
- Create Filegroup
- Create File on Filegroup
- Extend the range of the partition Scheme
- Split the range of the partition function
You can run it row by row with a cursor or a while loop or you can just copy and paste it in SMSS.
- It can be automated with a job as well ie.
@endDate = DATEADD(MONTH, 3, getdate() will create the next 3 months
- Change @dateAdd to 1 if you want monthly partitions
- Add you own columns or checks
Link
Create job = https://www.mssqltips.com/sqlservertip/3052/simple-way-to-create-a-sql-server-job-using-tsql/
sp_executesql = https://technet.microsoft.com/en-us/library/ms188001%28v=sql.110%29.aspx
While loop = https://dba.stackexchange.com/questions/57933/can-exec-work-with-while-loop-of-cursor
