Managing files in Script Tasks

Script Task in SSIS can be used to manage files on a server. I will give just simple examples here. Some of them can be performed (even simpler) using different methods like File System Task or batch files, but scripts are really useful when you have to do something more complex. Example of that would be when location of files are dynamic (typically different server in different environments).

Classes for managing files are in System.IO namespace. You could add that in the header of the class and then you do not have to reference that, but to make things obvious, I will repeat System.IO even when it's redundant.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim sFileA As String

'Read location of the file from connection string
sFileA = Dts.Connections("userLog").ConnectionString
Dts.Variables("userLog").Value = sFileA
'debug MsgBox(Dts.Variables("file_userLogToLoad").Value.ToString())

sFileB = "F:\Folder1\userlog1.txt"
sFileC = "F:\Folder2\userlog2.txt"

'check if file A exists
If System.IO.File.Exists(sFileA) Then
' check if file B exists
If System.IO.File.Exists(sFileB) Then
'Copy a file (A -> C)
System.IO.File.Copy(sFileA, sFileC)

'append C with B
Dim s As String
s = System.IO.File.ReadAllText(sFileB)
System.IO.File.AppendAllText(sFileC, s)

'' To Delete userlog file for next user log load
System.IO.File.Delete(sFileA)

''' To rename/Move a file
System.IO.File.Move(sFileC, sFileA)
End If
Dts.Variables("TruncateWorkTable").Value = 1
End If
Dts.TaskResult = Dts.Results.Success
End Sub

End Class