SSIS添加分区-动态
主要参考：动态分区
一、前提准备：
1、一个日期存储过程，注意代码可以得到一个月中的最后一天，最终生成时间维度。
USE [DrugDW]
GO
/****** Object: StoredProcedure [dbo].[PROC_DATETIME] Script Date: 2/28 星期二 14:16:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_DATETIME]
AS
BEGIN
/****** Object: StoredProcedure [dbo].[proc_Dim_date] Script Date: 05/20/2016 11:35:58 ******/
IF OBJECT_ID('dbo.Dim_Date') IS NOT NULL
DROP TABLE dbo.[Dim_Date]
CREATE TABLE [dbo].[Dim_Date](
[DateKey] [int] NULL,
[Date] [datetime] NULL,
[Year] [float] NULL,
[Month] [float] NULL,
[Month EN] [nvarchar](50) NULL,
[Month Short EN] [nvarchar](50) NULL,
[Month CN] [nvarchar](50) NULL,
[Day] [float] NULL,
[Quarter] [float] NULL,
[Quarter EN] [nvarchar](50) NULL,
[Quarter CN] [nvarchar](50) NULL,
[Weekday] [float] NULL,
[Weekday CN] [nvarchar](50) NULL,
[Weekday Short EN] [nvarchar](50) NULL,
[Week of Year] [float] NULL,
[Day of Year] [float] NULL,
[SemiYearly] [nvarchar](50) NULL,
[Period of Ten Days] [nvarchar](10) NULL,
[Period of Index] [nvarchar](2) NULL,
[Weekend] [nvarchar](5) NULL
) ON [PRIMARY]
SET DATEFIRST 7 --设周日为每周的第一天
--向日期表插入数据
DECLARE @b1 DATETIME
set @b1='2015-01-01' --设置起始日期
WHILE @b1< dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) --设置截止日期
BEGIN
INSERT INTO dbo.[Dim_Date] (
[DateKey],
[Date],
[Year],
[Month],
[Month EN],
[Month Short EN],
[Month CN],
[Day],
[Quarter],
[Quarter EN],
[Quarter CN],
[Weekday],
[Weekday CN],
[Weekday Short EN],
[Week of Year],
[Day of Year],
[SemiYearly],
[Period of Ten Days],
[Period of Index] ,
[Weekend]
)
VALUES(
CONVERT(NVARCHAR(10),@b1,112), --DateKey 1
@b1, --Date 2
DATEPART(year, @b1), --Year 3
DATEPART(month, @b1), --Month 4
CASE --Month EN 5
when (DATEPART(month, @b1))='1' then 'January'
when (DATEPART(month, @b1))='2' then 'February'
when (DATEPART(month, @b1))='3' then 'March'
when (DATEPART(month, @b1))='4' then 'April'
when (DATEPART(month, @b1))='5' then 'May'
when (DATEPART(month, @b1))='6' then 'June'
when (DATEPART(month, @b1))='7' then 'July'
when (DATEPART(month, @b1))='8' then 'August'
when (DATEPART(month, @b1))='9' then 'September'
when (DATEPART(month, @b1))='10' then 'October'
when (DATEPART(month, @b1))='11' then 'November'
else 'December'
END,
CASE --Month Short En 6
when (DATEPART(month, @b1))='1' then 'Jan'
when (DATEPART(month, @b1))='2' then 'Feb'
when (DATEPART(month, @b1))='3' then 'Mar'
when (DATEPART(month, @b1))='4' then 'Apr'
when (DATEPART(month, @b1))='5' then 'May'
when (DATEPART(month, @b1))='6' then 'Jun'
when (DATEPART(month, @b1))='7' then 'Jul'
when (DATEPART(month, @b1))='8' then 'Aug'
when (DATEPART(month, @b1))='9' then 'Sep'
when (DATEPART(month, @b1))='10' then 'Oct'
when (DATEPART(month, @b1))='11' then 'Nov'
else 'Dec'
END,
CASE --Month CN 7
when (DATEPART(month, @b1))='1' then N'一月'
when (DATEPART(month, @b1))='2' then N'二月'
when (DATEPART(month, @b1))='3' then N'三月'
when (DATEPART(month, @b1))='4' then N'四月'
when (DATEPART(month, @b1))='5' then N'五月'
when (DATEPART(month, @b1))='6' then N'六月'
when (DATEPART(month, @b1))='7' then N'七月'
when (DATEPART(month, @b1))='8' then N'八月'
when (DATEPART(month, @b1))='9' then N'九月'
when (DATEPART(month, @b1))='10' then N'十月'
when (DATEPART(month, @b1))='11' then N'十一月'
else N'十二月'
END,
DATEPART(day, @b1),--day 8
DATEName (qq, @b1),--quarter 9
CASE --quarter en 10
when DATEName (qq, @b1)='1' then 'Q1'
when DATEName (qq, @b1)='2' then 'Q2'
when DATEName (qq, @b1)='3' then 'Q3'
else 'Q4'
END,
CASE --quarter cn 11
when DATEName (qq, @b1)='1' then N'一季度'
when DATEName (qq, @b1)='2' then N'二季度'
when DATEName (qq, @b1)='3' then N'三季度'
else N'四季度'
END,
DATEPART(dw, @b1),--Weekday 12
CASE --Weekday CN 13
when DATEPART(dw, @b1)=1 then N'星期日'
when DATEPART(dw, @b1)=2 then N'星期一'
when DATEPART(dw, @b1)=3 then N'星期二'
when DATEPART(dw, @b1)=4 then N'星期三'
when DATEPART(dw, @b1)=5 then N'星期四'
when DATEPART(dw, @b1)=6 then N'星期五'
else N'星期六'
END,
CASE --Weekday Short EN 14 --注意,周日是第一天.
when DATEPART(dw, @b1)='1' then 'Sun'
when DATEPART(dw, @b1)='2' then 'Mon'
when DATEPART(dw, @b1)='3' then 'Tue'
when DATEPART(dw, @b1)='4' then 'Wed'
when DATEPART(dw, @b1)='5' then 'Thu'
when DATEPART(dw, @b1)='6' then 'Fri'
else 'Sat'
END,
DATEName (wk, @b1),--week of year 15
DATEName (dy, @b1),--day of year 16
CASE --SemiYearly 17
when DATEPART(month, @b1)<=6 then N'上半年'
else N'下半年'
END,
CASE --Period of Ten Days 18
when DATEName (dd, @b1)<=10 then N'上旬'
when DATEName (dd, @b1)>20 then N'下旬'
else N'中旬'
END,
CASE --Period of Ten Days 19
when DATEName (dd, @b1)<=10 then N'1'
when DATEName (dd, @b1)>20 then N'3'
else N'2'
END,
CASE --Is it Weekend? 20
when DATEPART(dw, @b1)='1' then '周末'
when DATEPART(dw, @b1)='7' then '周末'
else '平时'
END
)
--日期加1天
set @b1=DATEADD(day, 1, @b1)
END
end
2、一个事实表：
3、构建好的Cube，并且无分区（有分区也无妨，这里只是排除干扰，容易理解）：
4、准备SSIS参数：
二、利用SSIS动态构建分区
最终效果
2.1 、执行SQL任务
SQLStatement代码：
SELECT 'DrugDW' AS DataSoureID,--数据源
'Drug DW' AS CubeName,--分区来自哪一个cube
'Drug DW' AS CubeID,
'Fact OP Fee Detail' AS MeasureGroup,--指定是一个度量值组
'Fact OP Fee Detail' AS MeasureGroupID,
'Fact OP Fee Detail ' + Cast(MonthInfo.YearMonth AS VARCHAR(6)) AS Partition,--分区名称=度量值组名称+年月
'SELECT * FROM [dbo].[Fact_OPFeeDetail] where_clause' AS SQL,--要进行分区的SQL
cast(MinDateKey as varchar(8)) as MinDateKey,--最小datekey
cast(MaxDateKey as varchar(8)) as MaxDateKey--最大datekey
FROM (
SELECT t1.YearMonth,
(SELECT Min(datekey) FROM dim_date t2 WHERE CONVERT(VARCHAR(6), t2.Date, 112) = t1.YearMonth) AS MinDateKey,
(SELECT Max(datekey) FROM dim_date t2 WHERE CONVERT(VARCHAR(6), t2.Date, 112) = t1.YearMonth) AS MaxDateKey
FROM
(SELECT DISTINCT CONVERT(VARCHAR(6), Date, 112) AS YearMonth FROM dim_date) AS t1
)MonthInfo
WHERE EXISTS(SELECT * FROM [dbo].[Fact_OPFeeDetail] WHERE [VisitDateID] BETWEEN MonthInfo.MinDateKey AND MonthInfo.MaxDateKey)
SQLStatement执行结果：
设置结果集：
2.2、Foreach 循环容器便利结果集
2.3、编写脚本任务，处理每次的遍历结果：
2.4、 点击“编辑脚本”： 引用：Analysis Management Obejcts包
脚本代码(在SQL SERVER 2008请使用语句：(String)Dts.Variables["Partition"].Value;进行强制，不要使用Dts.Variables["User::Partition"].Value.ToString(); )：
#region Help: Introduction to the script task /* The Script Task allows you to perform virtually any operation that can be accomplished in * a .Net application within the context of an Integration Services control flow. * * Expand the other regions which have "Help" prefixes for examples of specific ways to use * Integration Services features within this script task. */ #endregion #region Namespaces using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using Microsoft.AnalysisServices; #endregion namespace ST_4038a8110570463994b546d9f7d48b3d { [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region ScriptResults declaration /// <summary> /// This enum provides a convenient shorthand within the scope of this class for setting the /// result of the script. /// /// This code was generated automatically. /// </summary> enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion public void Main() { // TODO: Add your code here String sPartition = Dts.Variables["User::Partition"].Value.ToString(); String sCubeName = Dts.Variables["User::CubeName"].Value.ToString(); String sMeasureGroup = Dts.Variables["User::MeasureGroup"].Value.ToString(); String sServer = "localhost"; String sDataBaseID = Dts.Variables["User::DatabaseID"].Value.ToString(); String sCubeID = Dts.Variables["User::CubeID"].Value.ToString(); String sMeasureGroupID = Dts.Variables["User::MeasureGroupID"].Value.ToString(); String sDataSoureID = Dts.Variables["User::DataSoureID"].Value.ToString(); String sSQL = Dts.Variables["User::SQL"].Value.ToString(); String sMaxDateKey = Dts.Variables["User::MaxDateKey"].Value.ToString(); String sMinDateKey = Dts.Variables["User::MinDateKey"].Value.ToString(); String aSql = sSQL.Replace("where_clause", "where VisitDateID >=" + sMinDateKey + " and VisitDateID <=" + sMaxDateKey); ConnectionManager cm = Dts.Connections.Add("MSOLAP100"); cm.ConnectionString = "Provider=MSOLAP.4;Data Source=localhost;IntegratedSecurity=SSPI;Initial Catalog=" + sDataBaseID; Microsoft.AnalysisServices.Server aServer = new Server(); aServer.Connect(sServer); Microsoft.AnalysisServices.Database aDatabase = aServer.Databases.FindByName(sDataBaseID); CubeCollection cubeCollection = aDatabase.Cubes; foreach (Cube item in cubeCollection) { string name = item.Name; } Microsoft.AnalysisServices.Cube aCube = aDatabase.Cubes.FindByName(sCubeName); Microsoft.AnalysisServices.MeasureGroup aMeasureGroup; try { MeasureGroupCollection collection = aCube.MeasureGroups; aMeasureGroup = collection.FindByName(sMeasureGroup); } catch (Exception ex) { throw ex; } if (aMeasureGroup.Partitions.Contains(sPartition)) { Dts.Variables["User::IsNotePresent"].Value = false; Dts.Variables["User::Xmla_script"].Value = ""; Dts.TaskResult = (int)ScriptResults.Success; } else { Dts.Variables["User::IsNotePresent"].Value = true; Dts.Variables["User::Xmla_script"].Value = "<Create xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "<ParentObject>" + "<DatabaseID>" + sDataBaseID + "</DatabaseID>" + "<CubeID>" + sCubeID + "</CubeID>" + "<MeasureGroupID>" + sMeasureGroupID + "</MeasureGroupID>" + "</ParentObject>" + "<ObjectDefinition>" + "<Partition xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" " + " xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\" xmlns:ddl200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200\" xmlns:ddl200_200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200/200\">" + "<ID>" + sPartition + "</ID>" + "<Name>" + sPartition + "</Name>" + "<Source xsi:type=\"QueryBinding\">" + "<DataSourceID>" + sDataSoureID + "</DataSourceID>" + "<QueryDefinition>" + aSql + "</QueryDefinition>" + "</Source>" + "<StorageMode>Molap</StorageMode><ProcessingMode>Regular</ProcessingMode>" + "<ProactiveCaching><SilenceInterval>-PT1S</SilenceInterval><Latency>-PT1S</Latency><SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><ForceRebuildInterval>-PT1S</ForceRebuildInterval>" + "<Source xsi:type=\"ProactiveCachingInheritedBinding\" /></ProactiveCaching>" + "</Partition>" + "</ObjectDefinition>" + "</Create>"; Dts.TaskResult = (int)ScriptResults.Success; } } } }
处理脚本返回值：如果@IsNotePresent是ture:就会将参数Xmla_script中的字符串传给下面的"Analysis Services 执行DDL任务"由它为度量值组生成分区；如果是false就返回null，不生成分区;
2.5、添加Analysis Services 执行 DDL 任务，处理返回值（即为度量值组生成分区）：
2.6、执行SSIS任务，查看分区生成情况： 
