Post reply

To find the latest file in a folder and use that file as the source for Data flow task

  • February 27, 2008 at 11:39 am

    #124244

    Hi,

    The source of the Dataflow task should be the latest file from a folder.

    The folder would have files with names starting with xyz_abc and ending with datestamp. For ex: xyz_abc20080227.xls, would be the latest file and all other files would have same names except the datestamp part.

    How would i configure the excel source component to pick the latest file from the folder.

    Any pointers/suggestions would be really helpful.

    Thanks in advance.

    Alicia Rose

  • February 27, 2008 at 2:57 pm

    #782899

    Actually there are many ways to do this. I prefer this way. But before doing this, I have to ask you if you only get one file each day? If so, then what I have done in past is create a folder in the disk and call it current/latest file and then do the ssis package to pick up file from there using File Systems Task. Then, at the very end of the Package, meaning after it goes thru whatever needs to be done with the file(example:cleaning, scrubbing), I would have another File System task to delete the file on Success. So when the file comes in tomorrow, you definately know that it is tomorrow's file and just put your package to a job to run this automatically.

    [font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

  • February 27, 2008 at 3:53 pm

    #782928

    I like the method SQL King described. A few other options include creating a variable and making it an expression to the current date. This would work if you process the file each day. Another way would be to create a script task that uses vb to look throught the directory and picks the file based off logic.


    My blog: http://jahaines.blogspot.com

  • February 27, 2008 at 4:46 pm

    #782942

    Thanks Adam and SQLKing.

    I want to apply configurations to the package for same excel source and OLEDB destination specified earlier.

    Suppose i am using variable and i am making it an expression in the connection string-(as told by Adam).

    I am choosing the configuration type as XML file.

    How would i cordinate the variable and xml file(if i can do)?

    Or how would i specify the connection string(in the expression) in a XML configuration file?

    I know the package configuration is a good choice when i have to deploy the package, but not sure, how to use them in the above case.

    Thanks in advance,

    Alicia Rose

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

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