MSBI (SSIS/SSRS/SSAS) Online Training

Friday, January 30, 2009

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("strDatabasename").Value.ToString + "" + _
"
" + _
" " + 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("strDatabasename").Value.ToString + "" + _
" " + Dts.Variables.Item("strDimensionname").Value.ToString + "" + _
"
" + _
" " + Dts.Variables.Item("ProcessType").Value.ToString + "" + _
"UseExisting" + _
"
"

2 comments:

peterjohn said...

Pretty good post. I just came across your site and wanted to say that I’ve really enjoyed reading your posts. In any case I’ll be subscribing to your feed and I hope you will keep a good work!Cheer!

sap online training
software online training
sap sd online training
hadoop online training
sap-crm-online-training

peterjohn said...

This is one awesome blog article. Much thanks again.
I really enjoy the blog.Much thanks again. Really Great.


oracle online training
sap fico online training
dotnet online training
qa-qtp-software-testing-training-tutorial