October 29, 2009 at 9:39 am
Hi
Does anyone know how to create or simulate a vb class in ssis?
I suspect it can't be done but there may be a work around - bodge or force I can use.
I don't want to create a dll or anything outside of the package if it can be helped.
What I have got is a series of tasks that involve backing up a database copying the backup file and restoring it on a dev server.
Step 1 is backup the db
step 2 is a script tasks to copy the file
set 3 restore the db.
Some of these databases take a while to backup and copy so the tasks run at the same time - so no For Each looping.
The script task is identical accept for the database name (which determines the name of the backup file an the folder it is stored in).
What I'd like to do is put this sub in a class:
Private Sub copyfile(ByVal databaseName As String)
Dim fileNames As String = databaseName & "*.bak"
Dim fileLen As Integer = Len(fileNames)
Dim SourceRoot As String = Dts.Variables("liveBackupRoot").Value.ToString()
Dim DestRoot As String = Dts.Variables("uatBackupRoot").Value.ToString()
Dim strSourceDir As String = SourceRoot & databaseName & "\"
Dim strSDestDir As String = DestRoot & "DR_" & databaseName & "\"
Dim di As New DirectoryInfo(strSourceDir)
Dim fi As FileInfo() = di.GetFiles(fileNames)
Dim fiTemp As FileInfo
Dim fileToCopy As FileInfo
Dim fileToCopyName As String = String.Empty
For Each fiTemp In fi
fileToCopyName = fiTemp.Name
If Len(fiTemp.Name) = fileLen Then
Dim fileSource As String = strSourceDir & fileToCopyName
Dim fileDest As String = strSDestDir & fileToCopyName
Try
File.Copy(fileSource, fileDest, True)
Catch ex As Exception
'' MsgBox(ex.Message)
End Try
End If
Next
End Sub
[/code = "vb"]
Any advice will be appreciated?
Seth
October 29, 2009 at 9:51 am
Your VB sample is just doing the file copy - am I right?
How are you structuring the rest of your package (the backup/restore bits) to achieve the parallelism you are after? Or maybe you haven't got that far yet?
Maybe you could build a single SSIS package that takes as parameters things like DBName, BackupFileSpec, CopyToFileSpec etc and then build a master package that executes multiple instances of this package, with appropriate parameters? Not that I've ever tried that, by the way 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 29, 2009 at 9:59 am
Hi Phil
Yes - the vb part just copes the file.
I have a couple of sequence containers that have the 3 steps 1 and 3 are TSQL backup and restore tasks with the vb part in between.
It's actually all finished and working but niggling me that I have a duplicate function in each of the 5 vb tasks (there are 5 dbs being copied). I can also see myself having to do something similar in the future.
A Master package might be a good enough work around for this though.
Thanks
Seth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply