I posted a blog entry before to Process the SSAS dimensions individually and automatically. If you use the same method to process the cube, you will find that it may not work all the times since the cube could be in an invalid state and unable to retrieve the Cube ID/Cube Name.
To overcome the issue, I will need to use the AMO .Net in stand of using MDV to accomplish this.
- Add a Script Task first in the the ETL control flow to retrieve the Dimension and Cube information.
Here is the steps in the Script Task:
- Create the AMO.Server object and connect to the SSAS database; Make sue you add Microsoft.AnalysisServices reference to the project.
- Retrieve the Database ID once it is connected.
- For each AMD Dimension in the database, retrieve the Dimension ID into an Array List
- For each AMO Cube in the database, retrieve the Cube ID into an Array List
- Save the ArrayLists as the SSIS Object Variables to be used later.
- Add Foreach Loop to process the Dimensions individually. In this step, you may add logging information to indicate the dimension being processed.
- Add another Foreach Loop to process the Cubes individually after processing the dimensions.
This way even thought the dimension process failed for some reason, we will still be able to retrieve the Cube ID back and reprocess them later without problem.