Generally developers use Scripting.FileSystem object in ActiveX script task to perform various file system related tasks (e.g. Copy file, Delete file etc.). In SSIS you can perform many file system related common tasks using File System task. You should find out the possibility of replacing ActiveX script using SSIS File System Task. If any task you performing using Scripting.FileSystem is not possible using File System Task (i.e. Check File Exists) then you can use Script Task in SSIS and use System.IO namespace to perform File/Folder related tasks not possible using FileSystem Task.
You can perform any of the following operations using File System Task
- Copy directory
- Copy file
- Create directory
- Delete directory
- Delete directory content
- Delete file
- Move directory
- Move file
- Rename file
- Set Attributes (i.e. Set file to Hidden, ReadOnly, Archive or System)
If your ActiveX script is using anything other than above listed operations then you might have to use SSIS Script Task and write code using System.IO methods. The following code snippets will show how to perform some of most common file/folder related tasks not possible to implement using File System Task.
Path related functions
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim sMyVar As String
'//Get only file name from a specified path => Returns mydatafile_001.txt
sMyVar = System.IO.Path.GetFileName("c:\temp\mydatafile_001.txt")
'//Get only directory path from a specified path => Returns c:\temp
sMyVar = System.IO.Path.GetDirectoryName("c:\temp\mydatafile_001.txt")
'//Combine two paths into one path => Returns c:\temp\mydatafile_001.txt
sMyVar = System.IO.Path.Combine("c:\temp", "mydatafile_001.txt")
'//Get filename without extension => Returns mydatafile_001
sMyVar = System.IO.Path.GetFileNameWithoutExtension("c:\temp\mydatafile_001.txt")
'//Get extension of the file => Returns txt
sMyVar = System.IO.Path.GetExtension("c:\temp\mydatafile_001.txt")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Check if file/folder exists
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim sMyVar As String
If File.Exists("c:\temp\file_001.txt") = True Then
'//Debug.Print "File Exists"
End If
If Directory.Exists("c:\temp") = True Then
'//Debug.Print "Folder Exists"
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Read from text file
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Read file content to string variable
Dim sMyfileData As String
Dim sReader As StreamReader = New StreamReader("c:\temp\file_001.txt")
sMyfileData = sReader.ReadToEnd
sReader.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Read from text file (line by line)
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Read file line by line
Dim sReader As New StreamReader("c:\autoexec.bat")
' Display all the text lines in the file.
Do Until sReader.Peek = -1
' The ReadLine methods reads whole lines.
Console.WriteLine(sReader.ReadLine)
Loop
' Always close a StreamReader when you've done with it.
sReader.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Write to text file
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Open existing file for append. If file doesn't exist then new file will be created
Dim writer As StreamWriter = New StreamWriter("c:\write_test.txt", True)
writer.WriteLine("Hello world - line1")
writer.WriteLine("Hello world - line2")
writer.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Get file information
Imports System.IO
Public Class ScriptMain
Public Sub Main()
'//Get file properties
Dim sInfo As String
Dim FileProps As FileInfo = New FileInfo("c:\windows\notepad.exe")
sInfo = sInfo & " File Name = " & FileProps.FullName
sInfo = sInfo & " Creation Time = " & FileProps.CreationTime
sInfo = sInfo & " Last Access Time = " & FileProps.LastAccessTime
sInfo = sInfo & " Last Write Time = " & FileProps.LastWriteTime
sInfo = sInfo & " Size = " & FileProps.Length
System.Diagnostics.Debug.Write(sInfo)
FileProps = Nothing
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
List files (with wild card search pattern and recursive option)
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim file As String
'//Recursive listing (search for *.txt)
'//Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.AllDirectories)
'//Top level listing (search for *.txt)
Dim files() As String = Directory.GetFiles("c:\windows", "*.txt", SearchOption.TopDirectoryOnly)
For Each file In files
System.Diagnostics.Debug.WriteLine(file & "...found")
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
List sub directories
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim dir As String
'Dim dirlist() As String = Directory.GetDirectories("c:\windows","*",SearchOption.AllDirectories)
Dim dirlist() As String = Directory.GetDirectories("c:\windows")
For Each dir In dirlist
System.Diagnostics.Debug.WriteLine(dir)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
List disk drives
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim dirInfo As Directory
Dim drive As String
Dim drives() As String = dirInfo.GetLogicalDrives()
For Each drive In drives
System.Diagnostics.Debug.WriteLine(drive)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
File/Folder delete, copy, move
Imports System.IO
Public Class ScriptMain
Public Sub Main()
If File.Exists("c:\dest\datafile.txt") Then
File.Delete("c:\dest\datafile.txt")
End If
File.Copy("c:\src\datafile.txt", "c:\dest\datafile.txt")
File.Move("c:\src\datafile.txt", "c:\dest\datafile.txt")
If Directory.Exists("c:\dest") Then
Directory.Delete("c:\dest")
End If
Directory.CreateDirectory("c:\dest")
Directory.Delete("c:\dest")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
How Big Is Your Package?