My Favorite XMLA-Script1 Of This Month:-

XMLA

1. OLAP-Backup Script(DB backup)-VB?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dts.Variables.Item("strBackupScript").Value = _
"" + _
" " + _
" " + Dts.Variables.Item("strBackupFolderFile").Value.ToString + Dts.Variables.Item("strDatabasename").Value.ToString + "_" + Format(Date.Now(), "yyyyMMddhhmmss") + ".abf" + _
" true" + _
""

'MsgBox(Dts.Variables.Item("strBackupScript").Value.ToString())
Dts.TaskResult = Dts.Results.Success

End Sub

End Class


2. OLAP-Rollback Script (DB Rollback)-VB?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Public Class ScriptMain


Public Sub Main()
Dim dInfo As DirectoryInfo = New DirectoryInfo(Dts.Variables("strBackupFolderFile").Value.ToString())
Dim temp As String
Dim strlatestfile As String
Dim tmpLatestCreationTime As DateTime

temp = Dts.Variables("strDatabasename").Value.ToString()

tmpLatestCreationTime = Now.AddYears(-10)
For Each f As FileInfo In dInfo.GetFiles
If InStr(f.Name, temp) > 0 Then
If f.CreationTime > tmpLatestCreationTime Then
strlatestfile = f.Name
tmpLatestCreationTime = f.CreationTime
End If
End If
Next
'MsgBox(strlatestfile)

Dts.Variables.Item("strRestoreScript").Value = _
"" + _
" " + Dts.Variables("strBackupFolderFile").Value.ToString() + strlatestfile + "" + _
" " + Dts.Variables("strDatabasename").Value.ToString() + "" + _
" " + "true" + "" + _
""

'MsgBox(Dts.Variables.Item("strRestoreScript").Value.ToString())

Dts.TaskResult = Dts.Results.Success
End Sub


End Class

8. How to Process a cube?

a).Dimension Process

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain


Public Sub Main()
Dim temp1 As String
Dim temp2 As String

Dts.Variables.Item("strProcessScript1").Value = _
""
temp1 = " "
temp2 = " " + _
"-1" + _
"" + Dts.Variables.Item("strErrorLogLocation").Value.ToString + "Log.Log" + "" + _
"ReportAndContinue" + _
" "

Dts.Variables.Item("strProcessScript2").Value = _
" " + _
""
Dts.Variables.Item("strDimensionProcess").Value = _
Dts.Variables.Item("strProcessScript1").Value.ToString + temp2 + temp1 + _
Dts.Variables.Item("strDimProcessScript").Value.ToString + _
Dts.Variables.Item("strProcessScript2").Value.ToString

'MsgBox(Dts.Variables.Item("strDimensionProcess").Value)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

*************************

Here
Dts.Variables.Item("strDimProcessScript").Value.ToString

Value is coming from for eachloop container like the given below script:
This is for all the dimensions

Dts.Variables.Item("strDimProcessScript").Value = Dts.Variables.Item("strDimProcessScript").Value.ToString() + _
" " + _
" " + _
" " + Dts.Variables.Item("ProcessType").Value.ToString + "" + _
"UseExisting" + _
" "