SSIS - import most recent files based on varying file name and file time

  • Hello experts,

    I need help devising a way to modify an SSIS import package to select the most recent files based on a varying file name and file time. So far, I have a package that can do the following, assuming these file names:

    FILEA_02-20-2017-09.csv
    FILEB_02-20-2017-09.csv
    FILEC_02-20-2017-09.csv
    FILED_02-20-2017-09.csv
    FILEE_02-20-2017-09.csv

    1. Each file has a different name (the 'FILEA' part) but for each export run gets the same date time and hour appended to it ('02-20-2017-09').
    2. I was able to use a Foreach Loop Container along with a hard-coded hour to import the files, using this kind of variable expression:

    @[FileA::Base_Folder] + "FILEA_" + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) + "-" +
    RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2) + "-" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) + "-07.csv"

    My problem is, sometimes some of the files are requested by the users, so it's possible that only a couple of them will be re-exported at a later hour. At that point, the files might look like this:

    FILEA_02-20-2017-09.csv
    FILEA_02-20-2017-11.csv
    FILEB_02-20-2017-09.csv
    FILEC_02-20-2017-09.csv
    FILED_02-20-2017-09.csv
    FILED_02-20-2017-11.csv
    FILEE_02-20-2017-09.csv

    However, if I try to change the variable expression to a generic hour (by changing "-07.csv" to something using 'hh'), the SSIS package will set the hour to the time that the SSIS package is being run, which is not necessarily the same hour when the file was exported. So I have to use the kludge of renaming the files to end with "-07.csv" which I find extremely ugly lol.

    Also I would want to ensure that only the more recent file (say, FILEA_02-20-2017-11.csv) is imported and that the SSIS package doesn't try to import FILEA_02-20-2017-09.csv and FILEA_02-20-2017-11.csv.

    I must be missing something obvious, but does anyone have any advice on how I can fully automate this kind of file import? My first thought is maybe find a way to get the latest hour using a "max" kind of function, treating the hour as a regular integer. But not sure that would work, and I would also need to make sure the file export hour was 24-hour style, not 12-hour style.

    Apologies for the length of this post, but as Blaise Pascal once wrote, "I made this [letter] very long only because I have not had the leisure to make it shorter."

    Thanks for any help!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Use the for each loop to populate a table with the full filename of each file. Have two additional fields of Prefix and FileDateTime, derive the values for these from the file name.
    Then write a query that returns the full file name for the latest of each prefix, use this result set with another for each loop that actually does import

  • tim.ffitch 25252 - Wednesday, February 22, 2017 4:56 AM

    Use the for each loop to populate a table with the full filename of each file. Have two additional fields of Prefix and FileDateTime, derive the values for these from the file name.
    Then write a query that returns the full file name for the latest of each prefix, use this result set with another for each loop that actually does import

    Thanks, Tim! I will try that out.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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