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
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"