SQL Server: Scheduling OLAP Database backup using Integration Services – Part II

Hi Friends,

In my previous post, I showed how you can backup your Analysis Services database using SQL Agent job and XMLA.

In this post, I shall show you how you can use SSIS package to achieve the same. So, your XMLA for backup is as follows:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

 <Object>
   <DatabaseID>Adventure Works DW 2008</DatabaseID>
 </Object>
 <File>Adventure Works DW 2008.abf</File>
</Backup>

We shall create a SSIS package and use the Analysis Services DDL execute task to

1. Start BIDS (Business Intelligence Development Studio) and create a new Integration Services Project (or) open any existing project and add a new package.
2. You get the default Package.dtsx
3. While you are in Control Flow, from the toolbox, drag Analysis Services Execute DDL Task.

1. You need to configure the task.
2. Double click the task to configure it.
3. In the Editor, click on DDL.
4. In the connection, set up the connect to OLAP database that you want to backup
5. In source type, select Direct Input.
6. In the SourceDirect section, paste the above XMLA command.
7. This is how the editor should look like:

1. Click OK
2. The simple package is ready. You can execute it manually to test it. And then you can schedule the package using SQL Agent job.
3. The backup will be taken in default OLAP backup folder.
4. In case, you want to overwrite existing file, modify the XMLA command as follows:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

 <Object>
   <DatabaseID>Adventure Works DW 2008</DatabaseID>
 </Object>
 <File>Adventure Works DW 2008.abf</File>
 <AllowOverwrite>true</AllowOverwrite>
</Backup>

That’s it. You are done.

Hope you enjoyed reading. I have also uploaded the sample SSIS solution in the Files section.