SSIS是一个ETL（提取，转换，加载）。这不是您想要做的。您只需要动态创建DDL语句。
我在以下季度工作，但如果需要，它也可以在1、2或X个月使用。
如果要对表进行分区，则首先需要创建文件，文件组和分区表并手动设置分区
在具有int标识PK和datetime2分区列的表上为2015 Q1（在Q1之前和Q2之后）创建N + 1分区。更新它以增加月份，使其每月或任何您需要的…
首先创建N个文件组：
Alter Database [Test] Add Filegroup [Part_Before2015]
Go Alter Database Test Add Filegroup [Part_201501] Go Alter Database Test Add Filegroup [Part_201504] Go
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]
在datetime2类型（或日期或什至日期时间）上创建分区函数：
Create Partition Function RangePartFunction (datetime2)
as Range Right For Values (‘20150101’, ‘20150401’)
Create Partition Scheme RangePartScheme as Partition RangePartFunction
To ([Part_Before2015], [Part_201501], [Part_201504])
根据其分区方案创建分区表：
Create TABLE [PartitionTable] (id int identity(0, 1) not null, date datetime2 not null, text char(8000))
On RangePartScheme (date) ;
在分区列和分区方案上添加聚簇索引：
Create Clustered Index IDX_Part On dbo.PartitionTable(date)
On RangePartScheme (date);
将PK添加到id列：
Alter Table dbo.PartitionTable Add COntraint PK_Part Primary Key Nonclustered(id, date);
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 = id10, 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 id10+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 id10+2, ‘Print ‘’Add Range [Part_’+name+’]’‘ Alter Partition Scheme RangePartScheme Next Used [Part_’+name+’] Go’ From d Union All Select id10+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
执行动态sql
自动化
如果要每月运行一次并确保始终创建接下来的12个月，请使用此命令 Set @endDate = DATEADD(MONTH, 12, getdate())
最后
它将为函数的最后一个边界和@endDate之间的N个丢失的四分之一输出4 * N行：
它也可以通过工作自动化，即。@endDate = DATEADD(MONTH, 3, getdate()将在接下来的3个月内创建
关联
创建作业= https://www.mssqltips.com/sqlservertip/3052/simple-way-to-create-a-sql- server-job-using-tsql/
sp_executesql = https://technet.microsoft.com/zh- cn/library/ms188001%28v=sql.110%29.aspx
While循环= https://dba.stackexchange.com/questions/57933/can-exec-work-with- while-loop-of-cursor
602392714
清零编程群