SSIS处理CUbe-动态
场景：由SSIS动态生成的分区，在使用SSIS动态执行这些分区!
一、我们知道执行某个分区的时候，其实是由脚本去调用和配置的：
脚本的内容：
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ErrorConfiguration 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<KeyErrorLimit>-1</KeyErrorLimit>
<KeyNotFound>IgnoreError</KeyNotFound>
</ErrorConfiguration>
<Parallel>
<Process 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<Object>
<DatabaseID>Drug_SSAS</DatabaseID>
<CubeID>Drug DW</CubeID>
<MeasureGroupID>Fact OP Fee Detail</MeasureGroupID>
<PartitionID>Fact OP Fee Detail 201702</PartitionID>
</Object>
<Type>ProcessData</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
我们只需要将脚本内的值，动态的赋值，即可按照我们的配置进行执行分区或者度量值组。
二、使用SSIS执行指定的分区（当前时间的前2个月的分区）：
最终效果
参数准备：
2.1 、执行SQL任务：主要是将执行cube所需的参数构造并传递出去：
SELECT 'Drug_SSAS' AS DatabaseID,
'Drug DW' AS CubeID,
'Fact OP Fee Detail ' AS MeasureGroupID,
'Fact OP Fee Detail ' + CONVERT(VARCHAR(6), Dateadd(month, -1, Getdate()), 112) AS PartitionID
UNION
SELECT 'Drug_SSAS' AS DatabaseID,
'Drug DW' AS CubeID,
'Fact OP Fee Detail ' AS MeasureGroupID,
'Fact OP Fee Detail ' + CONVERT(VARCHAR(6), Getdate(), 112) AS PartitionID
语句执行结果：
2.2 、Foreach 循环容器遍历SQL任务的结果集：
2.3 、执行脚本任务，处理Foreach 循环容器的每条结果：
点击“编辑脚本”：
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
namespace ST_679fffc011334d1cb4eae175a9221ba1
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// TODO: Add your code here
String sDatabaseID = Dts.Variables["User::DatabaseID"].Value.ToString();
String sCubeID = Dts.Variables["User::CubeID"].Value.ToString();
String sMeasureGroupID = Dts.Variables["User::MeasureGroupID"].Value.ToString().Trim();
String sPartitionID = Dts.Variables["User::PartitionID"].Value.ToString().Trim();
Dts.Variables["User::Xmla_Script"].Value =
"<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">"
+ "<ErrorConfiguration 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\" xmlns:ddl300=\"http://schemas.microsoft.com/analysisservices/2011/engine/300\" xmlns:ddl300_300=\"http://schemas.microsoft.com/analysisservices/2011/engine/300/300\">"
+ "<KeyErrorLimit>-1</KeyErrorLimit>"
+ "<KeyNotFound>IgnoreError</KeyNotFound>"
+ "<NullKeyNotAllowed>IgnoreError</NullKeyNotAllowed>"
+ "</ErrorConfiguration>"
+ "<Parallel>"
+ "<Process 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\" xmlns:ddl300=\"http://schemas.microsoft.com/analysisservices/2011/engine/300\" xmlns:ddl300_300=\"http://schemas.microsoft.com/analysisservices/2011/engine/300/300\">"
+ "<Object>"
+ "<DatabaseID>" + sDatabaseID + "</DatabaseID>"
+ "<CubeID>"+sCubeID+"</CubeID>"
+ "<MeasureGroupID>" + sMeasureGroupID + "</MeasureGroupID>"
+ "<PartitionID>" + sPartitionID + "</PartitionID>"
+ "</Object>"
+ "<Type>ProcessFull</Type>"
+ "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>"
+ "</Process>"
+ "</Parallel>"
+ "</Batch>";
Dts.TaskResult = (int)ScriptResults.Success;
}
#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
}
}
2.4 、我们知道脚本中是有参数传出来的，使用Analysis Services 执行 DDL 任务接受参数，执行cube处理：
2.5 、执行SSIS任务：
