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:
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:
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:
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:
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=""http://schemas.microsoft.com/analysisservices/2003/engine"">" + _
" <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
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:
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:
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...