Taking Backup and Restore of database using SSIS.

"This operation we can do using Analysis Service Execute DDL Task and Script Task".

Analysis Service Execute DDL task: The Analysis Services Execute DDL Task is a very useful one, allowing you to do just about anything with a SQL Server Analysis Services instance.  For example you could backup and restore a database, process a cube, create a partition, merge partitions, etc.  You specify commands to be executed using XML for Analysis (XMLA) which is the native XML protocol for all interaction between a client application and a Microsoft SQL Server Analysis Services instance. 

Script Task: The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. You use the Script task for work that must be done once in a package (or once per enumerated object), instead than once per data row.

-Procedures to take backup of a database

Step 1: Add a string variable to the package; we will use this variable to contain the XMLA script to perform the backup.  Right click on the Control Flow, select Variables from the context menu, then enter the variable as follows

Step 2: Drag and drop the Script Task from the Toolbox onto the Control Flow of the SSIS package.  Edit the Script Task and add the package variable created in Step 2 above to the ReadWriteVariables property.  We will assign the XMLA script to this variable in the next step.   

Step 3: Click the Edit Script button in the Script Task Editor and enter the following XMLA script to take the backup of database

 Note: Here "cubes" is the database name.

 The backup file name is modified to include the current date.  The resulting XMLA is stored in the package variable named ‘x’.  The use of ${BACKUPFILENAME} for the text to replace is purely arbitrary, but hopefully intuitive.

Step 4: Open the Analysis Services Execute DDL Task editor, click on DDL in the list box on the left, and set the properties as follows:

The XMLA to execute is defined in the package variable that we setup in the previous step.
At this point you can execute the SSIS package and you will see the backup file created; the default location is specified in the BackupDir property for the Analysis Server; e.g. C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Backup.

 Finaly our package look like this.

** To restore add this code in script task all other steps are similar **