This is the second entry in the series on how you can check for the existence of files in SQL Server, in part 1 Finding files in T-SQL we covered how the undocumented sp xp_FileExists could be used.
In this entry I will be introducing an overview of the FileSystemObject which we will be using primarily to check for the existence of files.
In the first example we shall create a job called “TEST – Find Files” with 4 steps which will do the following:
- Create a file C:\Test.txt
In this step we use CreateTextFile method to create our test file. - Check for the existence of the file C:\ThisIsntWhatICreated.txt
In this step we use the FileExists method to check for our test file. - Delete the file C:\Test.txt
In this step we use the DeleteFile method to delete our test file. - Create a Failed step for notification purposes.
This step is just to show that another step can be run if the file does not exist.
The code for this job can be found below:
-- Script generated by Richard Douglas
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Test Job]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Test Job]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'TEST - Find File')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''TEST - Find File'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'TEST - Find File'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'TEST - Find File', @owner_login_name = suser_sname(), @description = N'No description available.', @category_name = N'[Test Job]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Create File', @command = N'Function Main(), @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
FileName = "C:\Test.txt"
Dim fso, f1
Set fso = CreateObject("Scripting.FileSystemObject")
Set f1 = fso.CreateTextFile(FileName, True)
Set fso = Nothing
Set f1 = Nothing
End Function'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'Find File', @command = N'' Clean Up, @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 4, @on_fail_action = 4
Function Main()
Dim FSO
Dim File
Dim SourceFile
Set FSO = CreateObject("Scripting.FileSystemObject")
FileName = "C:\ThisIsntWhatICreated.txt"
If NOT FSO.FileExists(FileName) Then
Call Err.Raise(60001, "File Not found")
End If
'
Set File = Nothing
Set FSO = Nothing
End Function'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'Delete File', @command = N'Function Main()' Check if file exists to prevent error' Clean Up, @database_name = N'VBScript', @server = N'', @database_user_name = N'', @subsystem = N'ActiveScripting', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
Dim oFSO
Dim sSourceFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\Test.txt"
'
If oFSO.FileExists(sSourceFile) Then
oFSO.DeleteFile sSourceFile
End If
'
Set oFSO = Nothing
End Function
'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'Job Failed', @command = N'select 1', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 2, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
This has been a very simple overview of a few of the methods of the FileSystemObject, there are many more things you can do for example you may only want to proceed if the file is a certain size or of a certain date, with the FileSystemObject you can do all this and more. In fact I’ll show you how you can check the file size as well, this time we’ll use DTS (I realise it’s now two versions out of date but from the people I have spoken to lots of companies still use SQL 2000, even SQL 7 and still develop in DTS).
You can download the package here.
If you don’t want to download the package then the code below is the crux of it:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim FSO
Dim File
Dim SourceFile
Set FSO = CreateObject("Scripting.FileSystemObject")
FileName = DTSGlobalVariables("FileName").Value
If FSO.FileExists(FileName) Then
Set File = FSO.GetFile(FileName)
If File.Size = 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
Else
Main = DTSTaskExecResult_Failure
End If
' Clean Up
Set File = Nothing
Set FSO = Nothing
End Function
As you can see it’s pretty similar to the ActiveX we used in step two of the job with a couple of differences. The first being that we are using a global variable for the filename, this is so we could use it in a loop, read it from a table or any number of other things.
The second is that we are now also using the GetFile method that allows us to check the properties of the file itself. In this scenario we are looking at the size and saying if the file size is 0 then succeed, else fail.
To find out more about how to use the FileSystemObject you can refer to the MSDN documentation here. I hope you have found this an interesting introduction to the FileSystemObject and thank you for reading.