5

We have an SSIS process that imports various files in different formats from various sources. Each of these files is delivered at different times throughout the month.

The users would like to be able to see the modification date for each file, to check they are getting regular updates.

The aim would be to produce a table at the end of the process like this:

Desired Table

So I am trying to work out how to get the modification date of each of the files I have read in. Is there a way to do this in SSIS ?

Thanks in advance

Community
  • 1
  • 1
Lobsterpants
  • 1,188
  • 2
  • 13
  • 33

1 Answers1

11

You can add a script component to the pipeline which reads the filename from an input variable and writes the file modified date to an output variable:

    /// <summary>
    /// This method is called when this script task executes in the control flow.
    /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    /// To open Help, press F1.
    /// </summary>
    public void Main()
    {
        System.IO.FileInfo theFile = 
              new System.IO.FileInfo(Dts.Variables["User::FilePath"].Value.ToString());

        if (theFile.Exists)
        {
            Dts.Variables["User::LastFileDate"].Value = theFile.LastWriteTime;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }
Steve Ford
  • 7,433
  • 19
  • 40
  • Thanks very much @Steve, I will give that a try and see how it goes. – Lobsterpants Jul 22 '15 at 08:30
  • I have kindly used your script task @Steve but my file save date / modified date is coming up using your script as 30/12/1899 00:00:00 - it should be 06/11/2019 00:00:00 please help – Joeysonic Nov 08 '19 at 15:40
  • @Joeysonic are you sure that the file exists? Check that you have set the variable FilePath correctly and that it contains the path of a valid file. Also ensure that you have created the output variable correctly. – Steve Ford Nov 08 '19 at 17:31
  • @Steve hi yes the file exists... My ["User::FilePath"] variable is C:\Folder\ExcelFile.xls please help – Joeysonic Nov 11 '19 at 14:40
  • Are you running the package in debug mode or on a server? Can you set a breakpoint in the code, step through and check the value of the variables? – Steve Ford Nov 13 '19 at 18:12