Rudy Rodarte . com

Information

This article was written on 22 Jan 2010, and is filled under SQL Blogs.

Current post is tagged

, , , ,

Using ActiveX to check the age of a file in SQL Server

You may run into a case where working with old data can cause issues downstream. Now, you can use ActiveX to check the age of a file before proceeding. The code assumes that you have already assigned the file location elsewhere in the DTS Package. The code also assumes that you want to raise an error if the file is older than 24 hours.
Here is the code:

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

     ' Get the filename from my Text File connection called "Text File (Source)"
     ' There are other ways to do this, of course.

     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
          ' 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
          ' Return Error
               Main = DTSTaskExecResult_Failure
          Else
               ' Return Success
               Main = DTSTaskExecResult_Success
          End If
     End If

     Set oFile = Nothing
     Set oFSO = Nothing

End Function

You now know if the file is older than 24 hours ago.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter