Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How to check the file date
By Darren Green
Version 2000
Level Intermediate

This sample script was written to check the age of a file. If you import files according to a schedule, you may wish to check that the file is recent to prevent the potential re-import of an old file. The script reads the filename from an existing package connection and uses the scripting FileSystemObject to first check it exists, and then check it is less than 24 hours old.

' Pkg 238 (File Date)
Option Explicit

Function Main()
	Dim oFSO, oConn, sFileName, oFile

	' Get the filename from my Text File connection called "Text File (Source)"
	Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
	sFilename = oConn.DataSource
	Set oConn = Nothing

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	' Check File Exists first
	If Not oFSO.FileExists(sFilename) Then
		' SQL Server 2000 Only, log error
		DTSPackageLog.WriteStringToLog "File """ & sFilename & _
			""" does not exist."

		' Return Error
		Main = DTSTaskExecResult_Failure
	Else
		' Get file object
		Set oFile = oFSO.GetFile(sFilename)
	
		' Check age of file is less than 24 hours
		If DateDiff("h", oFile.DateLastModified, Now) >= 24 Then
			' SQL Server 2000 Only, log error
			DTSPackageLog.WriteStringToLog "File """ & _
				sFilename & """ is greater than 24 hours old."
	
			' Return Error
			Main = DTSTaskExecResult_Failure
		Else
			' Return Success
			Main = DTSTaskExecResult_Success	
		End If		
	End If
	
	Set oFile = Nothing
	Set oFSO = Nothing

End Function