SSIS Script Task Class

  • 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

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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