SSIS package to process whole SSAS database

Question

we are using SSIS packages to process Cubes in SQL Server 2008 R2 Analysis Services. Until now we have been using an Analysis Services Processing Task in the package and have been manually adding all Cube and Dimension objects to the processing queue in that task. This also means we have to adjust the package when we add Dimensions to a Cube or Cubes to the SSAS DB.

But now we need an SSIS package that will process the whole SSAS database selected, so that we can modify the Cube later on, possibly adding Dimensions, without having to modify the package as well.

In SQL Server Management Studio it is possible to right click an SSAS database and select "Process..." but for the corresping SSIS task, I could not find out how to do this.

Is there any way to process a whole SSAS database in an SSIS package?

Thanks in advance, Christian

Answer 1

I'm confused as to why you can't use the SSIS Analysis Services Processing Task. I believe you have to option to select a database in the processing settings. You can choose then entire database rather than choosing individual cubes or dimensions on that database. Just make sure the Type says database.

I have also used the XMLA answer provided by @Meff and it works fine as well.

Answer 2

You could also use AMO, you would need to include the Microsoft.AnalysisServices reference to a SSIS script task and provide the variable values. This way doesn't lock you to the database Id but is slightly more complex:

   string cubeConnectionString = Dts.Variables["User::CubeConnectionString"].Value.ToString();
    string databaseName = Dts.Variables["User::DatabaseName"].Value.ToString();

    Server server = new Server();
    server.Connect(cubeConnectionString);

    Database database = server.Databases.FindByName(databaseName);
    database.Process(ProcessType.ProcessFull);

    server.Disconnect();
    Dts.TaskResult = (int)ScriptResults.Success;

Answer 3

When you go to process the whole database, before you click 'OK', you should see a "Script" button in the top-left of the process window. This will generate the processing XMLA to a new window.

Now take that processing XMLA and use it in an "Analysis Services Execute DDL" control-flow component.

Be careful with cube re-deployments as you'll see the XMLA uses the Id not the name of the database.