How to Check a File's Last Modified Date Using SSIS

Febuary 9, 2018 by Jamel Cato

I do a lot of work with SQL Server Integration Services these days. While SSIS certainly shines with heavy-duty ETL work, it's one of my favorite tools because it works just as well for simple automation tasks.

One of the simple things I need to do all the time is confirm that particular files have been refreshed with new data on schedule (and then take particular actions depending on the outcome of these checks.) This is ideal grunt work to automate with SSIS.

In order to get a file's Last Modified Date with SSIS 2008 or later, you need to do three things:

  1. Create a package-scoped SSIS string variable to store the actual date that your target file was last modified. Let's call that variable File_Updated_Actual.
  2. Add a Script Task to your package with a ReadWrite variable called File_Updated_Actual (it must be spelled exactly as it is in Step 1).
  3. Add the following code to the ScriptMain class of your Script Task (replacing \\YourServer\YourFolder\your_file_name.txt with the actual path to your file.)

Public Sub Main()

Dim fi As System.IO.FileInfo
Dim ModifiedTime As DateTime

If Dts.Variables.Contains("User::File_Updated_Actual") = True Then

fi = New
System.IO.FileInfo("\\YourServer\YourFolder\your_file_name.txt")
ModifiedTime = fi.LastWriteTime
Dts.Variables.Item("User::File_Updated_Actual").Value =
fi.LastWriteTime.ToShortDateString
Dts.TaskResult = ScriptResults.Success

Else

Sts.TaskResult = ScriptResults.Failure

EndIf

End Sub

Update: I occasionally get emails from people stuck at Step 3. Normally this is because they haven't placed the code above in the correct place. It is important that the code block above be placed inside the ScriptMain class. SSIS creates this class by default when you add a Script Task to your package. Additionally, if you have SSIS configured to use Visual Basic as the default programming language for scripts, then the ScriptMain class will also already have a public subroutine called Main(). You will know because the first line from the code block above will already be there. If it's already there, then copy the code block above except for the first and last lines and then paste it into the Script Task Editor directly after the line that starts with Public Sub Main().

Basically this code uses the System.IO namespace built into Windows to retrieve the date the file was last modified, convert that date to a string and then store the string in a SSIS variable.

Once you have the last modified date stored in a SSIS variable, you can use it anywhere inside your package. Something I commonly do is compare the variable's value to a target date (such as the date the file should've been refreshed). You can make such a comparison by changing the evaluation operation of any precedence constraint to expression and then adding a simple equality expression in C# language syntax (which is different than the VB/.NET syntax used above for the Script Task--yes I know, this is one of many idiosyncrasies that give SSIS such a steep learning curve).

I hope that helps somebody out there.