How to Get latest folder details dynamically in ssis

Question

I have folders in this path C:\Customer\

Folder Names:

04012019
04022019
04032019
...

I want to pick the latest folder(04032019) dynamically.

Can anyone please help me how to achieve this in ssis .

Thanks in advance!!

Answer 1

We can achieve this without using script task. First of all you need to create a table in SQL Server

CREATE TABLE folder_Name (id INT IDENTITY(1,1)
      , folder VARCHAR(512)
      , depth INT)

and then run the below SQL statement to insert the folder name into the table folder_Name.

INSERT INTO @tbl (folder, depth)
EXEC master.sys.xp_dirtree 'C:\Client',1,0;

After this we can compare the value and assign this to a variable for selecting that folder and then you can read all files data using loop or sequence container.

Answer 2

You can use a Script Task to achieve that:

1. In the package, create a variable of type string (example: @[User::FolderPath])
2. Add a Script Task
3. Select @[User:FolderPath as ReadWriteVariable
4. Use a similar Script :

First you have to add using System.Linq; to use Linq functionalities within the script

public void Main()
{

    //Change the root folder value an specify the main directory that contains the folders you need to process
    string rootfolder = @"C:\Customer\";
    DateTime dt;
    string[] folders = System.IO.Directory.GetDirectories(rootfolder, "*", System.IO.SearchOption.TopDirectoryOnly);

    string maxdatefoldername = folders.Select(x => System.IO.Path.GetFileName(x))
        .Where(y => DateTime.TryParseExact(y,"MMddyyyy",System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None,out dt) == true)
        .OrderByDescending(z => DateTime.ParseExact(z, "MMddyyyy", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None)).FirstOrDefault();

    string maxfolder = folders.Where(x => System.IO.Path.GetFileName(x) == maxdatefoldername).FirstOrDefault();

    Dts.Variables["User:FolderPath"].Value = maxfolder;

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