MSBI (SSIS/SSRS/SSAS) Online Training

Friday, January 30, 2009

My Favorite Script1 Of This Month:-

VB Script


Difference between expression and vb.script –to get a portion from a string

SUBSTRING( @[User::strPackageName] , FINDSTRING( @[User::strPackageName] , "ETL_Extract",1) + 12 , LEN( @[User::strPackageName] ) )


Dts.Variables("strPkgName").Value = Mid(Dts.Variables("strPackageName").Value.ToString, InStrRev(Dts.Variables("strPackageName").Value.ToString, "\", -1) + 1, Len(Dts.Variables("strPackageName").Value.ToString))


1. How to write a variable value into the file + VB?

Dim f As File
f.WriteAllText("C:\test.txt", Dts.Variables("strMeasureProcess").Value.ToString())

2. How to delete files from the folder,if the files are older than 7 days + 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 NumOfDays As Integer = 7
For Each f As FileInfo In dInfo.GetFiles

f.Refresh()

If DateDiff(DateInterval.Day, f.CreationTime, Date.Now) > NumOfDays Then
f.Delete()
End If
Next

Dts.TaskResult = Dts.Results.Success
End Sub

End Class




3. How to find out latest files from the folder, based on the timestamp and name + 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("strRestoreFile").Value = "USE [MASTER] RESTORE DATABASE " & Dts.Variables("strDatabasename").Value.ToString() & " FROM DISK = N" & "'" & Dts.Variables("strBackupFolderFile").Value.ToString() & strlatestfile & "' WITH FILE = 1, NOUNLOAD, STATS = 10"

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

4. Maintenance-Backup Script (DB backup)-VB?
Dts.Variables("strBackupFile").Value = " BACKUP DATABASE " & Dts.Variables("strDatabasename").Value.ToString() & " TO DISK = N" & "'" & Dts.Variables("strBackupFolderFile").Value.ToString() & Dts.Variables("strDatabasename").Value.ToString() & "_" & Format(Date.Now(), "yyyyMMddhhmmss") & ".BAK' WITH NOFORMAT, NOINIT, NAME = N" & "'" & Dts.Variables("strDatabasename").Value.ToString() & "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
5. Maintenance-Rollback Script (DB backup)-VB?
Dts.Variables("strRestoreFile").Value = "USE [MASTER] RESTORE DATABASE " & Dts.Variables("strDatabasename").Value.ToString() & " FROM DISK = N" & "'" & Dts.Variables("strBackupFolderFile").Value.ToString() & strlatestfile & "' WITH FILE = 1, NOUNLOAD, STATS = 10"

No comments: