[SOLVED] How to Partition a Table by Month ("Both" YEAR & MONTH) and create monthly partitions automatically?

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