how to pick the first file first from specific folder using for each in SSIS

Question

how to pick the first file first from specific folder using for each in SSIS

hegdesuchi

Hall of Fame

Points: 3047
More actions
November 7, 2016 at 2:31 pm

#315399

I have a remote folder from where i pick the multiple files and loop through for each loop container. But I want to pick the first file first based on time stamp from that folder.
how do I do this in SSIS?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76945 More actions · Answer 1

The files are processed in alphabetical order. If you follow the naming convention of having a fixed filename prefix with a timestamp suffix in YYYYMMDDHHMMSS order they will be processed in datetime order.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 2

drew.allen (11/8/2016)
The files are processed in alphabetical order. If you follow the naming convention of having a fixed filename prefix with a timestamp suffix in YYYYMMDDHHMMSS order they will be processed in datetime order.
Drew

Yowch! Did not know that you couldn't interrogate file dates in SSIS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 3

Hi Guys,

I solved this using Script task.

I added a foreach loop container and a script task and added below code

var directory = new DirectoryInfo(Dts.Variables["User::remoteFolder"].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::VarFileName"].Value = file.ToString();
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}

Then I added my regular foreach loop container inside this foreach loop container by reading the file got by this script task.

Now I can pick the first file first.

Thanks for all your help

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Jeff Moden (11/8/2016)
drew.allen (11/8/2016)
The files are processed in alphabetical order. If you follow the naming convention of having a fixed filename prefix with a timestamp suffix in YYYYMMDDHHMMSS order they will be processed in datetime order.
Drew
Yowch! Did not know that you couldn't interrogate file dates in SSIS.

One can and it's easy using something like the FileInfo Class

😎