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