Loading Most Recent Excel File with SSIS: Script Task Debugging

Question

I try to get the "Last modified" excel file in a folder and load it in SSIS. I found a C# code to get the name of most recent excel sheet in a folder path, and copy that in the Script Task. The code is :

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;



namespace ST_2e01f076aa4f46d692cf4b47f5587da9.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

        public void Main()
        {

            // TODO: Add your code here
            var directory = new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());

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

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

            MessageBox.Show(Dts.Variables["User::VarFileName"].Value.ToString());


            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

However, when I run the script task to test it, I get the following error:

I used the project name displaying in error in my code, but still does not work. Could you please kindly help me how to fix it as I am new to both SSIS and C#. Thanks

Widmer

Answer 1

Here is an answer using Linq.

First add these namespaces

using System.Collections.Generic; //This gets you list
using System.Linq; //This allows you linq functions

//Here is your code
System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(@"D:\Temp");
List<System.IO.FileInfo>    fi = di.EnumerateFiles().ToList();
Dts.Variables["VarFileName"].Value = fi.Where(i=>i.Extension.ToLower()==".xls")
                .OrderByDescending(i => i.LastWriteTime)
                .Select(i => i.FullName).FirstOrDefault();

Ram

Answer 2

Per https://www.sqlservercentral.com/forums/topic/ssis-process-only-the-latest-file-based-on-name, you can just use an expression.

Here are the details:

1. In an SSIS package, go to the Control Flow.

2. Create a Foreach Loop Container.

3. Set the following:

   • General:

     • Name: Get most recent input file

   • Collection:

     • Enumerator: Foreach File Enumerator
     • Select the desired directory and file pattern

   • Variable Mappings (maps each file enumerated to a variable you specify)

     • Create variable User:current_file_name. Assign it to index 1.
     • Create variable User::latest_file_name. Do not assign to an index. You will use it later.

4. Within the Foreach Loop Container, create an Expression Task (per SSIS set variable at runtime).

   • Enter the formula below:

@[User::latest_file_name] = ((@[User::latest_file_name] < @[User::current_file_name]) 
                             ? @[User::current_file_name] 
                                  : @[User::latest_file_name])

5. Connect downstream processing to the output of this Foreach Loop Container. The variable @[User::latest_file_name] will contain the latest file.

SSIS seems to list files in a directory in alphabetical order, but I verified that the formula above will take the largest value (using lexicographical comparison), even if the files are not processed in alphabetical order.

Miniaturist