SSIS- How To Get The Oldest File From Directory In SSIS Package

A short post “How to get the oldest file from directory in SSIS”. As you can see that I have 3 files those were created on different date times.

Fig 1: Source folder with files

We will use script task to get the oldest file name from Source Folder. Let's create two variables as shown below

FolderPath: This variable will contain the Directory path where files exist.
FileName: This variable will be used to hold file name from Script Task so that we can use later in our SSIS Package.

Fig 2: Create variables in SSIS Package

Bring the Script Task to Control Flow Surface and Provide the Variables as shown 
Fig 3: Map variables to Script Task

Use the Below Code in Script Task
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_d95d08a222ff40f3a02c1dfc7d09a2f4.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
       The execution engine calls this method when the task executes.
       To access the object model, use the Dts property. Connections, variables, events,
       and logging features are available as members of the Dts property as shown in the following examples.

       To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
       To post a log entry, call Dts.Log("This is my log text", 999, null);
       To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

       To use the connections collection use something like the following:
       ConnectionManager cm = Dts.Connections.Add("OLEDB");
       cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

       Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
       
       To open Help, press F1.
   */

        public void Main()
        {
       var directory = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());

            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MaxValue;

            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime < lastModified)
                {
                    lastModified = file.LastWriteTime;
                    Dts.Variables["User::FileName"].Value = file.ToString();
                }
            }

            MessageBox.Show(Dts.Variables["User::FileName"].Value.ToString());
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

The highlighted code is what you need to paste in Script Task Editor.

Let's run our SSIS Package and see if it is returning the oldest file name from Directory.
Fig 4: Final output , Package returning the oldest file name

1 comment:

  1. I feel SSIS and other SQL related aspects do actually help people to know more about the very best features that would help crack successful and complex IT problems.

    SSIS Postgresql Read

    ReplyDelete