how to get the most recent file?

daily my client uploads a flat file to a folder.

each file has the same name with an " _##### " id number.

I have created a ssis package that will download the file, and put the data in a table, but I have to change the ID number manually.

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

|||

Ovidiu Burlacu wrote:

Try to use PropertyExpressions to set the ConnectionString of your flat file connection manager. You create a expression that appends the ID to the "same_file_name" and the ID can be set in a variable using configurations.

HTH,
Ovidiu Burlacu

And if the ID is always incremented, you can store either the last value or the next value in a flat file, or holding table in a database. For example, when you build the package, you could run an Execute SQL task to select the next ID from the database table and then use that result to build the filename.|||

Jdmaddox,

I did not try but just an idea. You can use 'xp_cmdshell'. Using this you can give OS commands.

http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx

And in DOS cmd: try DIR /? in cmd to get dir options

c:\>dir /OD gives the files names in sorted based on time, so hopefully you can parse them.

Hope this helps,

Venkat

|||maybe I should have started with...I am completely new to SSIS and DTS|||

jdmaddox wrote:

How can I get my package to pick the most recent flat file uploaded to the folder, so I can automate my process

Is that really what you want? What if two files have arrive since you last executed your package? You'll miss one of the files.

-Jamie

|||

jdmaddox wrote:

maybe I should have started with...I am completely new to SSIS and DTS

The fact that you are using SSIS and have never used DTS is a good thing in my opinion

-Jamie

|||

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

|||

jdmaddox wrote:

yes, a new file is uploaded daily, and I bring it in daily.

At present, I am executing the package by hand, and changing the file name in the connection manager.

It would be much simpler if I could get the package to target the most recent file by date and bring that into the DB

You could use For Each Loop ForEach File enumerator to loop over all the files, on the last iteration you will be looking at the last file, right? Assuming the files appear in date order (the easy way to do this is to store all files with a filename of YYYYMMDD*.*) then this will work.

-Jamie