Search code examples
sqlsql-serverssisssascube

How to generate or set unique name (using current Date) for daily backup SSAS Cubes


I have an ETL with an Analysis Services Execute DDL object running a daily backup of my cube the XML code below:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><Object><DatabaseID>MyCube</DatabaseID></Object><File>B:\Backups\Cubes\MyCube.abf</File><AllowOverwrite>true</AllowOverwrite></Backup>

So I'm getting every day a file like:

MyCube.abf

And using SSIS with Sql task object or Sql server agent, I would like to have an unique file for every day with the current date. Something Like:

MyCube_2018_05_09.abf
MyCube_2018_05_10.abf
MyCube_2018_05_11.abf

Also If can set something to delete the oldest backup leaving the recent 2 weeks that would be really helpful.

Note: I had referenced the website below, but I tried several time in different ways and nothing work.

http://dbatasks.blogspot.com/2012/08/taking-backup-of-ssas-database.html

Any assistance in this regard will be greatly appreciated :D


Solution

  • Moved to answer:

    In Expression Builder in SSIS for variable you can copy/paste this and tweak it a bit for your needs. This puts year as 4 digits, and months and days as 2 with leading 0's if its a single number day or month:

    "MyCube_"+ (DT_WSTR,4)YEAR(GETDATE()) +  RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".abf"