The code for the script task.

The script task has three variables as read only parameters:

sourceFile: name of the file. E.g: target.txt

sourcePath: e.g. c:\workfolder

targetPath: e.g. \\server\targetFolder

bankupFolder: e.g backup

The VB code

' 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.IO

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()

Dim sourceFile As String = Path.Combine(Dts.Variables("sourcePath").Value.ToString(), Dts.Variables("sourceFile").Value.ToString())

'MsgBox(sourcePathFile)

Dim targetPath As String = Dts.Variables("targetPath").Value.ToString()

If String.IsNullOrEmpty(sourceFile) OrElse Not File.Exists(sourceFile) Then

' Check if a valid source file path was specified on the package variable

Dts.Events.FireError(101, "Source path error", String.Format("You need to set a valid source file path in the package variable 'Source_FilePath'. Invalid path: '{0}'", sourceFile), String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

ElseIf String.IsNullOrEmpty(targetPath) OrElse Not Directory.Exists(targetPath) Then

' Check if a valid target path was specified on the package variable

Dts.Events.FireError(102, "Target folder error", String.Format("You need to set a valid target folder location in the package variable 'Target_Folder'. Invalid folder: '{0}'", targetPath), String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

Else

Process(sourceFile, targetPath)

'get all sub folders under target

'for each folder under target,

'if exists source file

'if exists backup folder, replace/copy source file to backup folder

'else, create backup folder first, then replace/copy source file to backup folder

'replace source file

Dim subdirectoryEntries As String() = Directory.GetDirectories(targetPath)

Dim subdirectory As String

For Each subdirectory In subdirectoryEntries

'MsgBox(subdirectory.ToString)

Dim sd As FileInfo = New FileInfo(subdirectory)

'do not process folder named 'backup'

If sd.Name = Dts.Variables("bankupFolder").Value.ToString() Then

Continue For

End If

Process(sourceFile, subdirectory.ToString)

Next subdirectory

Dts.TaskResult = Dts.Results.Success

End If

End Sub

'if exists source file under target path

'if exists backup folder, replace/copy source file to backup folder

'else, create backup folder first, then replace/copy source file to backup folder

'replace source file under target path.

Public Sub Process(ByVal sourceFile As String, ByVal targetPath As String)

Dim targetFile As String = Path.Combine(targetPath, Dts.Variables("sourceFile").Value.ToString())

'if source file is target file, don't process

If sourceFile = targetFile Then

Return

End If

If File.Exists(targetFile) Then

Dim targetBankupFolder As String = Path.Combine(targetPath, Dts.Variables("bankupFolder").Value.ToString())

' MsgBox(targetBankupPath)

' If the backup folder does not exist in the folder specifed, create the backup folder.

If Not Directory.Exists(targetBankupFolder) Then

'MsgBox("bankup folder NOT exists!")

Directory.CreateDirectory(targetBankupFolder)

Dts.Events.FireInformation(401, "Backup folder created", String.Format("Backup folder '{0}' was created.", targetBankupFolder), String.Empty, 0, False)

End If

Dim targetBankupFile As String = Path.Combine(targetBankupFolder, Dts.Variables("sourceFile").Value.ToString())

'if file already exists in backup folder, rename the file with timestamp

If File.Exists(targetBankupFile) Then

FileSystem.Rename(targetBankupFile, targetBankupFile & DateTime.Now.ToString("yyyyMMddHHmmss"))

End If

' Archive the existing file to the backup folder.

File.Copy(targetFile, targetBankupFile, True)

Dts.Events.FireInformation(402, "Target file archived", String.Format("Target file '{0}' was archived to the backup folder '{1}'.", targetFile, targetBankupFolder), String.Empty, 0, False)

' Overwrite the target file with the source file.

File.Copy(sourceFile, targetFile, True)

Dts.Events.FireInformation(403, "Target file overwritten", String.Format("Target file '{0}' was overwritten with the source file '{1}'.", targetFile, sourceFile), String.Empty, 0, False)

End If

End Sub

End Class