Home > SSAS, SSIS > Backup SSAS Database with SSIS task

Backup SSAS Database with SSIS task

We often perform maintenance tasks for various needs. One of those in the check list is Database backup. I am not talking about SQL Server Database. Here, I am talking about taking backup of SSAS database.

Of course, we knew to take a cube backup using SSMS. In this article I am going to show you a most useful task in SSIS to execute DDL (Data Definition Language) statement in SSAS server.

Analysis Services Execute DDL task:

This task helps us to execute DDL statements such as create, alter, delete and etc. object in the Analysis Services database.

XMLA is the scripting language that is framed and execute in the SSAS server to perform an action. We can construct this statement or can generate this script for specific operation in the SSMS and execute it when needed.

In this article, I will be showing you how to execute XMLA script in SSAS to backup a database in the server to the user defined location using “Analysis Services Execute DDL task”.


Step 1: Create a package and add “Analysis Services Execute DDL Task” from the toolbox. Edit the task by right click the task and select edit option.

Step 2: Go to DDL tab and create new connection to SSAS instance.

Step 3: Edit the connection string if it is named instance else click ok. In my case, I have default instance (localhost).

Step 4: Select the source type for the DDL statement.

Step 5: Write XMLA statement to take backup of the database or Paste the generated script using SSMS. In this example I have generated the script using SSMS.

Step 6: Click the Source direct property box and Paste the script in to the DDL Statement dialogue box. Click OK

Step 7: Execute the package.

Thanks for reading. Please send an email if you have any questions.

For more information read this.

  1. November 8, 2013 at 11:45 am

    Just what I wanted – Thank You!

  2. Alvin A. Garcia
    June 28, 2015 at 10:53 pm

    Hi, thank you for sharing your knowledge.

  3. Alejandro
    July 9, 2015 at 9:26 am

    Most excellent SSAS backup using SSIS. I will look up how to add custom names to the backup files, so they can be truly unattended.

  4. January 30, 2017 at 1:33 am

    Hi, what if i need to automate this job for all the databases in my SSAS environment.

    • Bernard
      October 28, 2017 at 12:53 am

      I would consider using a string object variable, and encapsulate in a For Each Loop

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: