主要參考：動(dòng)態(tài)分區(qū)

一、前提準(zhǔn)備：

1、一個(gè)日期存儲(chǔ)過(guò)程，注意代碼可以得到一個(gè)月中的最后一天，最終生成時(shí)間維度。

USE [DrugDW] GO /****** Object: StoredProcedure [dbo].[PROC_DATETIME] Script Date: 2/28 星期二 14:16:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER 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 NULLDROP 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 --設(shè)周日為每周的第一天--向日期表插入數(shù)據(jù) DECLARE @b1 DATETIME set @b1='2015-01-01' --設(shè)置起始日期 WHILE @b1< dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) --設(shè)置截止日期 BEGININSERT 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 2DATEPART(year, @b1), --Year 3DATEPART(month, @b1), --Month 4CASE --Month EN 5when (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 6when (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 7when (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 8DATEName (qq, @b1),--quarter 9CASE --quarter en 10when 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 11when 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 12CASE --Weekday CN 13when 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 15DATEName (dy, @b1),--day of year 16CASE --SemiYearly 17when DATEPART(month, @b1)<=6 then N'上半年'else N'下半年'END,CASE --Period of Ten Days 18when DATEName (dd, @b1)<=10 then N'上旬' when DATEName (dd, @b1)>20 then N'下旬'else N'中旬'END,CASE --Period of Ten Days 19when DATEName (dd, @b1)<=10 then N'1' when DATEName (dd, @b1)>20 then N'3'else N'2'END,CASE --Is it Weekend? 20when DATEPART(dw, @b1)='1' then '周末'when DATEPART(dw, @b1)='7' then '周末'else '平時(shí)'END ) --日期加1天set @b1=DATEADD(day, 1, @b1) END end View Code

2、一個(gè)事實(shí)表：

?3、構(gòu)建好的Cube，并且無(wú)分區(qū)（有分區(qū)也無(wú)妨，這里只是排除干擾，容易理解）：

4、準(zhǔn)備SSIS參數(shù)：

??二、利用SSIS動(dòng)態(tài)構(gòu)建分區(qū)

?最終效果

? ?2.1 、執(zhí)行SQL任務(wù)? ? ? ??

?

SQLStatement代碼：

SELECT 'DrugDW' AS DataSoureID,--數(shù)據(jù)源'Drug DW' AS CubeName,--分區(qū)來(lái)自哪一個(gè)cube'Drug DW' AS CubeID,'Fact OP Fee Detail' AS MeasureGroup,--指定是一個(gè)度量值組'Fact OP Fee Detail' AS MeasureGroupID,'Fact OP Fee Detail ' + Cast(MonthInfo.YearMonth AS VARCHAR(6)) AS Partition,--分區(qū)名稱(chēng)=度量值組名稱(chēng)+年月'SELECT * FROM [dbo].[Fact_OPFeeDetail] where_clause' AS SQL,--要進(jìn)行分區(qū)的SQLcast(MinDateKey as varchar(8)) as MinDateKey,--最小datekeycast(MaxDateKey as varchar(8)) as MaxDateKey--最大datekeyFROM (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 MaxDateKeyFROM (SELECT DISTINCT CONVERT(VARCHAR(6), Date, 112) AS YearMonth FROM dim_date) AS t1)MonthInfoWHERE EXISTS(SELECT * FROM [dbo].[Fact_OPFeeDetail] WHERE [VisitDateID] BETWEEN MonthInfo.MinDateKey AND MonthInfo.MaxDateKey) View Code

SQLStatement執(zhí)行結(jié)果：

設(shè)置結(jié)果集：

2.2、Foreach 循環(huán)容器便利結(jié)果集? ??

?

?2.3、編寫(xiě)腳本任務(wù)，處理每次的遍歷結(jié)果：? ? ? ??

2.4、 點(diǎn)擊“編輯腳本”：? 引用：Analysis Management Obejcts包 ?

?

?腳本代碼(在SQL SERVER 2008請(qǐng)使用語(yǔ)句：(String)Dts.Variables["Partition"].Value;進(jìn)行強(qiáng)制，不要使用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; #endregionnamespace 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};#endregionpublic void Main(){// TODO: Add your code hereString 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 &gt;=" + sMinDateKey + " and VisitDateID &lt;=" + 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;}}} } View Code

?處理腳本返回值：如果@IsNotePresent是ture:就會(huì)將參數(shù)Xmla_script中的字符串傳給下面的"Analysis Services 執(zhí)行DDL任務(wù)"由它為度量值組生成分區(qū)；如果是false就返回null，不生成分區(qū);

2.5、添加Analysis Services 執(zhí)行 DDL 任務(wù)，處理返回值（即為度量值組生成分區(qū)）：?

2.6、執(zhí)行SSIS任務(wù)，查看分區(qū)生成情況：? ??

?

?

轉(zhuǎn)載于:https://www.cnblogs.com/java-oracle/p/6478853.html

總結(jié)

以上是生活随笔為你收集整理的SSIS添加分区-动态的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。