SSIS: Archiving files with VB.Net

When creating SSIS packages it’s a common requirement to be able to archive the processed files. Here’s VB.Net code snippet, with a quick walkthrough, that does exactly that.

The script uses two variables, inFiles and archiveFiles. The variable inFiles contains the folder we want to move files from, and archiveFiles is the destination.

Configure your variables in BIDS like below.

Add a script task to the designer.

Right click the script task, choose ‘Edit’ then ‘Script’. Add the inFiles and archiveFiles variables to the ReadOnlyVariables textbox.

Click the “Design Script” button and paste the below code into the VSA editor.

' 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

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        '
        ' Add your code here
        '
        Dim dir As System.IO.Directory
        ' fetch the folder locations from SSIS variables
        Dim inFiles As String = Dts.Variables("inFiles").Value.ToString
        Dim archiveFiles As String = Dts.Variables("archiveFiles").Value.ToString

        ' Get the system date and time separators for removing from the folder name we'll create later
        Dim dateSeparator As String = System.Globalization.DateTimeFormatInfo.CurrentInfo.DateSeparator
        Dim timeSeparator As String = System.Globalization.DateTimeFormatInfo.CurrentInfo.TimeSeparator

        ' Used to create a datetime stamped folder removing separators
        Dim dt As String = DateTime.Now.ToString.Replace(" ", "").Replace(dateSeparator, "").Replace(timeSeparator, "").Replace("/", "")

        ' Check folder paths end with a "\"
        If inFiles.EndsWith("\") = False Then
            inFiles &= "\"
        End If

        If archiveFiles.EndsWith("\") = False Then
            archiveFiles &= "\"
        End If

        archiveFiles &= dt

        ' Create datetime stamp archive folder if it doesn't already exist
        If dir.Exists(archiveFiles) = False Then
            dir.CreateDirectory(archiveFiles)
        End If

        ' Move all files to archive
        For Each archFile As String In dir.GetFiles(inFiles)
            dir.Move(archFile, archiveFiles & "\" & archFile.Substring(archFile.LastIndexOf("\") + 1, archFile.Length - archFile.LastIndexOf("\") - 1))
        Next

        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class

Place some files into the location your inFiles variable points to. Then right click the script task and choose “Execute Task”.

Once the package has executed successfully the files will be moved to archiveFiles from inFiles.