Post reply

Load the Latest File from a set of files

  • October 7, 2009 at 5:12 pm

    #156296

    Hi All,

    I have a set of files with dates and time, in the form of YYYYMMDD_HHMM.

    I have to pick the latest file and load it.

    I have used a for-each loop Container.

    My idea is

    1) Load first FileName and store it in a variable.

    2) Load the next file, compare it with the first FileName and then decide, which one is latest

    There might be more than 2 files in the folder.

    I am unable to implement the above, since I am unable to store the first FileName in a variable (step 1 above)

    Implementation of above or any other idea is most welcome.

    Thanks in advance

  • October 7, 2009 at 6:06 pm

    #1063170

    use an Execute SQL Task, and do this:

    declare @dos table (LineText varchar(100))

    insert into @dos

    execute xp_cmdshell 'dir <REPLACE THIS WITH YOUR DIRECTORY> /o-d /b /a-d'

    select top 1 * from @dos

    depending on your version of sql, you may need to replace the table variable with a temp table (and the accompanying create table instead of declare table statement)

    If your filename is what is in the format "YYYYMMDD_HHMM", then replace the /o-d with /o-n

    Put the results into a variable, and you have the filename you need to process.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 8, 2009 at 8:19 am

    #1063487

    You can use 2 variables and a Foreach Loop Container that cycles through the file folder. Setting varFullFileName in the Foreach Loop and varFullFileNameLatest by an expression like:

    @[User::varFullFileName] > @[User::varFullFileNameLatest] ? @[User::varFullFileName] : @[User::varFullFileNameLatest]

    [font="Arial Narrow"]bc[/font]

  • October 8, 2009 at 8:37 am

    #1063499

    Thanks you all for your ideas.

    I will try and see if I am able to achieve the requirement.

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

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