VBScript / SQL Server – Working with files and the FileSystemObject

The ability to work with files gives us great flexibility in how we use DTS.

It allows us to add some intelligence into a package, and cope with unexpected situations automatically. The key to working with files is the Scripting Run Time as this gives us the FileSystemObject, which can be fully exploited through ActiveX Script Tasks, Workflow scripts, and even transformations, inside your DTS packages. We start by demonstrating some simple file manipulation procedures, and then move on to how we can fully integrate them into a DTS package and make our solutions more flexible.

Full documentation from Microsoft on the FileSystemObject object and related objects can be found here.

Copy File

This shows a simple file copy operation, using [gs hardcoded] source and destination filenames.

' Copy File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"
	sDestinationFile = "C:\DestinationFile.txt"

	oFSO.CopyFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Move or Rename File

This shows a simple file move operation, using hardcoded source and destination filenames. There is no explicit rename method in the [gs script]ing object, but a move is just the same.

' Move File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"
	sDestinationFile = "C:\Folder\DestinationFile.txt"

	oFSO.MoveFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Delete File

This shows a slightly more advanced delete operation. When using the DeleteFile method, if the file does not exist, an error is raised (File not found). We use the FileExists method to check for the file, and only call the DeleteFile when required.

' Delete File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"

	' Check if file exists to prevent error
	If oFSO.FileExists(sSourceFile) Then
		oFSO.DeleteFile sSourceFile
	End If

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Using Global Variables

To try and make our packages more flexible we should avoid using hard coded filenames as much as possible. One good method of doing this is to store all parameters required in global variables. In this way, no matter how many times we use a filename or file path inside our package, we only have to make one change.

This sample is a move operation again, and uses two global variables. The source filename is held in one global variable, and the destination path or folder is held in a second. We are only supplying the destination folder in this example, so the existing filename will be preserved. It is important that the path is qualified with a backslash, so we use a helper function QualifyPath to ensure this is the case.

' Global Variable Move
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value
	sDestinationFile = QualifyPath(DTSGlobalVariables("DestinationPath").Value)

	MsgBox sSourceFile
	MsgBox sDestinationFile
	oFSO.MoveFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Function QualifyPath(ByVal sPath)
	If Right(sPath, 1) = "\" Then
		QualifyPath = sPath
	Else
		QualifyPath = sPath & "\"
	End If
End Function

File Date & Time

A simple validation check we may wish to do would be to check the data and time of a file to ensure it has been created within a defined time period. In this example we check that the file has been created today, otherwise we return a failure result from the task. In some case you may not want to fail the task, which in turn leads to a failed package result, in which case the code should be placed inside a Workflow script instead.

' File Date & Time
Option Explicit

Function Main()

	Dim oFSO
	Dim oFile
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value

	Set oFile = oFSO.GetFile(sSourceFile)

	If oFile.DateCreated < Date Then
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End If

	' Clean Up
	Set oFile = Nothing
	Set oFSO = Nothing
End Function

File Size

Another simple validation check similar to the date check shown above uses the Size property. In this example we will return an error if the file is empty or 0 bytes in size.

' File Size
Option Explicit

Function Main()

	Dim oFSO
	Dim oFile
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value

	Set oFile = oFSO.GetFile(sSourceFile)

	If oFile.Size > 0 Then
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End If

	' Clean Up
	Set oFile = Nothing
	Set oFSO = Nothing
End Function

Using Connections

The majority of your file manipulations will be related to files used by [gs connection]s within your DTS package. A common requirement is to be import a file of a changing name, for which many people would first think of using the move or rename method shown above, but why not just change the connection instead?

The obvious integration of connections into your file manipulations is to read or set a filename on a connection, and use that within the rest of your [gs script]. Connections have a DataSource property, and for Text File connections and Jet (Excel, Access) connections, this property is the filename. For DBase and FoxPro connections then DataSource is the [gs folder], the filename or table will be selected in the task that uses the connection.

In this simple example we will read the filename from a connection and then rename the file, such as you may want to do after importing a file to indicate that the file has been dealt with. To get reference to the connection object we use the name, this is what you see as the [gs label] in the designer and it is also shown in the "Existing connection" drop-down in the connection properties dialog.

' Rename File from Connection
Option Explicit

Function Main()

	Dim oPkg
	Dim oConn
	Dim oFSO
	Dim oFile
	Dim sFilename

	' Get reference to the current Package object
	Set oPkg = DTSGlobalVariables.Parent

	' Get reference to the named connection
	Set oConn = oPkg.Connections("Text File (Source)")

	' Get the filename from the connection
	sFilename = oConn.DataSource

	' Rename the file
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	oFSO.MoveFile sFilename, sFilename & ".bak"

	' Clean Up
	Set oConn = Nothing
	Set oPkg = Nothing
	Set oFSO = Nothing
	
	Main = DTSTaskExecResult_Success
End Function