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任务：