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
- Start BIDS (Business Intelligence Development Studio) and create a new Integration Services Project (or) open any existing project and add a new package.
- You get the default Package.dtsx
- While you are in Control Flow, from the toolbox, drag Analysis Services Execute DDL Task.
- You need to configure the task.
- Double click the task to configure it.
- In the Editor, click on DDL.
- In the connection, set up the connect to OLAP database that you want to backup
- In source type, select Direct Input.
- In the SourceDirect section, paste the above XMLA command.
- This is how the editor should look like:
- Click OK
- The simple package is ready. You can execute it manually to test it. And then you can schedule the package using SQL Agent job.
- The backup will be taken in default OLAP backup folder.
- 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.