CODEWITHSUNDEEP
ssis
Neo
Neo

Reputation: 16239

Dynamically made flat file connection - want to take most recent file in the folder using SSIS?

I have created one SSIS package that takes .csv file data and insert into SQL table,

But now I want most recently added file in that folder suppose

My flat file connection path is : C:\Temp

Now in temp there is my file named MYDATA 2011-09-08.csv

now some more files are there like MYDATA 2011-09-15.csv , MYDATA 2011-09-17.csv

Now MYDATA 2011-09-17.csv is the most recent by date and i want to pickup this file how can i do this???

Upvotes: 0

Views: 1477

Answers (1)

luviktor
luviktor

Reputation: 2270

I would complete this task with a foreach loop combined with a script task.

  • First you should declare 3 variables:

Variables

  • Next step is adding a foreach loop container to the package with a script component inside of it.

enter image description here

  • Set foreach loop container properties as follows.

enter image description here

enter image description here

  • Set variables which script component has access to

enter image description here

  • In the script component parse the date from the file name, check if it greater than the recent date (User::RecentDate), and if it is save it to the User::RecentFileName variable.

    string strDatePart = Path.GetFileNameWithoutExtension(
    Dts.Variables["User::FileName"].Value.ToString()).Substring(7);
DateTime dateTime = DateTime.MinValue;
if (DateTime.TryParse(strDatePart, out dateTime))
{
    if (dateTime > (DateTime)Dts.Variables["User::RecentDate"].Value)
    {
        Dts.Variables["User::RecentDate"].Value = dateTime;
        Dts.Variables["User::RecentFileName"].Value =           
            Dts.Variables["User::FileName"].Value;
    }

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

Of course you can change this script as you wish it's just an example. After the loop ends the User::RecentFileName will contain your desired result.

Upvotes: 1

Related Questions