Wednesday, March 12, 2008

SSIS Package to Back Up SSAS Databases

There are a couple of different posts and blog entries out there that discuss backing up SSAS databases using various techniques such as AMO, SSIS, etc. I recently created an SSIS package to do the same thing, and I think it's a little bit simpler than some of the other solutions out there.

The basic design of the package is to use a ForEach Loop container to loop over the list of databases on a given SSAS server (versus using AMO or some other method of getting a list of databases) and then using an Analysis Services DDL task with an XMLA script that calls for a backup of the current database.

The figure below shows the overall control flow in the package:

Control Flow for Backup SSAS Databases Package

The ForEach Loop container uses the ADO.NET Schema Rowset Enumerator along with an ADO.NET connection to the SSAS server. Thus, the package contains a Connection Manager using the .Net Provider for OLEDB along with the OLE DB Provider for Analysis Services 9.0. Thus, the Connection Manager looks like this:

Connection Manager for ADO.NET Connection to SSAS

Notice the Provider selected, the Server name entered, and the fact that no Initial Catalog is selected (since we're going to loop over all of the catalogs with the ForEach loop container). In this case, the Connection Manager just connects to the local SSAS server, but that could be dynamically handled with a package variable and a property expression on the Connection Manager.

The important settings for the ForEach Loop Container look like this:

Collection Settings for ForEach Loop Container

Variable Mappings Settings for ForEach Loop Container

Note that the Collections settings specify the ADO.NET Schema Rowset Enumerator, the defined Connection Manager, and the Catalogs schema. Also note that the Variable Mappings tab specifies that the catalog name returned by the enumerator be stored in a package variable named DatabaseName.

Within the ForEach Loop container, you'll see a Script Task as well as an Analysis Services Execute DDL Task. The Script Task is used to generate the XMLA script needed to backup the current database and the Analysis Services Execute DDL Task then executes that XMLA script. The Script Task is configured as follows:

Script Settings for Script Task

Note that the DatabaseName variable is passed in as a read-only variable while another package variable named BackupScript is passed in as a read-write variable. The script within the task looks like this:

Public Sub Main()
' Add your code here

Dts.Variables.Item("BackupScript").Value = _
"<Backup xmlns="""">" + _
" <Object>" + _
" <DatabaseID>" + Dts.Variables.Item("DatabaseName").Value.ToString + "</DatabaseID>" + _
" </Object>" + _
" <File>" + Dts.Variables.Item("DatabaseName").Value.ToString + ".abf</File>" + _
" <AllowOverwrite>true</AllowOverwrite>" + _

Dts.TaskResult = Dts.Results.Success

End Sub

Sorry for the additional line wrapping in the code above, but the important point is that the BackupScript variable will contain a well-formed XMLA script for backing up the database named in the DatabaseName variable. The script is fairly simple in nature as it simply uses a backup filename of the database name with .abf appended and it assumes the backup file can be overwritten. Other scenarios could be handled (time-stamping the backup filenames, for example) via additional code.

Once the Script Task executes, the BackupScript variable is then ready to use to backup the current database (within the context of the ForEach Loop container). The Analysis Services Execute DDL Task is configured to execute the backup script as follows:

DDL Settings for AS Execute DDL Task

Note that the Analysis Services Execute DDL Task uses a native OLE DB provider to connect to SSAS, not an ADO.NET provider. Thus, a second Connection Manager is needed within the package to handle this connection. It is configured as follows:

Connection Manager for OLE DB Connection to SSAS

Note that it is configured the same as the ADO.NET-based Connection Manager above, but uses a Native OLE DB provider. Also note that the server name here could be made dynamic via a variable and a Property Expression as well.

And that's it. If you run the package, it will loop through all of the databases on the SSAS server and backup each one. If this needs to be done on a scheduled basis, the package can be easily deployed to one or more SSAS servers and scheduled using SQL Server Agent.

I've pushed a copy of the package up to my SkyDrive in case you want to take a look or even use it (as is of course!) yourself. Here's a link to the zip file there:

Let me know what you think...


Anonymous said...

Hello Dave, great package, well thought out and works very well.

One gotcha, if the SSAS database is renamed, the package will fail as the DatabaseID will be different to the DatabaseName used by the script. Any suggestions on how the script can be changed to use the DatabaseID?



Pedro said...

Good Post!!!

Dave Fackler said...

Duane and Pedro,

Thanks for the comments. I looked at trying to find a way to handle the backups via DatabaseID instead of DatabaseName, but it doesn't seem possible using the OLEDB provider and the Schema Rowset Enumerator. One possibility I thought of was to update the Script Task and use AMO to get the DatabaseID based on the DatabaseName. This is reverting to AMO, which is what I was trying to avoid, but it would likely work...

Dave F.

Priya said...

Hi Dave

Thanks for this post. I have now downloaded the package and scheduled for execution on the SQL Server job agent. The package has executed successfully.

PS I have included this on my blog at

I hope you don't mind it.


Anonymous said...

I used the Package work fine but when I schdule this package it gives error

Executed as user: Domoinname\Service account. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:03:53 PM Error: 2008-10-09 14:03:54.86 Code: 0xC0014023 Source: EACH - For Each SSAS Database Description: The GetEnumerator method of the ForEach Enumerator has failed with error 0x80004005 "Unspecified error". This occurs when the ForEach Enumerator cannot enumerate. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:03:53 PM Finished: 2:03:54 PM Elapsed: 1.437 seconds. The package execution failed. The step failed.

Dave Fackler said...

With regards to the error, I believe that happens when the SQL Server Agent service account does not have admin rights within SSAS. If that is the case, you may need to add that account to the administrative role within SSAS or use a proxy account for the job that does have admin rights to SSAS. Give that a try and let me know if it is still failing...

Dave F.

ng2000 said... hosts free msaccess databases look-alikes for windows. Might offer something helpful.

Jon Baker said...

Thanks for this post. We are implementing it as is.

Jon Baker

Anonymous said...

Hi Dave, regarding your reply to Duane and Pedro on May 7, 2008, may I know what is AMO and how can I use AMO to get Database ID instead of Database Name.

Thanks & regards,

Sunny said...

Hello Dave, good post

Can you please tell me how to add date and time along with backup file.


Anonymous said...

Great package. Works perfectly but I want to push the backup to a UNC path instead of the default location. I'm not a scripting guru so I was wondering if someone could help me here. I see where it needs to be changed in the VB script but don't know the correct syntax.
Thanks in advance!!

Anonymous said...

Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!

Anonymous said...

Hi dave,

Great Post !!!

I am facing the similar issue as mentioned by Duane.

Were you able to find any workaround to this problem?

Could you please let me know if ther is any other way to do it.


Sam Kane said...

Here are this and some other articles on SSAS Write Back:

BI Joe said...

Hi Dave, great article, thank you!

worked perfectly. JT

Anonymous said...

Great package. Works perfectly but I want to push the backup to a UNC path instead of the default location, where do I change this?

Anonymous said...

Right click the SSAS server instance in Management Studio and change the backup directory from the default to where you desire.

Requires a server restart.

Anonymous said...

This SSIS packge works great until somebody changes the name of the database. Then the DatabaseID and Database Name are different (See the properties of the db = usually both are same for ID and Name)

So is there a way to loop thru the catalog and get databaseID instead of name?