I do a lot of work with Sql Server Integration Services these days. Complicated Data Warehouse stuff mostly. 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—everyday actually—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, 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 Main() method of your Script Task (obviously replacing \\YourServer\YourFolder\your_file_name.txt with the actual path to your file):

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.