SSIS Packages in Analysis Services

SQL Server Integration Services (SSIS) can be used to deploy and manage Analysis Services objects such as data sources, data source views, cubes, dimensions, and partitions. This article provides an overview of how to work with SSIS packages that target Analysis Services, including deployment strategies, common tasks, and best practices.

Why Use SSIS for Analysis Services?

SSIS offers a graphical, drag‑and‑drop environment that simplifies complex ETL workflows. When combined with Analysis Services, SSIS can automate:

Key SSIS Tasks for Analysis Services

TaskDescriptionTypical Use‑Case
Analysis Services Processing TaskProcesses objects like cubes, dimensions, partitions.Refresh analytic data after loading a staging table.
Analysis Services Execute DDL TaskExecutes XMLA commands for creating or altering objects.Deploy a new cube definition from a script.
Analysis Services Transfer TaskCopies objects between databases or servers.Move a dimension from development to production.
Data Flow Task (OLE DB Source + OLE DB Destination)Standard ETL flow for loading fact tables.Populate fact tables before processing cubes.

Sample Package: Deploy and Process a Cube

The following XMLA script creates a simple cube and processes it. Save it as DeployCube.xmla and reference it from an Analysis Services Execute DDL Task.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ObjectDefinition>
      <Cube>
        <Name>SalesCube</Name>
        <DataSourceID>DataSource_Sales</DataSourceID>
        <Dimensions>
          <Dimension>
            <ID>[Date]</ID>
          </Dimension>
          <Dimension>
            <ID>[Product]</ID>
          </Dimension>
        </Dimensions>
        <MeasureGroups>
          <MeasureGroup>
            <Name>SalesFact</Name>
            <ID>[SalesFact]</ID>
            <DataSourceViewID>DataSourceView_SalesFacts</DataSourceViewID>
          </MeasureGroup>
        </MeasureGroups>
      </Cube>
    </ObjectDefinition>
  </Create>
  <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Object="Cube" Type="ProcessFull">
    <Object>
      <DatabaseID>AdventureWorksDW2019</DatabaseID>
      <CubeID>SalesCube</CubeID>
    </Object>
  </Process>
</Batch>

Best Practices

  1. Separate Development and Production Environments: Use distinct SSIS projects for each tier.
  2. Version Control: Store SSIS packages and XMLA scripts in Git or Azure DevOps.
  3. Parameterize Connections: Leverage SSIS project parameters for server names and credentials.
  4. Error Handling: Configure event handlers to log failures and send notifications.
  5. Incremental Processing: Prefer ProcessAdd or ProcessUpdate over ProcessFull where possible.

Further Reading